PostgreSQL Joins Explained with Examples
On this page
Sample tables for learning SQL joins
A good schema splits data across tables: customers in one, orders in another. Almost every useful question needs them back together, and a join does that. Join bugs rarely raise errors. They return too few rows or too many, and the report looks fine until someone checks the numbers.
The easiest way to learn joins is with data small enough to see in full. Three customers, three orders. Ana has two orders, Ben has one shipped order, and Cy has none.
CREATE TABLE customers (
customer_id int PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
order_id int PRIMARY KEY,
customer_id int REFERENCES customers,
status text NOT NULL
);
INSERT INTO customers VALUES (1, 'Ana'), (2, 'Ben'), (3, 'Cy');
INSERT INTO orders VALUES (10, 1, 'shipped'), (11, 1, 'pending'), (12, 2, 'shipped');INNER JOIN keeps matches, LEFT JOIN keeps everyone
An INNER JOIN pairs rows where the condition matches and drops everything else:
SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
-- Ana 10 | Ana 11 | Ben 12 (Cy is gone: no orders to pair with)JOIN on its own means INNER JOIN. That's right for "orders with their customer's name". It's wrong for "every customer and their orders", because Cy disappears.
A LEFT JOIN keeps every row from the left table. Where nothing matches, the right side's columns come back as NULL:
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.name, o.order_id;
-- Ana 10 | Ana 11 | Ben 12 | Cy NULLThose NULLs are useful. Filter for them and you get rows with no match, a pattern called an anti-join:
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
-- CyThe other outer joins are less common. RIGHT JOIN keeps every row from the right table, and most teams rewrite it as a LEFT JOIN with the tables swapped, because it's easier to read. FULL OUTER JOIN keeps unmatched rows from both sides. It's the tool for reconciling two systems, where you want to see records missing from either one.
The WHERE clause that undoes your LEFT JOIN
Here's the bug that catches everyone. You want every customer, plus their shipped orders. This looks right:
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'shipped';
-- Ana 10 | Ben 12 (Cy has vanished)The WHERE clause runs after the join. For Cy, o.status is NULL, and NULL = 'shipped' isn't true, so WHERE throws the row away. Your LEFT JOIN now behaves like an INNER JOIN.
Move the condition into ON. Then it decides which orders get paired, and every customer still survives:
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id AND o.status = 'shipped'
ORDER BY c.name;
-- Ana 10 | Ben 12 | Cy NULLThe rule of thumb: conditions about which rows pair up go in ON. Conditions about which rows the final result shows go in WHERE. For an INNER JOIN the placement doesn't change the result. For an outer join it changes everything.
Cartesian products and CROSS JOIN
The second silent bug goes the other way. Forget the join condition, and PostgreSQL doesn't complain. It pairs every row with every row:
SELECT count(*) FROM customers, orders; -- 9, which is 3 x 3That's a Cartesian product. On real tables, 10,000 orders times 500 customers gives five million rows. If a count looks like two table sizes multiplied together, check your join conditions.
Sometimes you want every combination on purpose, and then you write CROSS JOIN to make the intent clear:
SELECT s.size, c.color
FROM (VALUES ('S'), ('M'), ('L')) AS s(size)
CROSS JOIN (VALUES ('red'), ('blue')) AS c(color);
-- 6 rows: every size in every colorA milder version of the same problem happens with one-to-many joins. Join customers to orders and then sum something from customers, and Ana is counted twice, once per order. If totals look inflated, check whether a join has multiplied the rows.
Self joins and chains of joins
A table can join to itself. The classic case is employees and their managers, stored in one table. Give the table two aliases, one per role:
CREATE TABLE staff (
id int PRIMARY KEY,
name text NOT NULL,
manager_id int REFERENCES staff
);
INSERT INTO staff VALUES (1, 'Mo', NULL), (2, 'Lin', 1), (3, 'Raj', 1);
SELECT e.name AS employee, m.name AS manager
FROM staff e
LEFT JOIN staff m ON m.id = e.manager_id;
-- Mo NULL | Lin Mo | Raj MoIt's a LEFT JOIN on purpose. Mo has no manager, and an INNER JOIN would drop the CEO from the org chart.
Real queries often chain three or four tables. Each JOIN needs its own ON condition linking it to a table already in the query. Put one join per line and give each table a short alias:
SELECT c.name, o.order_id, p.product_name, oi.quantity * p.unit_price AS line_total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products p ON p.product_id = oi.product_id;The order you write the joins in is for readers. The planner reorders them to whatever it estimates is cheapest, and picks a strategy for each one (nested loop, hash join or merge join). Day 22 shows how to see its choices. What you control is indexes on the join columns, which give the planner fast options.
When both columns have the same name, USING (customer_id) is a shorter way to write the ON condition, and the column appears once in the output. Avoid NATURAL JOIN, which matches every column that shares a name. Add a created_at to both tables later and the join quietly changes meaning.
LATERAL: a subquery that runs once per row
One more join answers a question the others can't: "each customer with their latest order". A plain join returns every order. A LATERAL subquery can refer to columns of the tables before it, so it runs once per customer, with its own ORDER BY and LIMIT:
SELECT c.name, latest.order_id, latest.status
FROM customers c
LEFT JOIN LATERAL (
SELECT o.order_id, o.status
FROM orders o
WHERE o.customer_id = c.customer_id
ORDER BY o.order_id DESC
LIMIT 1
) AS latest ON true
ORDER BY c.name;
-- Ana 11 pending | Ben 12 shipped | Cy NULL NULLLEFT JOIN LATERAL ... ON true keeps customers with no orders, like any LEFT JOIN. Write CROSS JOIN LATERAL to drop them instead. Change LIMIT 1 to LIMIT 3 and you have "top 3 per group". With an index on orders (customer_id, order_id), each customer's lookup is a short index read, which makes LATERAL the fastest way to do top N per group when there are few groups and many rows in each. Window functions (Day 17) solve the same problem and are usually better when you need most rows of every group.
Check yourself
Move the date condition into the ON clause. In WHERE it drops the NULL-padded rows and turns the LEFT JOIN into an INNER JOIN.
The join condition is missing or never restricts anything, so PostgreSQL produced a Cartesian product. Add the ON clause that links orders.customer_id to customers.customer_id.
Each customer row was repeated once per order, and again per payment, so summed values were counted several times. Aggregate each child table separately first (Day 9 shows how), or sum at the right level.
LEFT JOIN LATERAL (SELECT ... WHERE o.customer_id = c.customer_id ORDER BY ... LIMIT 3) AS recent ON true. The LEFT join keeps customers whose subquery returns no rows, with NULLs in its columns.
USING (customer_id) is shorthand for joining on equally named columns, and it outputs that column once. ON o.customer_id = c.id lets you join on any condition, including differently named columns or ranges. Both give the same rows when the condition is the same equality.