Loading...
JOINs trip up nearly everyone learning SQL. Pick a JOIN type below and watch exactly which rows survive, drop, and combine, one row at a time.
Conor Robertson
June 14, 2026 · 8 min read
The questions that come up most once people start writing JOINs for real.
QueryCase teaches SQL through mystery cases on real datasets. Music, sports, film, and more. Your first cases are completely free.
Start your first caseFree to start. No credit card required.
| name | title |
|---|---|
| Fox | The Heist |
| Owliver | The Witness |
JOINs are where SQL stops feeling like English and starts feeling like a puzzle. The syntax makes sense, the results do not. The diagram above is the fix: switch the JOIN type and watch which rows survive, which drop out, and where NULL appears.
Two detectives have no case. One case has no detective. Those three gaps are the entire reason the four JOIN types exist.
The mental model
A JOIN matches rows from two tables on a shared value. The type only decides one thing: what to do with the rows that don't match. INNER throws them away. LEFT, RIGHT and FULL keep some or all of them, padding the missing side with NULL. Once that clicks, every JOIN is just a choice about leftovers.
Every JOIN has the same three parts. Name them once and you can read any JOIN, however long the query gets.
d.name instead of repeating detectives.name everywhere. Aliases are optional, but almost everyone uses them.LEFT JOIN for INNER JOIN or any other and only the leftover handling changes. c is the alias for cases.id equals the case's detective_id. This is the most important line, and the one people forget.The Venn diagrams show the shape of what each one keeps. The cards show when to reach for it.
"Left" and "right" just mean the table written before the JOIN keyword and the one written after it. "Outer" is the family name for any JOIN that keeps unmatched rows, so LEFT, RIGHT and FULL are all outer joins. INNER is the one that is not.
The single most useful trick: LEFT JOIN + IS NULL
Want the detectives with no case? LEFT JOIN to keep all of them, then WHERE c.case_id IS NULL to keep only the rows that did not match. "Find the things missing a match" is one of the most common real questions in SQL, and this is the standard answer: unassigned cases, customers who never ordered, users with no activity.
SELF JOIN is joining a table to itself using two aliases, for when rows relate to other rows in the same table, like a detective whose mentor_id points to another detective's id.
CROSS JOIN pairs every row with every row, no ON at all. Four detectives times three cases makes twelve rows, a cartesian product. Handy for generating combinations, and a classic way to accidentally melt a large query.
Know the result you want but not the keyword? Read it backwards.
| You want… | Use |
|---|---|
| Only records that match on both sides | INNER JOIN |
| Your whole main list, with matches where they exist | LEFT JOIN |
| The records with no match at all | LEFT JOIN + IS NULL |
| Everything from both tables, gaps included | FULL OUTER JOIN |
| Every possible combination of two tables | CROSS JOIN |
When a JOIN finds no match, the missing columns come back NULL: not zero, not an empty string. And NULL never equals NULL, which is why you filter with IS NULL, never = NULL.
If one detective has three cases, a JOIN repeats that detective once per case. That is a one-to-many relationship doing its job. Use COUNT(DISTINCT d.id) when the repeats throw off a total.
When both tables have an id, write d.id and c.detective_id, never a bare id. Otherwise you get an "ambiguous column" error, or a silent bug that returns the wrong column.
The fastest way to learn this is to write it, not read it. Reading about JOINs gets you to "I understand it." Writing them gets you to "I reach for the right one without thinking." The gap is just reps: pick a question, write the JOIN, check the row count against what you expected, and adjust.