SQL Joins

Combine tables with INNER, LEFT, RIGHT and FULL joins — and know which to reach for.

Why joins exist

Relational databases split data into tables to avoid duplication. An order doesn't copy the customer's name and email — it stores a customer_id that references the customers table. Joins put the pieces back together.

customers

id	name
1	Asha
2	Ravi
3	Meera

orders

id	customer_id	amount
101	1	2500
102	1	900
103	2	4200
104	9	300

Meera has no orders. Order 104 points at a customer that doesn't exist.

INNER JOIN

Returns only rows that match on both sides:

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

name	order_id	amount
Asha	101	2500
Asha	102	900
Ravi	103	4200

Meera (no orders) and order 104 (no customer) disappear.

LEFT JOIN

Returns every row from the left table, with NULLs where there's no match on the right:

SELECT c.name, o.id AS order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

name	order_id	amount
Asha	101	2500
Asha	102	900
Ravi	103	4200
Meera	NULL	NULL

Finding rows with no match

A classic pattern: "customers who have never ordered."

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

RIGHT and FULL joins

RIGHT JOIN keeps every row from the right table. Most people just swap the table order and use LEFT JOIN for readability.
FULL OUTER JOIN keeps unmatched rows from both sides (not supported in MySQL — emulate it with UNION).

Join	Left-only rows	Matched rows	Right-only rows
`INNER`	–	✓	–
`LEFT`	✓	✓	–
`RIGHT`	–	✓	✓
`FULL`	✓	✓	✓

Joins + aggregation

SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.amount), 0) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total DESC;

Note COUNT(o.id) rather than COUNT(*) — it gives Meera 0 instead of 1.

Row multiplication

Joining a parent to two different child tables at once (e.g. orders and reviews) multiplies rows, silently inflating SUM and COUNT. Aggregate each child in a subquery first, then join.

⚡ Try it yourself

Add a products table and an order_items(order_id, product_id, qty) table. Write a query listing each customer with the names of every product they've bought.

Interview questions

QWhat's the difference between INNER JOIN and LEFT JOIN?+

INNER JOIN returns only rows with a match in both tables. LEFT JOIN returns all rows from the left table, filling columns from the right table with NULL where no match exists.

QWhy does putting a condition on the right table in WHERE turn a LEFT JOIN into an INNER JOIN?+

Unmatched rows have NULL in the right table's columns, and WHERE right.col = 'x' is false for NULL, so those rows are filtered out. Put such conditions in the ON clause to keep the outer-join behaviour.

Key takeaways

INNER = matches only; LEFT = everything on the left plus matches.
LEFT JOIN ... WHERE right.id IS NULL finds rows with no match.
Watch for row multiplication when joining multiple child tables.