# JOIN

A JOIN combines rows from two sources using a matching condition. Each matching pair produces a result row. If one key has several matches, every matching pair is included.

## Syntax

```text
left_source
    [INNER | LEFT [OUTER] | RIGHT [OUTER] | FULL [OUTER] | CROSS]
    JOIN right_source
    [ON condition | USING (column, ...)]
```

## Join types

| Type         | Result                                               |
| ------------ | ---------------------------------------------------- |
| `INNER JOIN` | Matching row pairs only; this is the default JOIN.   |
| `LEFT JOIN`  | Matching pairs plus unmatched left rows.             |
| `RIGHT JOIN` | Matching pairs plus unmatched right rows.            |
| `FULL JOIN`  | Matching pairs plus unmatched rows from either side. |
| `CROSS JOIN` | Every pair of left and right rows.                   |

Outer joins fill the missing side with SQL `NULL`. An equality condition does not match `NULL` keys with one another.

`ON` accepts a matching expression. `USING (id)` matches shared columns by name. A `NATURAL` join uses all shared column names, so changes to a schema can change its matching condition; explicit conditions make the intended keys visible.

ClickHouse `ANY`, `ASOF` and distributed join modifiers are not supported.

## Examples

### Retaining customers without orders

```sql
CREATE TABLE customers (id INTEGER, name TEXT);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Grace');

CREATE TABLE orders (customer_id INTEGER, amount INTEGER);
INSERT INTO orders VALUES (1, 40), (1, 60);
```

```sql
SELECT customers.id, customers.name, orders.amount
FROM customers
LEFT JOIN orders ON orders.customer_id = customers.id
ORDER BY customers.id, orders.amount
FORMAT csv;
```

```text
1,"Ada",40
1,"Ada",60
2,"Grace",\N
```

### Matching a shared column

```sql
CREATE TABLE products (id INTEGER, name TEXT);
INSERT INTO products VALUES (1, 'Book'), (2, 'Pen');

CREATE TABLE stock (id INTEGER, quantity INTEGER);
INSERT INTO stock VALUES (1, 5), (2, 20);
```

```sql
SELECT products.id, name, quantity
FROM products
JOIN stock USING (id)
ORDER BY products.id
FORMAT csv;
```

```text
1,"Book",5
2,"Pen",20
```

A condition in `WHERE` runs after the join. For example, filtering a nullable right-side column there can remove the unmatched rows of a LEFT JOIN. Put conditions that determine right-side matches in `ON` when unmatched left rows must be retained.