Join Tab

The Join tab allows users to write SQL that will refine the results or include additional sources to add using Joins. Most SQL operators for your data platform are supported. The Join tab is part of the Node Editor.

SELECT

Select is done by the Mapping Grid so it typically doesn't need to be included in the Join tab.

Typically, a SQL SELECT statement’s clauses are evaluated in the order shown below, which can be helpful when structuring the contents of the Join tab.

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. WINDOW
6. QUALIFY
7. ORDER BY
8. LIMIT

The Join tab builds the FROM, filter, and grouping portions of your Node SQL. The Mapping Grid supplies the column SELECT list.

Snowflake

FROM {{ ref('WORK', 'ORDERS') }} "orders"
QUALIFY ROW_NUMBER() OVER (PARTITION BY "orders"."O_ORDERKEY" ORDER BY "orders"."O_ORDERDATE") = 1

Databricks

FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY `O_ORDERKEY` ORDER BY `O_ORDERDATE`) AS rn
  FROM {{ ref('WORK', 'ORDERS') }}
) `orders`
WHERE `orders`.`rn` = 1

BigQuery

FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY `O_ORDERKEY` ORDER BY `O_ORDERDATE`) AS rn
  FROM {{ ref('WORK', 'ORDERS') }}
) `orders`
WHERE `orders`.`rn` = 1

Generate Join

Generate Join opens a small modal with an automatically generated FROM statement that includes the Source Node used in the current node. If more than one Source Node was used, it will attempt to generate an INNER JOIN statement where you can configure which columns the JOIN will use. If you want to combine multiple sources without a JOIN, refer to our Multi Source Nodes feature instead.

Using Ref Functions

You must use Ref Functions to refer to Nodes here, otherwise you may find unexpected behavior.

Copy to Clipboard

Copies the generated SQL statement to your operating system's clipboard.

Copy to Editor

Copies the generated SQL statement to the text box in the Join tab. Note that this will not erase the current contents of the text box, and will append to the existing text instead.

Formatting Joins

Coalesce generates SQL using the identifier quoting style for your Project platform. Match that style when you write identifiers by hand in the Join tab. Ref Functions and helper tokens use the same rules when they expand.

Snowflake

FROM {{ ref('WORK', 'ORDERS') }} "orders"
INNER JOIN {{ ref('WORK', 'CUSTOMER') }} "customer"
  ON "orders"."O_CUSTKEY" = "customer"."C_CUSTKEY"

Databricks

FROM {{ ref('WORK', 'ORDERS') }} `orders`
INNER JOIN {{ ref('WORK', 'CUSTOMER') }} `customer`
  ON `orders`.`O_CUSTKEY` = `customer`.`C_CUSTKEY`

BigQuery

FROM {{ ref('WORK', 'ORDERS') }} `orders`
INNER JOIN {{ ref('WORK', 'CUSTOMER') }} `customer`
  ON `orders`.`O_CUSTKEY` = `customer`.`C_CUSTKEY`

If a Join fails with an invalid identifier error, see Quoted Identifiers in Coalesce and Error: Invalid Identifier.