Dev Basics · 5 min read
SQL Joins Explained
Joins are how SQL combines rows from two tables, and they are the single concept that most often trips people up when learning SQL. The good news is that the differences come down to one question: what happens to rows that have no match?
This guide walks through each join type with that question in mind.
Try it yourself with the related tool.
Format a SQL query →Advertisement
INNER JOIN — only matches
An INNER JOIN returns only the rows that have a match in both tables. If a user has no orders, that user disappears from the result entirely. This is the default when people say "join", and it is what you want when you only care about related records — for example, orders that definitely belong to a user.
LEFT JOIN — keep everything on the left
A LEFT JOIN returns every row from the left table, plus matching rows from the right. Where there is no match, the right-hand columns come back as NULL. This is the join you want for questions like "list all users and how many orders they have, including those with none".
A common trap: filtering on a right-hand column in the WHERE clause silently turns a LEFT JOIN back into an INNER JOIN, because NULL fails the comparison. Put that condition in the ON clause instead.
RIGHT and FULL OUTER
RIGHT JOIN is the mirror image of LEFT — every row from the right table, matches from the left. In practice it is rare, because you can always swap the table order and use LEFT, which most teams find easier to read. FULL OUTER JOIN keeps unmatched rows from *both* sides, which is useful for reconciliation — finding records that exist in one system but not the other.
CROSS JOIN and choosing
CROSS JOIN produces every combination of rows from both tables, with no condition — handy for generating matrices like every size in every colour, and a performance disaster if you write one by accident.
The rule of thumb: start with INNER if you only want matched records, and use LEFT when the left table's rows must all appear regardless.