SQL Joins

A normalized database keeps each fact in one place. A customer's name lives in customers, not repeated on every order. The price is that answering a real question means stitching tables back together.

A join does the stitching. Learn it as a matching process and it stops being mysterious.

A join is row matching#

For every row on the left, the database looks for rows on the right where the ON condition is true. Each match produces one output row containing columns from both sides.

SELECT c.full_name, o.order_id, o.order_date
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id = 2;

 full_name  | order_id | order_date
------------+----------+------------
 Ben Carter |        3 | 2024-02-10
 Ben Carter |        7 | 2024-04-05

Ben has two orders, so Ben's row appears twice, once per match. Remember that. Every join bug you will meet comes back to "how many matches does each row have?"

c and o are table aliases. They shorten the query and are required when the same table appears twice.

INNER JOIN#

Only rows with a match on both sides survive. This is what a bare JOIN means.

Customers without orders vanish. So do orders whose customer is missing, though the foreign key prevents those.

LEFT JOIN#

Keeps every row from the left table. Where nothing matches, the right-hand columns come back as NULL.

SELECT c.full_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE c.customer_id IN (4, 9, 10)
ORDER BY c.customer_id, o.order_id;

   full_name   | order_id |  status
---------------+----------+-----------
 Diego Alvarez |        5 | cancelled
 Isla Murphy   |       14 | pending
 Jon Smith     |          |

Jon Smith has no orders, but he is still in the result, with NULL where the order would be. That makes LEFT JOIN the tool for "show me everything on the left, with extra detail when it exists".

 customers (left)         orders (right)            result
 +---+-----------+        +----+-------+           +-----------+----------+
 | 4 | Diego     | ------ |  5 | 4     |    --->   | Diego     |    5     |
 | 9 | Isla      | ------ | 14 | 9     |    --->   | Isla      |   14     |
 |10 | Jon       |   (no match)                    | Jon       |   NULL   |
 +---+-----------+        +----+-------+           +-----------+----------+

RIGHT and FULL OUTER JOIN#

RIGHT JOIN is a LEFT JOIN with the tables swapped. Most people write everything as LEFT JOIN and reorder the tables, so the data flows one way and the code reads consistently.

FULL OUTER JOIN keeps unmatched rows from both sides. It is rare, and useful for reconciliation: what exists in one list but not the other.

SELECT d.name AS dept, e.full_name
FROM departments d
FULL OUTER JOIN employees e ON e.department_id = d.department_id
WHERE e.employee_id IS NULL OR d.department_id IS NULL;

 dept  | full_name
-------+-----------
 Legal |

Legal exists with no employees. No employee lacks a department, because the column is NOT NULL.

CROSS JOIN#

Every row on the left paired with every row on the right. No ON clause.

SELECT COUNT(*) FROM customers CROSS JOIN products;   -- 10 x 11 = 110

It is legitimate when you want every combination, such as every product for every month in a report. It is a disaster when you get one by accident. Forget the ON in an old-style comma join (FROM a, b) and you get a cross join silently. Ten thousand rows times ten thousand rows is a hundred million.

SELF JOIN#

A table joined to itself, using two aliases so the database can tell the roles apart. The org chart is the classic case, since manager_id points at another row in the same table.

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

    employee    |   manager
----------------+-------------
 Meera Iyer     |
 Arjun Rao      | Meera Iyer
 Sofia Martins  | Arjun Rao
 Kenji Watanabe | Arjun Rao
 Priya Nair     | Meera Iyer
 Daniel Okafor  | Priya Nair
 Lena Fischer   | Priya Nair
 Omar Haddad    | Meera Iyer
 Grace Thompson | Omar Haddad
 Rahul Verma    | Meera Iyer

It must be a LEFT JOIN. With an inner join, Meera disappears, because she has no manager to match.

ON versus WHERE with outer joins#

For an inner join, a condition in ON or in WHERE gives the same result. For a LEFT JOIN it does not, and this is a favorite interview question.

Revenue per customer, ignoring cancelled orders, but still listing customers who have none:

-- Condition in ON: cancelled orders just fail to match, customers stay
SELECT c.full_name, COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS spend
FROM customers c
LEFT JOIN orders o       ON o.customer_id = c.customer_id AND o.status <> 'cancelled'
LEFT JOIN order_items oi ON oi.order_id   = o.order_id
GROUP BY c.customer_id, c.full_name
ORDER BY spend DESC, c.customer_id;

   full_name   |  spend
---------------+---------
 Farhan Ali    |  882.00
 Aisha Khan    |  726.00
 Grace Lin     |  607.50
 Ben Carter    |  322.00
 Chloe Dubois  |  269.50
 Elena Petrova |  167.50
 Hiro Tanaka   |   83.00
 Isla Murphy   |   82.00
 Diego Alvarez |    0.00
 Jon Smith     |    0.00

Move o.status <> 'cancelled' into WHERE and Diego and Jon vanish. After the join, their status is NULL, NULL <> 'cancelled' is unknown, and WHERE throws them out. The LEFT JOIN has quietly become an inner join.

Rule: a filter on the right table of a LEFT JOIN goes in ON if you want to keep unmatched left rows.

Anti-joins: finding what is missing#

"Customers who never ordered" is one of the most common questions in SQL. Two clean patterns:

-- LEFT JOIN, keep the non-matches
SELECT c.full_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- NOT EXISTS
SELECT c.full_name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

Both return Jon Smith. The second states the intent more directly and is immune to the NULL trap from the operators lesson. Products never ordered works the same way and returns Cable Organizer.

The fan-out problem#

Join a parent to a child with many rows and the parent's values repeat once per child.

SELECT COUNT(*) FROM orders o JOIN order_items oi ON oi.order_id = o.order_id;
-- 26

SELECT COUNT(DISTINCT o.order_id) FROM orders o JOIN order_items oi ON oi.order_id = o.order_id;
-- 14

Twenty-six rows, fourteen orders. If orders had a shipping_fee column and you summed it after this join, each order's fee would be added once per line item. Totals inflate and nothing errors.

Two defenses. Aggregate the child table in a subquery first so the join is one-to-one, or sum only values that belong to the child's grain. The deeper habit is to ask before every join: after this join, what does one row represent?

USING, NATURAL and multi-column joins#

When both columns share a name, USING is shorter and returns one copy of the column:

SELECT * FROM orders JOIN order_items USING (order_id);

Avoid NATURAL JOIN. It joins on every column that happens to share a name, so adding an unrelated created_at to both tables changes the query without touching it.

Composite keys need all parts in the ON clause.

How the database executes a join#

The planner chooses among three algorithms.

• Nested loop. For each left row, look up matching right rows, ideally through an index. Great for small inputs or very selective lookups.
• Hash join. Build an in-memory hash table from the smaller input, then probe it with each row of the larger one. The default for large unsorted inputs.
• Merge join. Sort both inputs on the join key and walk them together. Wins when both are already sorted.

You never write these. You influence them with indexes and statistics. Seeing Nested Loop on a million-row join with no index is the signal that something is missing.

Common mistakes#

Moving a right-table filter into WHERE. It turns a LEFT JOIN into an inner join.

Joining and then DISTINCT. If duplicates appear, find out why the join multiplies rows.

Aggregating after fan-out. Check the grain of the joined result.

Forgetting the join condition. The accidental cross join returns enormous results and looks like a hang.

Using INNER JOIN where a parent row might be missing. Reports quietly undercount.

Practice#

1. List every product with the number of units sold in non-cancelled orders, including products never sold. Cable Organizer should show 0 and Wireless Mouse 7.
2. Show each employee's department name. Then list departments with no employees using an anti-join.
3. For each order, show the customer name and the order total. Order 9 should total 607.50.
4. Write the "customers with spend, including zero" query yourself, then move the status filter into WHERE and explain exactly which rows change.
5. Using a self join, list each manager with their number of direct reports. Meera should have 4.