The five JOIN types: sticking two tables together
A JOIN glues two tables side by side where a shared column matches. The five types just differ on what happens to rows that don't find a match.
Your data usually lives in more than one table, orders in one, customers in another. A JOIN
sticks them together side by side, matching rows by a shared column (like customer_id). The five
join types all do that; they only differ on one question: what happens to a row that has no match
on the other side?
🎯 Explain Like I'm Hired Picture two spreadsheets you're lining up by a shared ID column.
INNER JOINkeeps only the rows that exist in both sheets.LEFT JOINkeeps every row from the first sheet, leaving blanks where the second sheet has no match.FULL OUTER JOINkeeps everything from both, with blanks on whichever side is missing. Example: "find customers who signed up but never ordered" is aLEFT JOINfrom customers to orders, then keeping the rows where the order side came back blank (WHERE orders.id IS NULL).
-- INNER JOIN: only rows that match on both sides
SELECT o.id, o.amount, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
The ON part says which columns have to match. Here, an order's customer_id has to equal a
customer's id. That's the whole idea: everything below is just "what happens to the leftovers."
Two tiny tables to keep in your head for the rest of this article:
customers orders
id | name id | customer_id | amount
---+------- --+-------------+-------
1 | Riya 10 | 1 | 500
2 | Aditya 11 | 1 | 300
3 | Meera 12 | 2 | 900
13 | 99 (no such customer) | 700
Riya has two orders, Aditya has one, Meera has none, and order 13 points at a customer that doesn't exist. Those two odd ones (a customer with no order, an order with no customer) are exactly what each join type treats differently.
INNER JOIN: only the rows that match on both sides
Problem: "List every order together with the name of the customer who placed it."
SELECT o.id AS order_id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;
Result: Meera vanishes (no orders), and order 13 vanishes (no real customer):
order_id | name | amount
---------+--------+-------
10 | Riya | 500
11 | Riya | 300
12 | Aditya | 900
customers orders INNER keeps
○──────────○ only the overlap
( only matched pairs )
Reach for it when a row with no match is meaningless to your question. Most day-to-day joins are INNER joins.
LEFT JOIN: keep every row from the left table
Problem: "List all customers and how much each has ordered, including ones who never ordered."
SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
Result: Meera stays, with blanks where her order would be:
name | order_id | amount
-------+----------+-------
Riya | 10 | 500
Riya | 11 | 300
Aditya | 12 | 900
Meera | (blank) | (blank)
customers orders LEFT keeps
●──────────○ ALL of the left,
( all left + matches ) plus matches
Reach for it when the left table is your "full universe" (all customers, all products) and you
want to see who's missing something. Add WHERE o.id IS NULL and you get exactly the customers who
never ordered, one of the most common real questions there is.
RIGHT JOIN: the mirror image
Problem: same as LEFT, just written from the other direction. RIGHT JOIN keeps every row from
the right table.
SELECT c.name, o.id AS order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.id; -- keeps all orders, even orphan #13
Order 13 (the one pointing at a non-existent customer) stays, with a blank name. In practice most people avoid RIGHT JOIN and just flip the table order to use a LEFT JOIN, because "keep everything on the left" reads more naturally than tracking which side is which.
FULL OUTER JOIN: keep everything from both sides
Problem: "Show me the mismatches on both sides, customers with no orders AND orders with no customer, in one query."
SELECT c.name, o.id AS order_id
FROM customers c
FULL OUTER JOIN orders o ON o.customer_id = c.id;
Result keeps Meera (customer, no order) and order 13 (order, no customer), both with blanks:
name | order_id
-------+---------
Riya | 10
Riya | 11
Aditya | 12
Meera | (blank) <- customer with no order
(blank)| 13 <- order with no customer
customers orders FULL keeps
●──────────● EVERYTHING from
( all left + all right ) both sides
Reach for it when you're reconciling two lists and care about what's unmatched on either side: "which records are in the old system but not the new, and vice versa."
CROSS JOIN: every combination
Problem: "Build a grid of every store × every day, so I can spot days with zero sales."
SELECT s.name, d.day
FROM stores s
CROSS JOIN calendar d; -- no ON clause: every store paired with every day
A CROSS JOIN has no ON. It pairs every row of A with every row of B (3 stores × 30 days =
90 rows). Genuinely useful for building complete scaffolds to fill gaps against; but if a CROSS JOIN
happens by accident (you forgot the ON), you get an explosion of rows, a classic bug.
Bonus: the self-join, a table joined to itself
Sometimes the two things you're matching live in the same table. Classic case: an employees
table where each row has a manager_id pointing at another row in the same table.
Problem: "List each employee next to their manager's name."
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id; -- same table, two nicknames
The trick is the two aliases, e for the employee and m for the manager, which let you treat one
table as if it were two. (LEFT JOIN so the CEO, who has no manager, still shows up with a blank.)
Chaining several joins
You can keep joining: each JOIN glues on one more table:
SELECT o.id, c.name, p.title
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN products p ON o.product_id = p.id;
Just know that every INNER join in the chain can drop rows: if an order has no matching product, that whole row disappears. When in doubt about losing rows, LEFT JOIN the optional tables.
The interview trap: a LEFT JOIN that secretly acts like INNER
SELECT o.id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.country = 'IN';
This looks like a LEFT JOIN, but the WHERE c.country = 'IN' quietly throws away the unmatched
rows, because for an unmatched order, c.country is blank, and "is blank equal to 'IN'?" is
"unknown," so the row drops. If you want to keep unmatched rows, put that condition in the ON line
instead of WHERE. This is the single most common join mistake in interviews.