Joins & Set Operations

A join combines rows from two tables into one wider row. Since relational databases store relationships as shared values rather than pointers, joins are how you reassemble the data model at query time.

The join model

Every join starts from the same primitive: the Cartesian product. Pair every row of A with every row of B, then keep the pairs where a condition holds.

-- Conceptually, this:
SELECT * FROM authors JOIN books ON authors.id = books.author_id;

-- Means: form every (author, book) pair, keep those where the ids match.

The engine never actually materialises the full product — the planner uses nested loops, hash joins, or merge joins depending on statistics. But the semantics are the product plus a filter, and thinking of it that way makes every join type easy to derive.

The examples below extend the overview schema:

orders.sql

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  book_id     INTEGER NOT NULL REFERENCES books(id),
  qty         INTEGER NOT NULL DEFAULT 1
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Amina',  'Casablanca'),
  (2, 'Bruno',  'Lisbon'),
  (3, 'Chidi',  NULL);           -- never ordered anything

INSERT INTO orders (id, customer_id, book_id, qty) VALUES
  (1, 1, 1, 2),
  (2, 1, 3, 1),
  (3, 2, 4, 1);

INNER JOIN

Keeps only pairs where the condition is true. Rows with no match on either side disappear.

SELECT c.name, b.title, o.qty
FROM orders AS o
INNER JOIN customers AS c ON c.id = o.customer_id
INNER JOIN books     AS b ON b.id = o.book_id
ORDER BY c.name;

 name  |        title         | qty
-------+----------------------+-----
 Amina | The Dispossessed     |   2
 Amina | Stories of Your Life |   1
 Bruno | Kindred              |   1

Chidi is gone — no orders, no rows. INNER is the default: bare JOIN means INNER JOIN in both engines.

Table aliases (AS o, AS c) are not decoration. Once you join three tables, unqualified column names become ambiguous and hard to read. Alias everything and qualify every column.

LEFT OUTER JOIN

Keeps all rows from the left table. Where the right table has no match, its columns come back as NULL.

SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

 name  | order_count
-------+-------------
 Amina |           2
 Bruno |           1
 Chidi |           0

OUTER is optional noise — LEFT JOIN and LEFT OUTER JOIN are identical.

Caution

COUNT(o.id) gives 0 for Chidi; COUNT(*) would give 1, because the outer join produced one all-null row for him. COUNT(*) counts rows, COUNT(col) counts non-null values. This is the single most common outer-join bug. More in aggregation.

ON vs WHERE in an outer join

This distinction only matters for outer joins, and it matters a lot.

-- Filter applied DURING the join: Chidi survives with NULLs
SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id AND o.qty > 1;

-- Filter applied AFTER the join: Chidi's NULL row fails the test and vanishes
SELECT c.name, o.id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.qty > 1;

-- first query              -- second query
 name  | id                  name  | id
-------+----                -------+----
 Amina |  1                  Amina |  1
 Bruno |                    (1 row)
 Chidi |
(3 rows)

The second query has quietly become an inner join. Any WHERE condition on the right-hand table of a LEFT JOIN turns it into an inner join, unless the condition explicitly allows nulls (WHERE o.id IS NULL OR o.qty > 1).

Conditions on the left table belong in WHERE. Conditions on the right table belong in ON.

RIGHT and FULL OUTER JOIN

RIGHT JOIN keeps all rows from the right table — it is LEFT JOIN with the operands swapped. FULL OUTER JOIN keeps unmatched rows from both sides.

SELECT c.name, o.id
FROM orders AS o
RIGHT JOIN customers AS c ON c.id = o.customer_id;

SELECT c.name, o.id
FROM customers AS c
FULL OUTER JOIN orders AS o ON o.customer_id = c.id;

	SQLite	PostgreSQL
LEFT JOIN	Yes, always	Yes
RIGHT JOIN	3.39+ only	Yes
FULL OUTER JOIN	3.39+ only	Yes

Caution

SQLite gained RIGHT and FULL OUTER JOIN in 3.39.0 (June 2022). Older builds — including some system SQLite libraries still shipping today — reject them with a syntax error. Check with SELECT sqlite_version();.

Portable workarounds: rewrite RIGHT JOIN as a LEFT JOIN with the tables swapped, and simulate FULL OUTER JOIN with two LEFT JOINs combined by UNION:

SELECT c.name, o.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
UNION
SELECT c.name, o.id FROM orders o LEFT JOIN customers c ON c.id = o.customer_id;

In practice, RIGHT JOIN is rare — most people find it easier to read the query with the tables reordered.

CROSS JOIN

The bare Cartesian product, no condition.

SELECT a.name, b.title FROM authors AS a CROSS JOIN books AS b;   -- 3 × 4 = 12 rows

The comma syntax means the same thing: FROM authors, books. Legitimate uses are generating combinations — every size × every colour, every day × every store.

Danger

An accidental cross join is the classic “why did my query return 40 million rows” bug. It happens when you list tables with a comma and forget the WHERE linking them. Use explicit JOIN ... ON syntax so a missing condition is a syntax error rather than a silent product.

Note

In SQLite, CROSS JOIN has a second meaning: it tells the query planner not to reorder the tables, forcing the left table to be the outer loop. It is an optimiser hint, occasionally useful when SQLite picks a bad join order. PostgreSQL ignores join-order hints entirely and always reorders freely.

ON vs USING

USING (col) is shorthand for ON left.col = right.col when both tables name the column identically. It also merges the two columns into one in the output.

-- These match the same rows
SELECT * FROM orders JOIN books ON orders.book_id = books.id;
SELECT * FROM orders JOIN books USING (id);       -- only if both call it "id"

-- Realistic case: a shared column name
SELECT * FROM order_items JOIN books USING (book_id);

With USING, book_id appears once in SELECT * output and can be referenced unqualified. With ON, both copies appear. Both engines support USING.

NATURAL JOIN goes further and joins on every commonly named column, with no condition written at all.

Danger

Never use NATURAL JOIN. It silently changes meaning when someone adds a column — add a created_at to both tables and your join now requires matching timestamps. Both engines support it; neither should tempt you.

Self joins

A table joined to itself. You must alias both copies to tell them apart. The canonical case is a hierarchy:

CREATE TABLE employees (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO employees VALUES
  (1, 'Rania', NULL),
  (2, 'Sam',   1),
  (3, 'Tariq', 1),
  (4, 'Uma',   2);

SELECT e.name AS employee, m.name AS manager
FROM employees AS e
LEFT JOIN employees AS m ON m.id = e.manager_id
ORDER BY e.name;

 employee | manager
----------+---------
 Rania    |
 Sam      | Rania
 Tariq    | Rania
 Uma      | Sam

LEFT JOIN keeps Rania, who has no manager. An inner join would drop the root of the hierarchy.

Self joins also find pairs within a table. Use an inequality to avoid duplicates and self-pairing:

SELECT a.title, b.title
FROM books AS a
JOIN books AS b ON a.author_id = b.author_id AND a.id < b.id;
-- Pairs of books by the same author, each pair listed once

To walk a hierarchy of unknown depth you need a recursive CTE, not a join — see advanced SQL.

Multi-table joins

Joins are left-associative: each JOIN combines the accumulated result so far with the next table.

SELECT c.name, c.city, b.title, a.name AS author, o.qty
FROM orders    AS o
JOIN customers AS c ON c.id = o.customer_id
JOIN books     AS b ON b.id = o.book_id
JOIN authors   AS a ON a.id = b.author_id
WHERE c.city = 'Casablanca'
ORDER BY b.title;

Two things to watch:

Row multiplication. Joining a one-to-many relationship multiplies rows. If a customer has 5 orders and you also join a table with 3 addresses per customer, you get 15 rows per customer — and any SUM over them is wrong by 3×. Aggregate each branch separately (in a subquery or CTE) before joining.

Order matters for outer joins. Once you LEFT JOIN table B, an ordinary JOIN to table C afterwards will drop the rows where B was null. Keep outer joins outer all the way down the chain:

FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN books  b ON b.id = o.book_id       -- must also be LEFT

LATERAL (PostgreSQL only)

A LATERAL subquery in FROM can reference columns from tables listed before it — a per-row correlated join. It is the tidy way to fetch “top N per group”.

-- PostgreSQL: the 2 most recent orders per customer
SELECT c.name, o.id, o.qty
FROM customers AS c
LEFT JOIN LATERAL (
  SELECT id, qty FROM orders WHERE customer_id = c.id ORDER BY id DESC LIMIT 2
) AS o ON true;

SQLite has no LATERAL. Use a window function with ROW_NUMBER(), which works in both engines.

Set operations

Set operations combine the results of two queries vertically instead of horizontally. Both queries must produce the same number of columns with compatible types; the column names come from the first query.

SELECT name FROM customers
UNION
SELECT name FROM authors;

Operator	Result	SQLite	PostgreSQL
UNION	All rows from both, duplicates removed	Yes	Yes
UNION ALL	All rows from both, duplicates kept	Yes	Yes
INTERSECT	Rows in both, deduplicated	Yes	Yes
EXCEPT	Rows in the first but not the second, deduplicated	Yes	Yes
INTERSECT ALL / EXCEPT ALL	Multiset versions that keep duplicate counts	No	Yes

For deduplication purposes these operators treat NULLs as equal — unlike =.

Tip

UNION ALL is significantly faster than UNION because it skips the sort/hash needed to detect duplicates. If you know the inputs are disjoint (a common case when unioning partitioned tables), always write UNION ALL.

ORDER BY and LIMIT apply to the whole compound result, not to a branch:

SELECT title FROM books WHERE year < 1980
UNION ALL
SELECT title FROM books WHERE year >= 2000
ORDER BY title
LIMIT 5;

To limit an individual branch, wrap it in a subquery — this form works in both engines:

SELECT * FROM (SELECT title FROM books ORDER BY year LIMIT 2)
UNION ALL
SELECT * FROM (SELECT title FROM books ORDER BY year DESC LIMIT 2);

Note

PostgreSQL also accepts parentheses directly around each branch ((SELECT ... LIMIT 2) UNION ALL (SELECT ... LIMIT 2)). SQLite does not — the subquery form above is the portable one.

Anti-joins: finding what’s missing

An anti-join returns rows from A with no match in B. There are three standard ways to write it.

-- 1. NOT EXISTS — correlated, null-safe, usually the best plan
SELECT c.name
FROM customers AS c
WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

-- 2. LEFT JOIN ... IS NULL — join, then keep only the unmatched rows
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- 3. NOT IN — reads well, but see the warning
SELECT c.name
FROM customers AS c
WHERE c.id NOT IN (SELECT customer_id FROM orders);

All three return Chidi here.

Danger

Form 3 is a trap. If the subquery returns any NULL, NOT IN returns no rows at all — x NOT IN (1, NULL) is x <> 1 AND x <> NULL, and x <> NULL is unknown, so the condition is never true.

NOT EXISTS has no such problem and both engines optimise it well. Prefer NOT EXISTS for anti-joins.

For form 2, test a column that is guaranteed non-null in matching rows — the primary key is the safe choice. Testing a nullable column can’t distinguish “no match” from “matched, value was null”.

The positive version, a semi-join (rows from A that have a match, without duplicating them):

SELECT c.name
FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);

Note that a plain JOIN would return Amina twice (she has two orders). EXISTS stops at the first match, so each customer appears once. That difference is the reason semi-joins exist.

Key points

• A join is the Cartesian product plus a condition; every join type follows from that.
• INNER drops unmatched rows; LEFT/RIGHT/FULL preserve them and fill the other side with NULL.
• SQLite only got RIGHT and FULL OUTER JOIN in 3.39; LEFT JOIN has always worked.
• A WHERE condition on the right table of a LEFT JOIN silently converts it to an inner join — put it in ON instead.
• Alias every table, qualify every column, never use NATURAL JOIN.
• Joining one-to-many relationships multiplies rows and corrupts aggregates; aggregate before joining.
• UNION deduplicates, UNION ALL doesn’t and is faster; INTERSECT ALL/EXCEPT ALL are PostgreSQL-only.
• Use NOT EXISTS for anti-joins; NOT IN breaks on nulls.