In this article

The page's title should always be the Snap's name.  For example, File Reader. 

Overview

Use this Snap to add a JOIN clause to join tables in separate queries coming from the upstream Snaps. This Snap also allows you to preview the result of the output query. You can validate the modified query using this preview functionality.

Prerequisites

None.

Limitations

None.

Known Issues

Snap Input and Output

Snap Settings

Parameter Name	Data Type	Description	Default Value	Example
Label	String		ELT Join	Combined Dataset
Get preview data	Check box		Not selected	Selected
ELT Join Type	String/Drop-down list	Choose the join type to use in the SQL. Available options are: Inner Left outer Full outer Right outer Natural Full outer Natural Left outer Natural Right outer Natural Inner Cross Left Anti Left Semi See Snowflake Join Types for details. See Redshift Join Types for details. See Azure Synapse Join Types for details.  See Join Types for Databricks on AWS for details. Natural Joins are not natively supported by Azure Synapse, Databricks Lakehouse Platform (DLP), and BigQuery databases. But, this Snap uses a series of query rewrite mechanisms to support these Join Types. You can apply these Natural Joins to your data sets in Azure Synapse, Databricks Lakehouse Platform (DLP), and BigQuery, accordingly. BigQuery does not have the native support for Left Anti and Left Semi join types. But, this Snap uses a series of query rewrite mechanisms to support these Join Types in BigQuery.	Inner	Left outer
ELT Join Condition	String/Expression	Specify the condition to initiate the JOIN operation. If you do not specify any condition here, the Snap uses ON null as the default condition. You can also use Pipeline parameters in this field to bind values. However, you must be careful to avoid SQL injection. See Preventing SQL Injection for details. You can specify any SQL expression that has a boolean output (true or false). The ELT Join Condition is ignored if the join type is: Natural Inner Join Natural Left Outer Join Natural Right Outer Join Natural Full Outer Join Cross Join	N/A	CUST_CODE1 = CUST_CODE
Left Table Alias	String/Expression	Specify the alias to use for the table in the first input view. This enables you to qualify the columns to join with an alias name and resolve any ambiguity due to identical column names.	N/A	TBL1
Right Table Alias	String/Expression	Specify the alias to use for the table in the second input view. This enables you to qualify the columns to join with an alias name and resolve any ambiguity due to identical column names.	N/A	TBL2
Resultant Column Names Prefix Type	Drop-down list	Not applicable if target database is Databricks Lakehouse Platform (DLP). Choose an option from the list to prefix the resultant columns names with a table alias; this enables the Snap to prevent collision of identical column names in resultant table. Available options are: None: Select this option if you do not want to add a prefix to any of the column names. All Columns: Select this option to prefix the alias name to all the resultant column names. Only Duplicate Columns: Select this option to prefix the alias name to only identical column names. For Pipelines created prior to 4.24 GA, if you choose the All Columns or the Only Duplicate Columns option in this field, ensure that you also configure an account for the Snap.	None	RT.D_DATE_SK

Preventing SQL Injection

You can pass Pipeline parameters as values in an SQL expression; however, if you do not phrase the expression properly it can lead to the parameter's name being bound as a value in the database. This potentially incorrect information being inserted into the database is known as SQL injection. It is thus necessary to take precautions when including Pipeline parameters in your SQL expression to prevent SQL injection. Based upon the intended use of the Pipeline parameter, use one or both the following methods to prevent accidental SQL injection:

Method-1: Simple Substitutions

You can reference the Pipeline parameter directly with a JSON-path without enabling expressions.

For example, if you want to use the Pipeline parameter, name, which contains the value of a column in the ELT Join Condition field:

colname = _name

Method-2: Dynamic Substitutions

You must enable expressions when using Pipeline parameters for dynamic substitutions. Format the SQL expression, except the Pipeline parameter's reference, as a string. 

For example, if you want to use the Pipeline parameter, name, which contains the value of a column in the ELT Join Condition field: 

_columnname + “= _name”

The Snap evaluates the expression and also carries out path substitutions.

Here is how it works

The Snap pre-processes the query to extract any JSON-Paths and converts them to bound parameters. For example, consider the following query:

_columnname + “= _name”

The Snap converts this query into the following before turning it into a prepared statement for the database:

colname = ?

The Snap evaluates the JSON-Path to get the value to bind the Pipeline parameter in the prepared statement. 

Troubleshooting

None.

Examples

Performing Inner Join 

We need a query that contains a JOIN clause. This example demonstrates how we can use the ELT Join Snap to build a query with the JOIN clause.

First, we build SELECT queries to read the target tables. To do so, we can use two ELT Select Snaps, in this example: Read Part A and Read Part B. Each of these Snaps is configured to output a SELECT * query to read the target table in the database. Additionally, these Snaps are also configured to show a preview of the SELECT query's execution as shown:

Read Part A Configuration	Read Part B Configuration

A preview of the outputs from the ELT Select Snaps is shown below:

Read Part A Output	Read Part B Output

The SELECT * queries in both of these Snaps form the inputs for the ELT Join Snap. We want to perform an inner join based on matching values of the CUST_CODE column in the tables. Accordingly, this Snap is configured as shown below:

Upon execution, the ELT Join Snap combines both incoming SELECT * queries and adds the JOIN clause.

A preview of the ELT Join Snap's output is shown below:

We can also add an ELT Insert-Select Snap downstream and write the result of this query into another table.

Download this Pipeline.

Preventing Collision of Identical Columns in Resultant Column Names

In this example, we retrieve data from two tables which are in Snowflake database. We filter the records based on certain clauses and join the queries using a JOIN clause to get the resultant identical columns without any collision. We use the the ELT Join Snap to achieve this.

First we use two ELT Select Snaps to retrieve data from the SF store and SF date_dim tables respectively. We configure the ELT Select Snaps as shown below; the respective output views are as shown below.

Then, we configure the ELT Filter Snaps with the following filter conditions.

Then, we configure the ELT Join Snap to perform a left outer join based on the matching values of the D_DATE_SK column in both the tables by applying a Join condition. Note that we select Only Duplicate Columns for Resultant Column Names Prefix Type to avoid collision of identical columns in the resultant output.

Upon execution, the ELT Join Snap combines both the incoming SELECT queries and adds the JOIN clause. The Snap prefixes the alias names to identical columns. A preview of the output is as shown below:

We can also add an ELT Insert-Select Snap downstream and write the result of this query into another table.

Download the Pipeline.

Downloads

Edit the Excerpt Include macro below to link to the Snap Pack page for this Snap page. Ensure that the heading Snap Pack History is not within the Snap Pack's history Excerpt. 

See Also

Provide links to the endpoint's official documentation, any relevant or related internal documentation, and any other links you have referred to on this page. Care should be taken when referencing external sites/documentation that it is an official site/documentation. Do not refer to forums such as stackoverflow.

• SnapLogic Product Glossary
• Getting started with SnapLogic