Joining Tables in MySQL

Relational databases split data across multiple tables to avoid repetition. JOINs let you recombine that data in a single query.

Consider two tables: orders and customers. An INNER JOIN returns only rows that have a match in both tables:

SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.total > 100
ORDER BY o.total DESC;

A LEFT JOIN returns all rows from the left table plus matching rows from the right. Unmatched right-table columns are NULL, which is useful for finding orphaned records:

-- Find customers who have placed no orders
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

You can join more than two tables. Keep it readable by using aliases:

SELECT o.id, c.name, p.name AS product, oi.qty
FROM orders o
JOIN customers c  ON o.customer_id  = c.id
JOIN order_items oi ON oi.order_id  = o.id
JOIN products p   ON oi.product_id  = p.id
WHERE o.created >= '2025-01-01';

When joining on columns that appear in both tables, use the table alias prefix to avoid ambiguity. Always index foreign key columns to keep JOINs fast.