Blog›SQL JOINs Explained: A Visual, Interactive Guide
SQL SkillsGetting Started
Finally Understand SQL JOINs
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.
CR
Conor Robertson
June 14, 2026 · 8 min read
Frequently asked questions
The questions that come up most once people start writing JOINs for real.
INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table regardless of a match, filling the right-side columns with NULL where there is no match. Put simply: INNER drops the leftovers, LEFT keeps the ones on the left.
The ON clause is the match rule. It tells the database which column in the first table should line up with which column in the second, for example ON d.id = c.detective_id. Without it, the database has no way to know how the two tables relate, and most databases will either error or fall back to pairing every row with every row.
Yes. Writing JOIN on its own defaults to INNER JOIN in every major SQL database. Spelling out INNER JOIN is just clearer to read in longer queries.
A JOIN produces one row per match. If a left-table row matches several right-table rows (a one-to-many relationship), it appears once per match. That is expected behaviour. Aggregate the data or use COUNT(DISTINCT ...) if the repeats are throwing off your totals.
Yes, chain as many as you need, each with its own ON condition. The database applies them left to right, so each JOIN operates on the combined result of the ones before it.
INNER, LEFT and CROSS JOIN work identically across all three. FULL OUTER JOIN works in PostgreSQL, but older MySQL and SQLite versions do not support it directly, so you emulate it with a LEFT JOIN UNION a RIGHT JOIN. The keywords are otherwise universal.
Ready to practice on real data?
QueryCase teaches SQL through mystery cases on real datasets. Music, sports, film, and more. Your first cases are completely free.
Chief FoxKeep a row only if it matches on both sides. Raven and Sterling have no case, and The Package has no detective, so all three drop out.
SELECTd.name, c.title FROMdetectives d INNER JOINcases c ON d.id = c.detective_id;
Result set
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 one idea that makes JOINs click
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.
Anatomy of a JOIN
Every JOIN has the same three parts. Name them once and you can read any JOIN, however long the query gets.
SELECT d.name, c.title
FROMdetectivesd
LEFT JOINcasesc
ON d.id = c.detective_id;
FROM detectives d· Your starting table. The lone d after it is an alias, a short nickname so you can write d.name instead of repeating detectives.name everywhere. Aliases are optional, but almost everyone uses them.
LEFT JOIN cases c· The second table to bring in, and the kind of JOIN to use. Swap LEFT JOIN for INNER JOIN or any other and only the leftover handling changes. c is the alias for cases.
ON d.id = c.detective_id· The match rule. It tells the database how the two tables relate: pair a detective with a case when the detective's id equals the case's detective_id. This is the most important line, and the one people forget.
The four JOINs you'll actually use
The Venn diagrams show the shape of what each one keeps. The cards show when to reach for it.
INNER JOIN
Only records that exist in both tables, like cases that have a detective assigned.
LEFT JOIN
Your whole main list intact: all detectives, with their case attached only if they have one.
RIGHT JOIN
Rarely. Most people swap the tables and write a LEFT JOIN, which reads more naturally.
FULL OUTER JOIN
Reconciling two datasets: show everything, and flag where the two sides do not line up.
"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.
Two more you'll meet occasionally
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.
Quick reference
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
Three things that trip people up
NULL is the absence of a value, not a value
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. When you want those blanks to read as something friendlier, a CASE expression can swap them for a label like "unassigned".
Duplicate rows are usually correct
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.
Always qualify shared column names
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. There are ten practice questions with worked solutions if you would rather they were handed to you, or the SQL detective game if you would rather they arrived as cases.
Once JOINs feel automatic, window functions and the OVER clause are the natural next step, and the one that separates comfortable SQL from interview-ready SQL.
SQL JOINs Explained: A Visual, Interactive Guide | QueryCase
