Joins

ReferenceApplies to: Unified engine deployments

Joins combine relations using keys or predicates. Qualify shared column names with source aliases. Matching duplicate keys can multiply result rows; inspect key uniqueness before summing joined facts.

Join types

Form	Rows retained
`INNER JOIN`	Matching pairs
`LEFT [OUTER] JOIN`	All left rows, with NULL for unmatched right columns
`RIGHT [OUTER] JOIN`	All right rows, with NULL for unmatched left columns
`FULL [OUTER] JOIN`	Matches and unmatched rows from both inputs
`CROSS JOIN`	Every left/right pair
`LEFT SEMI JOIN`	Left rows with at least one right match, without right columns
`LEFT ANTI JOIN`	Left rows with no right match, without right columns

ON predicate names the matching condition. USING (column, ...) matches same-named columns and produces a shared key column. Prefer explicit ON conditions when source key names differ, as in TPC-DS.

Inner join

This joins item attributes to sales and summarizes completed matches. The item key resolves which dimension row belongs to each sales line.

SELECT i.i_category, SUM(CAST(s.ss_net_paid AS DOUBLE)) AS net_sales
FROM demo.tpcds.store_sales AS s
INNER JOIN demo.tpcds.item AS i ON s.ss_item_sk = i.i_item_sk
WHERE i.i_category IS NOT NULL
GROUP BY i.i_category
ORDER BY net_sales DESC, i.i_category
LIMIT 10;

Outer join

Place filters on nullable right-side columns carefully. A condition in WHERE can remove unmatched rows from a left join; a condition in ON controls matching while retaining left rows.

SELECT c.c_customer_sk, c.c_first_name, a.ca_state
FROM demo.tpcds.customer AS c
LEFT JOIN demo.tpcds.customer_address AS a
  ON c.c_current_addr_sk = a.ca_address_sk AND a.ca_state = 'CA'
ORDER BY c.c_customer_sk
LIMIT 10;

Semi and anti joins

Use a semi join to test for a match without repeating the left row for every right match. An anti join finds missing matches. Null keys do not match under ordinary equality.

SELECT i.i_item_sk, i.i_category
FROM demo.tpcds.item AS i
LEFT SEMI JOIN demo.tpcds.store_sales AS s ON i.i_item_sk = s.ss_item_sk
WHERE i.i_item_sk <= 20
ORDER BY i.i_item_sk;

Cross join

A cross join multiplies input cardinalities. Restrict each input before using one for exploration.

SELECT i.i_item_sk, s.s_store_sk
FROM (SELECT i_item_sk FROM demo.tpcds.item ORDER BY i_item_sk LIMIT 2) AS i
CROSS JOIN (SELECT s_store_sk FROM demo.tpcds.store ORDER BY s_store_sk LIMIT 2) AS s
ORDER BY i.i_item_sk, s.s_store_sk;

Native execution rejects null-safe joins and null-aware join forms it cannot preserve. Use ordinary equality with explicit, appropriate handling of null keys. NATURAL joins depend on all common column names and are fragile when schemas change.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Join types

Inner join

Outer join

Semi and anti joins

Cross join

​Join types

​Inner join

​Outer join

​Semi and anti joins

​Cross join

Join types

Inner join

Outer join

Semi and anti joins

Cross join