SQL Joins Explained

A join combines rows from two tables based on a matching condition. Every join type answers the same underlying question differently: what should happen to rows that have no match on the other side? Keep that question in mind and the whole family collapses into one idea.

All examples below use the same two tables.

-- users                      -- orders
-- id | name                  -- id | user_id | total
-- 1  | Ada                   -- 10 | 1       | 50
-- 2  | Bo                    -- 11 | 1       | 30
-- 3  | Cy                    -- 12 | 2       | 20
--                            -- 13 | 9       | 99   (no such user)

Ada has two orders, Bo has one, Cy has none, and order 13 points at a user that does not exist.

INNER JOIN: only the matches

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

Result: Ada 50, Ada 30, Bo 20. Cy disappears (no orders) and order 13 disappears (no user). INNER JOIN keeps only rows that match on both sides. It is the default meaning of plain JOIN, and the right choice when unmatched rows are irrelevant to the question.

LEFT JOIN: keep everything on the left

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id;

Result: Ada 50, Ada 30, Bo 20, Cy NULL. Every user appears at least once; where no order matches, the order columns are filled with NULL. This is the join for "show all X, with their Y if any," which describes most real reporting queries. It is by far the most used join after INNER.

The classic trick that falls out of it: find rows with no match.

SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;   -- users who never ordered: Cy

RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN is LEFT JOIN with the tables' roles swapped: keep every row from the right table. Our example would keep order 13 with NULL user columns. In practice almost nobody writes RIGHT JOIN; reordering the tables and using LEFT reads more naturally, which is why you rarely see it in real codebases.

FULL OUTER JOIN keeps unmatched rows from both sides: Cy appears with NULL order columns and order 13 appears with NULL user columns. It shines in reconciliation work, comparing two datasets to find records missing from either side. Note that MySQL does not support it; the workaround is a LEFT JOIN unioned with a RIGHT JOIN.

CROSS JOIN and self joins

CROSS JOIN has no ON clause and pairs every row with every row (3 users x 4 orders = 12 rows). Useful deliberately for generating combinations, like every product paired with every month for a report grid. Accidentally, it is what you get from a forgotten join condition, which is why a query that suddenly returns millions of rows usually means a missing ON.

A self join is any join type applied to one table twice, standard for hierarchies:

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

LEFT rather than INNER, so the CEO with no manager still appears.

The traps that catch everyone

Filtering in WHERE silently turns LEFT into INNER. This is the most common join bug in the wild:

-- intends: all users, with their large orders
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.total > 25;    -- Cy's NULL fails this test, Cy vanishes

NULL comparisons are never true, so unmatched rows get filtered out and the LEFT JOIN was pointless. The fix is moving the condition into the join:

LEFT JOIN orders o ON o.user_id = u.id AND o.total > 25

Now Cy stays, with NULLs. Conditions in ON decide what matches; conditions in WHERE decide what survives. For LEFT JOINs the difference is everything.

Row multiplication. Joins return one row per matching pair, so Ada appears twice above. Join a third table with multiple matches and the counts multiply: summing o.total after also joining a shipments table with two rows per order doubles every total. If an aggregate looks inflated, count the rows before blaming the SUM, and consider aggregating in a subquery before joining.

Ambiguous columns. Once two tables are involved, SELECT id may be ambiguous. Alias every table and qualify every column, as all the examples here do; it also makes six-line queries readable.

Reading other people's joins

Real production queries chain four or five joins, and the logic is often buried in a single unformatted line. Formatting first makes the structure visible: each JOIN and its ON pair on their own lines, so you can trace which table hangs off which. Paste any query into the SQL Formatter to get that layout instantly, and see formatting SQL for readability for the conventions that make joins easy to scan.