Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 1 · Lesson 7 · 7 min read

PostgreSQL Joins Explained with Examples

On this page
  1. Sample tables for learning SQL joins
  2. INNER JOIN keeps matches, LEFT JOIN keeps everyone
  3. The WHERE clause that undoes your LEFT JOIN
  4. Cartesian products and CROSS JOIN
  5. Self joins and chains of joins
  6. LATERAL: a subquery that runs once per row
  7. Check yourself

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.

SQL
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:

SQL
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:

SQL
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 NULL

Those NULLs are useful. Filter for them and you get rows with no match, a pattern called an anti-join:

SQL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;
-- Cy

The 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:

SQL
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:

SQL
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 NULL

The 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:

SQL
SELECT count(*) FROM customers, orders;   -- 9, which is 3 x 3

That'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:

SQL
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 color

A 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:

SQL
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 Mo

It'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:

SQL
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:

SQL
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 NULL

LEFT 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.