JOIN patterns you will meet weekly: inner joins for matching rows, outer joins when data may be missing, and self-joins for hierarchies. Solid JOINs are the backbone of reporting on a normalised schema.
INNER JOIN – only matches
INNER JOIN returns rows where both sides match the join condition. Orders without customers (orphans) and customers without orders drop out.
SELECT
o.id AS order_id,
o.placed_at,
c.name AS customer_name,
c.email
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
ORDER BY o.placed_at DESC;Alias tables (o, c) when queries grow. Explicit INNER JOIN ... ON is clearer than old comma syntax in the FROM clause.
Joining three or more tables
Chain joins along foreign keys – order to customer, order to line items, line items to products:
SELECT
o.id AS order_id,
c.name AS customer,
p.name AS product,
oi.quantity,
oi.unit_price,
(oi.quantity * oi.unit_price) AS line_total
FROM orders o
INNER JOIN customers c ON c.id = o.customer_id
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN products p ON p.id = oi.product_id
WHERE o.placed_at >= '2026-01-01';Each JOIN should follow a relationship you modelled. If you need a fourth or fifth join, check whether the schema or the question needs simplifying.
LEFT JOIN – keep the left side
LEFT JOIN returns all rows from the left table, with matching right-side columns or NULL when there is no match. Use it when the left entity must appear even if the related data is missing.
-- All customers, with order count (zero if none)
SELECT
c.id,
c.name,
COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name;
-- Products never ordered
SELECT p.id, p.name
FROM products p
LEFT JOIN order_items oi ON oi.product_id = p.id
WHERE oi.id IS NULL;The WHERE oi.id IS NULL pattern finds rows with no match on the right – “products never sold”. Put filters on the optional side carefully: conditions on right-table columns in WHERE turn LEFT JOIN into INNER JOIN behaviour. Use AND in the ON clause when you mean to preserve nulls.
RIGHT JOIN and FULL OUTER JOIN
RIGHT JOIN is LEFT JOIN with tables swapped – MySQL supports it but you rarely need it; flip the table order instead. MySQL has no native FULL OUTER JOIN; simulate with UNION of LEFT and RIGHT joins if you ever need both unmatched sides (uncommon in app code).
Self-join
Join a table to itself – useful for employee/manager hierarchies or comment threads:
CREATE TABLE employees (
id INT UNSIGNED PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager_id INT UNSIGNED NULL,
FOREIGN KEY (manager_id) REFERENCES employees(id)
);
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;Aggregation with JOINs
When you JOIN before GROUP BY, mind duplicate rows – one order with three line items triples the order row before aggregation. Use subqueries or aggregate in stages:
-- Revenue per customer (correct)
SELECT
c.id,
c.name,
SUM(oi.quantity * oi.unit_price) AS total_spent
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
INNER JOIN order_items oi ON oi.order_id = o.id
GROUP BY c.id, c.name
HAVING total_spent > 100;Multiplying counts accidentally is a classic bug – always sanity-check totals against a simpler query.
Readable JOIN habits
- One JOIN per line; align ON conditions
- Short table aliases that mean something
- List SELECT columns explicitly in production code – avoid
SELECT *in joins - Index every column you join on
The next tutorial compares subqueries, JOINs, and CTEs – three ways to combine data when one flat JOIN is not enough.