SQL JOIN Types Explained: INNER, LEFT, RIGHT, and FULL
Understand the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN in SQL, with examples of what rows each one returns.
Published September 18, 2026
A JOIN combines rows from two tables on a matching column. INNER returns only matches, LEFT keeps every left row, RIGHT keeps every right row and FULL keeps everything from both.
The concept
Think of two overlapping circles. INNER JOIN is the overlap. LEFT JOIN is the whole left circle, with NULLs where the right side has no match. RIGHT is the mirror image. FULL OUTER is both circles together.
Rows without a match are filled with NULL on the missing side, which is how you find 'orphans' with WHERE right.id IS NULL.
Same tables, different joins
Practical examples
INNER JOIN
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;LEFT JOIN with an anti-join
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;FULL OUTER JOIN (PostgreSQL)
MySQL has no FULL JOIN; emulate it with UNION of a LEFT and RIGHT join.
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.a_id;Common mistakes
- Forgetting the ON clause and producing a cartesian product.
- Putting a filter on the right table in WHERE, which turns a LEFT JOIN into an INNER JOIN — put it in ON.
- Joining on non-unique columns and multiplying rows.
Key takeaways
- INNER = matches only; LEFT/RIGHT = keep one side; FULL = keep both.
- Unmatched sides become NULL.
- Index the join columns.
FAQ
LEFT JOIN vs LEFT OUTER JOIN?
They are the same; OUTER is optional.
Why did my row count explode?
The join key is not unique on one side, so each row matches many.