JOINs are the most frequently asked-about SQL topic in interviews — and the one where vague answers ("INNER JOIN gets matching rows on both sides") lose candidates the offer. What interviewers want to see is that you can predict exactly which rows appear in the output, including the edge cases with NULLs, duplicates, and unmatched rows.
This guide uses a single, consistent dataset throughout so you can see exactly what changes between each join type.
The sample tables
customers: orders:
┌────┬──────────┐ ┌────┬─────────────┬──────────┐
│ id │ name │ │ id │ customer_id │ product │
├────┼──────────┤ ├────┼─────────────┼──────────┤
│ 1 │ Alice │ │ 10 │ 1 │ Laptop │
│ 2 │ Raman │ │ 11 │ 1 │ Mouse │
│ 3 │ Bob │ │ 12 │ 3 │ Keyboard │
│ 4 │ Carol │ │ 13 │ 5 │ Monitor │
└────┴──────────┘ └────┴─────────────┴──────────┘
Note: Raman (id 2) has NO orders
Carol (id 4) has NO orders
Order 13 references customer_id 5 which does NOT EXIST
INNER JOIN — only matching rows from both tables
SELECT c.name, o.product FROM customers c INNER JOIN orders o ON c.id = o.customer_id;
┌───────┬──────────┐
│ name │ product │
├───────┼──────────┤
│ Alice │ Laptop │ ← customer 1 matched order 10
│ Alice │ Mouse │ ← customer 1 matched order 11
│ Bob │ Keyboard │ ← customer 3 matched order 12
└───────┴──────────┘
Excluded: Raman (no orders) ✗
Carol (no orders) ✗
Order 13 (customer_id=5, no customer) ✗
INNER JOIN is the most common join. Use it when you only want rows where the relationship exists on both sides.
LEFT JOIN — all from left, matching from right (NULLs if no match)
SELECT c.name, o.product FROM customers c LEFT JOIN orders o ON c.id = o.customer_id;
┌───────┬──────────┐ │ name │ product │ ├───────┼──────────┤ │ Alice │ Laptop │ │ Alice │ Mouse │ │ Raman │ NULL │ ← Raman has no orders → NULL on the right │ Bob │ Keyboard │ │ Carol │ NULL │ ← Carol has no orders → NULL on the right └───────┴──────────┘ Order 13 still excluded (it references a non-existent customer)
LEFT JOIN is the second most common join. Use it when you need all records from the left table regardless of whether a match exists.
RIGHT JOIN — all from right, matching from left
RIGHT JOIN is LEFT JOIN with the tables swapped. Most SQL developers just rewrite RIGHT JOINs as LEFT JOINs with the table order swapped — it's more readable.
SELECT c.name, o.product FROM customers c RIGHT JOIN orders o ON c.id = o.customer_id;
┌───────┬──────────┐ │ name │ product │ ├───────┼──────────┤ │ Alice │ Laptop │ │ Alice │ Mouse │ │ Bob │ Keyboard │ │ NULL │ Monitor │ ← Order 13 has no matching customer → NULL on left └───────┴──────────┘ Raman and Carol excluded (no orders on the right side)
FULL OUTER JOIN — all rows from both sides
SELECT c.name, o.product FROM customers c FULL OUTER JOIN orders o ON c.id = o.customer_id;
┌───────┬──────────┐ │ name │ product │ ├───────┼──────────┤ │ Alice │ Laptop │ │ Alice │ Mouse │ │ Raman │ NULL │ ← left-only │ Bob │ Keyboard │ │ Carol │ NULL │ ← left-only │ NULL │ Monitor │ ← right-only (orphaned order) └───────┴──────────┘
The anti-join pattern — find rows with NO match
This is the most commonly missed interview pattern. "Show me customers who have never placed an order."
SELECT c.name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL; -- ← the key: keep only the NULLs -- Result: Raman, Carol (no orders)
SELF JOIN — a table joined to itself
SELECT e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- The same table, aliased twice as e and m
Quick reference
| JOIN type | Returns | NULL rows? |
|---|---|---|
| INNER JOIN | Matching rows only | Never |
| LEFT JOIN | All left + matching right | Right side when no match |
| RIGHT JOIN | All right + matching left | Left side when no match |
| FULL OUTER JOIN | All rows from both | Either side when no match |
| CROSS JOIN | Every combination (M×N) | Never — no ON clause |
| SELF JOIN | Table joined to itself | Depends on LEFT vs INNER |