INNER JOIN
🏠The everyday picture
Matching two lists side by side: the order book says "order 55 by customer 12", and the customer register says "customer 12 is Asha Kumari, Patna". Putting them together gives "order 55, Asha Kumari, Patna".
📘What it really is
- A join combines rows from two tables where a condition matches, usually a foreign key with a primary key:
SELECT o.id, o.placed_at, c.name, c.city, o.total_paise
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
ORDER BY o.placed_at DESC;
JOIN(also writtenINNER JOIN) keeps only rows that match on both sides.-
Table aliases (
o,c) keep queries short. Prefix columns with the alias when both tables have a column of the same name (id). -
Join several tables in a chain:
SELECT o.id, c.name, p.name AS product, oi.quantity
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;
- Follow your ER diagram's lines (Module 53): each line is a possible join.
- Forgetting the
ONcondition (or getting it wrong) multiplies rows, pairing every row with every other. If a result is suspiciously large, check the joins.
🖱️Try it
Write queries for: each order with the customer's name; each order item with the order date, customer name and product name; every payment with the customer who paid it.
✅Quick check
What does an INNER JOIN do with an order whose customer doesn't exist?
Show the answer
Leaves it out; only rows with matches on both sides appear.
A join returns 40,000 rows from two tables of 200 rows each. What's likely wrong?
Show the answer
The join condition is missing or wrong, pairing every row with every other.