SQL & Database2 min readUpdated August 4, 2026
Understanding JOINs: INNER, LEFT, RIGHT and FULL
The difference between the JOIN types, and why picking the wrong one makes a report lose rows without raising a single error.
SQLJOINQueryDatabaseReporting
Transactional data is spread across many tables so nothing repeats. A JOIN is how it gets reassembled when read. The JOIN type decides what happens to rows with no counterpart — and that is where the hardest mistakes hide, because the query still runs without error, it just quietly returns less.
| Type | Rows returned |
|---|---|
| INNER JOIN | Only those with a match in both tables |
| LEFT JOIN | Every row from the left table; right columns NULL where there is no match |
| RIGHT JOIN | Every row from the right table; the mirror of LEFT |
| FULL OUTER JOIN | Every row from both tables, matched or not |
An example that separates them
-- INNER: customers with no orders vanish from the result
SELECT c.name, SUM(o.total)
FROM customer c
INNER JOIN sales_order o ON o.customer_id = c.id
GROUP BY c.name;
-- LEFT: customers with no orders still appear, with NULL
SELECT c.name, COALESCE(SUM(o.total), 0) AS total
FROM customer c
LEFT JOIN sales_order o ON o.customer_id = c.id
GROUP BY c.name;If the question is which customers have never ordered, only the LEFT version can answer it. The INNER version deletes precisely the rows you are looking for.
Two traps that keep catching people
- Putting a condition on the right table in the WHERE clause of a LEFT JOIN turns it into an INNER JOIN, because the NULL rows get filtered out. Put the condition in the ON clause instead.
- Joining to a table with many matching rows multiplies the left rows, so a SUM comes out larger than it should. Aggregate the right table first, then join.