Skip to content
Coordinator DB
View as Markdown
View as Markdown

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.