SQL JOINs explained
Inner, left, right, full outer, cross and self joins, mapped by the only question that matters: which rows survive when there is nothing to match them with.
Joining two tables
A join answers one question: when two tables describe the same thing, how do you line them up? Everything difficult about joins comes from what to do with the rows that turn out to have no partner on the other side.
The left table: customers
Picture a customers table. One row per person, each with an identity number that belongs to them and to nobody else. We call this the left table for one reason only: it is the one you happen to name first.
The right table: orders
Now an orders table. Thousands of rows, each carrying the identity number of whoever placed it. The same customer appears many times over, and some customers appear not at all, because they signed up and never bought a thing.
The join key
The link between them is the key: that customer identity number, stored in both places. Nothing stops you joining two unrelated columns that merely happen to hold numbers. A foreign key constraint keeps the stored data honest, but it will not stop you writing a meaningless join.
The ON condition
The ON clause is where you state the match. Anything can go in there, not just equality, but ninety nine times out of a hundred it is one column equalling another, and that is exactly what makes the fast join algorithms possible.
INNER JOIN
An inner join keeps only the rows that matched on both sides. A customer who never ordered vanishes, and so does an order pointing at a customer who no longer exists. It is the default, and it deletes your unmatched rows in silence.
LEFT JOIN
A left join keeps every row of the left table whatever happens. Where there is no matching order the database pads those columns with null. Your customer list stays complete, which is what a report usually needs, but remember that a row of nulls is still a row, so count the order column rather than counting rows.
RIGHT JOIN
A right join is the same idea pointed the other way: every row of the right table survives, padded with nulls on the left. Almost nobody writes one, because swapping the two table names turns it straight back into a left join.
FULL OUTER JOIN
A full outer join keeps everything from both sides, matched where it can be and padded with nulls where it cannot. This is the reconciliation join, the one you reach for when two systems are supposed to agree and clearly do not.
CROSS JOIN
A cross join has no condition at all. It pairs every row with every row, so a hundred customers and a hundred products give you ten thousand rows. Written by accident it is a catastrophe; written deliberately it builds calendars and grids.
SELF JOIN
A self join treats one table as two. Give it two different aliases and you can ask which employees share a manager, or line up each day of sales beside the day before, all without ever leaving that single table.
The anti join trick
Here is a trick worth stealing. Do a left join, then keep only the rows where the right side came back null, and you have every customer who never ordered. That is an anti join, and unlike the obvious NOT IN version it will not silently return nothing the moment one null turns up in the list.
Rows that multiply
Beware the quiet bug. If the key is not unique on both sides, a join multiplies rows instead of matching them. Three matching rows on the left and four on the right give you twelve, and your revenue total suddenly looks wonderful.
How joins really run
Underneath, there are only three real strategies. Nested loops when one side is tiny, a hash table built from one input, or a merge of two already sorted streams. Whatever you write becomes one of those three, chosen for you by the planner.
Watch it explain itself
Every step above is narrated aloud. Play it, or open it in the editor and make it yours — no account needed.