JOINs are where most people learning SQL get stuck. The syntax is short, the diagrams look simple, and yet the results are constantly surprising. The confusion almost always comes from the same place: thinking about JOINs as “combining tables” instead of asking the one question that actually matters — what happens to rows that have no match?
The setup
Imagine two tables. customers holds people; orders holds purchases, each linked back to a customer.
| customers.id | customers.name |
|---|---|
| 1 | Ana |
| 2 | Ben |
| 3 | Carla |
| orders.id | orders.customer_id | orders.total |
|---|---|---|
| 10 | 1 | 50 |
| 11 | 1 | 30 |
| 12 | 2 | 20 |
| 13 | NULL | 90 |
Note the two awkward rows: Carla has never ordered anything, and order 13 belongs to no customer. Those two rows are what reveal the difference between JOIN types.
INNER JOIN — only the matches
SELECT c.name, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;Returns only rows that exist on both sides: Ana (50), Ana (30), Ben (20). Carla disappears because she has no order. Order 13 disappears because it has no customer.
Notice something important: Ana appears twice. A JOIN does not “merge” tables one-to-one — it produces one row for every matching pair. This is the single most common source of unexpected row counts.
LEFT JOIN — everything on the left, matched or not
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;Every customer is kept, whether or not they ordered. Carla appears with NULL in the total column. Order 13 is still excluded, because it has no customer to attach to.
LEFT JOIN is the workhorse of reporting. Any time you want “all X, plus their Y if it exists” — all customers with their order count, all products with their reviews, all employees with their last training date — LEFT JOIN is the answer.
RIGHT JOIN — the mirror image
RIGHT JOIN keeps everything from the second table instead. In our example it would keep order 13, showing NULL for the customer name, and drop Carla.
In practice RIGHT JOIN is rare. Most developers simply reverse the table order and use LEFT JOIN, because reading a query is easier when the “kept” table is always the one you started from.
FULL OUTER JOIN — keep everything
FULL OUTER JOIN keeps every row from both tables, filling in NULL wherever there is no match. Our result would include Carla (no order) and order 13 (no customer).
It is mostly used to find inconsistencies — orphaned records, data that failed to sync between systems, mismatches after a migration. Note that not every database engine supports it; MySQL, for example, does not, and requires a workaround using a UNION of a LEFT and a RIGHT JOIN.
Quick reference
| Join type | Unmatched left rows | Unmatched right rows |
|---|---|---|
| INNER JOIN | Dropped | Dropped |
| LEFT JOIN | Kept (NULLs on right) | Dropped |
| RIGHT JOIN | Dropped | Kept (NULLs on left) |
| FULL OUTER JOIN | Kept | Kept |
| CROSS JOIN | Every row paired with every row — no condition at all | |
The mistake that silently ruins LEFT JOINs
This is worth memorising. Suppose you want all customers, with their orders from 2026 only:
-- WRONG: silently behaves like an INNER JOIN
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2026-01-01';Carla has no orders, so her o.order_date is NULL. The WHERE clause runs after the join, and NULL >= '2026-01-01' is not true — so Carla is filtered right back out. The LEFT JOIN has been destroyed.
The fix is to move the condition into the ON clause, so it applies while matching rather than after:
-- RIGHT: filter during the join
SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.order_date >= '2026-01-01';Rule of thumb: conditions on the outer (optional) table belong in ON. Conditions on the preserved table can safely stay in WHERE.
Self joins and multiple joins
Two extensions come up constantly in real work, and both are less exotic than they sound.
A self join is a table joined to itself, which is how you model hierarchies. If an employees table has a manager_id column pointing at another row in the same table, you can list every employee alongside their manager by joining employees to a second alias of employees. A LEFT JOIN is usually the right choice here, so that the person at the top of the hierarchy — who has no manager — still appears in the results.
Chaining several joins in one query is equally routine: customers to orders, orders to order items, order items to products. The important thing is that joins are evaluated in sequence, each one operating on the result of the previous step. If an INNER JOIN appears anywhere in that chain, it can quietly discard rows that an earlier LEFT JOIN had carefully preserved. When a query with many joins returns fewer rows than expected, this is almost always the reason — read the chain from top to bottom and find the first join that drops rows.
Other habits worth building
- Always alias your tables (
customers c) and prefix every column. Ambiguity errors vanish and queries become readable. - Check your row count. If a join unexpectedly multiplies rows, you are joining on a non-unique column somewhere.
- Index your join keys. Foreign key columns without an index are a classic cause of slow queries.
- Use
COUNT(o.id), notCOUNT(*), when counting after a LEFT JOIN — otherwise customers with no orders will count as 1 instead of 0.
Conclusion
Forget the overlapping circles for a moment and ask a single question every time you write a join: which rows do I want to survive even when they have no match? Answer that, and the correct JOIN type picks itself.
To go deeper into query writing, indexing and design, the free databases courses on Cursa are a solid next step.
