Joining tables ​

A join relates rows from two sources. Use an explicit ON condition or a USING list to show the intended keys.

INNER JOIN returns matching pairs. LEFT, RIGHT and FULL joins also retain unmatched rows from the indicated sides and fill missing fields with NULL. CROSS JOIN creates every combination.

Example ​

Customers with optional orders ​

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

CREATE TABLE orders (user_id INTEGER, quantity INTEGER);
INSERT INTO orders VALUES (1, 2);

SELECT users.name, orders.quantity
FROM users
LEFT JOIN orders ON orders.user_id = users.id
ORDER BY users.id
FORMAT csv;

"Ada",2
"Grace",\N

Grace is retained even without an order. A condition on the nullable order fields in WHERE can remove that unmatched row; put conditions defining eligible matches in ON when it should remain.

Duplicate matches ​

A join returns every matching pair. If a key appears twice on the right, one matching left row produces two result rows. Verify counts when the keys are not unique.

See the join guide and JOIN reference.