Joins are where SQL goes from storing data to answering real questions, and they are also where many developers get stuck. The confusion usually comes from missing a simple mental model. Once you picture what each join keeps and drops, the differences become obvious. This guide builds that model.
The basic idea
A join combines rows from two tables based on a related column — for example, matching each order to the customer who placed it via a shared customer ID. The join type decides what happens to rows that have no match on the other side.
The four main joins
- INNER JOIN — keeps only rows that match in both tables. Unmatched rows from either side are dropped.
- LEFT JOIN — keeps every row from the left table, filling in NULLs where the right table has no match.
- RIGHT JOIN — the mirror image: every row from the right table, NULLs on the left where unmatched.
- FULL OUTER JOIN — keeps all rows from both tables, with NULLs wherever a match is missing.
The one to reach for
In practice, INNER and LEFT joins cover the vast majority of needs. INNER answers "show me records that have a match"; LEFT answers "show me all of these, plus their match if it exists". A classic use of LEFT JOIN is finding records with no match at all — join, then filter where the right side is NULL, which reveals, say, customers who have never ordered.
The row explosion trap
A common surprise is getting far more rows than expected. This happens when the join column is not unique on one side — each left row matches multiple right rows, multiplying the results. It is not a bug in the join; it is the data. Be aware of the relationship (one-to-one, one-to-many, many-to-many) before joining, and aggregate when you only want counts or sums.
The takeaway
Think of each join as a decision about unmatched rows: drop them (INNER) or keep them with NULLs (LEFT, RIGHT, FULL). With that lens, joins stop being mysterious and become a precise tool for asking questions across tables.