SQL Joins, Finally Explained

You learned to split your data into separate tables — users in one place, orders in another, linked by an id. That was the right call. But now every real question you want to ask ("which customer placed this order?") lives across two tables, and you're stuck. A JOIN is how you put them back together for a single query.

Joins confuse almost everyone at first, and it's not because you're slow. It's because nobody draws you the picture: a join is just matching rows from one table to rows in another, using a shared key. Once you can see that picture, INNER, LEFT, and the rest stop being magic words and become choices you make on purpose.

How to read this

• Need the difference between INNER and LEFT right now? Jump to Phase 2: INNER vs LEFT (and the Others) — it leads with annotated queries and result tables.
• Want joins to finally make sense? Read in order. Phase 1 installs the mental model, Phase 2 shows the everyday joins, and Phase 3 covers the gotchas that bite people.

The phases

1. Why Joins Exist — you split data into linked tables on purpose; a JOIN matches rows across them on the shared key. The core picture, with two tables becoming one result.
2. INNER vs LEFT (and the Others) — INNER (only matching rows), LEFT (every left row, NULLs where there's no match), plus a calm note on RIGHT and FULL. Each shown with a query and a result table side by side.
3. Join Gotchas — the accidental cartesian explosion, NULLs from outer joins quietly breaking your WHERE, joining on the wrong columns, and how to sanity-check a join's row count.

This guide is about getting the right rows. Making a slow join fast — indexes on join keys, how the database actually executes a join — is its own topic, covered in Why Is My Query Slow?.

Related: Relationships and Keys · Querying Basics: SELECT & WHERE · Why Is My Query Slow?