> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Joins

> 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 s

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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.