Understanding SQL JOINs: The Complete Guide
JOINs are one of the most powerful — and most misunderstood — features in SQL. They allow you to combine rows from two or more tables based on a related column. If you've ever stared at a JOIN and wondered why your result set looks wrong, this guide is for you.
The Sample Tables
Throughout this guide, we'll use two simple tables:
- customers — stores customer IDs and names
- orders — stores order IDs, customer IDs, and order totals
Some customers have no orders. Some orders (bad data!) have no matching customer. This setup makes it easy to see the difference between each JOIN type.
INNER JOIN — Only Matching Rows
An INNER JOIN returns only the rows where there is a match in both tables.
SELECT customers.name, orders.total
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;If a customer has no orders, they won't appear in the result. If an order has no matching customer, it won't appear either. Think of it as the intersection of two sets.
LEFT JOIN — All Rows from the Left Table
A LEFT JOIN (also called LEFT OUTER JOIN) returns all rows from the left table, plus any matching rows from the right table. Where there's no match, the right-side columns return NULL.
SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;This is ideal when you want to find customers who have never placed an order — just filter WHERE orders.id IS NULL.
RIGHT JOIN — All Rows from the Right Table
A RIGHT JOIN is the mirror of a LEFT JOIN. It returns all rows from the right table and matching rows from the left. It's less commonly used because you can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping table order.
SELECT customers.name, orders.total
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;FULL OUTER JOIN — All Rows from Both Tables
A FULL OUTER JOIN returns all rows from both tables. Where there's no match, NULL fills in the gaps on either side. This is useful for data reconciliation tasks.
SELECT customers.name, orders.total
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;Note: MySQL does not support FULL OUTER JOIN natively — use a UNION of LEFT and RIGHT JOINs instead.
Quick Comparison Table
| JOIN Type | Returns | NULLs on left? | NULLs on right? |
|---|---|---|---|
| INNER JOIN | Matched rows only | No | No |
| LEFT JOIN | All left + matched right | No | Yes |
| RIGHT JOIN | All right + matched left | Yes | No |
| FULL OUTER JOIN | All rows from both | Yes | Yes |
Key Takeaways
- Use INNER JOIN when you only care about records with matches on both sides.
- Use LEFT JOIN when you need all records from the primary (left) table regardless of matches.
- Use FULL OUTER JOIN when you need a complete picture of both tables, including unmatched rows.
- Always specify your JOIN condition with
ON— a missing condition creates a cartesian product.
Mastering JOINs unlocks the ability to query relational data meaningfully. Practice with real datasets, and the logic will quickly become second nature.