Topic 27 of 52
INNER JOIN
Overview
INNER JOIN returns only the rows where the join condition matches in BOTH tables. It is the most common join type and the default when you write just JOIN (without LEFT, RIGHT, or FULL).
Syntax
sql
-- Syntax (INNER is optional)
SELECT columns FROM table_a
INNER JOIN table_b ON table_a.id = table_b.table_a_id;
-- Same as:
SELECT columns FROM table_a
JOIN table_b ON table_a.id = table_b.table_a_id;
-- Multiple INNER JOINs
SELECT
o.id AS order_id,
u.name AS customer,
p.name AS product,
oi.quantity,
oi.unit_price
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN users u ON o.user_id = u.id
JOIN products p ON oi.product_id = p.id;
-- INNER JOIN with additional conditions
JOIN orders o ON u.id = o.user_id AND o.status = 'completed'Common Pitfalls
- INNER JOIN silently drops rows with no match — if you expect all rows from the left table, you need LEFT JOIN.
- Check for correct ON conditions — joining on the wrong columns returns incorrect (but no error) results.
- Interview tip: 'Find employees who have managers' = INNER JOIN employees on manager_id. 'Find all employees (including those without managers)' = LEFT JOIN.
Real-World Example
Complete order receipt query using multiple INNER JOINs:
example
sql
-- Generate order receipt
SELECT
o.order_number,
o.created_at AS order_date,
u.name AS customer_name,
u.email AS customer_email,
p.name AS product_name,
p.sku,
oi.quantity,
oi.unit_price,
oi.quantity * oi.unit_price AS line_total,
cat.name AS category,
o.status
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
JOIN categories cat ON p.category_id = cat.id
WHERE o.order_number = 'ORD-2025-001234'
ORDER BY oi.id;