MySQL Joins

Combining Tables

A JOIN returns rows from two or more tables based on a matching condition. This is the heart of relational databases.

The Dataset

customers(id, name)     orders(id, customer_id, total)

INNER JOIN

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

Returns only rows that match in both tables. Customers with no orders do not appear.

LEFT JOIN

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

Returns every customer, even those without orders. Missing values come back as NULL.

RIGHT JOIN and CROSS JOIN

SELECT c.name, o.total FROM customers c RIGHT JOIN orders o ON o.customer_id = c.id;
SELECT c.name, o.total FROM customers c CROSS JOIN orders o;

RIGHT JOIN is the mirror of LEFT JOIN. CROSS JOIN pairs every row of one table with every row of the other; use it rarely and with care.

Joining Three Tables

SELECT c.name, o.total, p.title
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id;

Key Points

• INNER JOIN keeps only matching rows.
• LEFT JOIN keeps all rows from the left table.
• Always provide an explicit ON condition.
• Multiple joins chain naturally with JOIN ... ON.