Combining tables with JOIN

What is a JOIN?

A JOIN combines multiple tables on a related column to retrieve data together. Real-world data is spread across multiple tables, so JOIN is one of the most frequently used pieces of SQL syntax in actual work.

INNER JOIN

Retrieves only the data that matches in both tables. Users with no orders are not included in the result.

SELECT o.id, u.name, o.order_date, o.status
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

LEFT JOIN

Retrieves all of the left table's data, plus any matching rows from the right table. When there's no match, the result is NULL. Use this when you want to show "everyone, including users who have never placed an order."

SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

Table aliases

Giving a table a short alias right after its name makes SQL easier to read. INNER JOIN can also be shortened to just JOIN.

SELECT o.id, u.name, o.status
FROM orders o
JOIN users u ON o.user_id = u.id
ORDER BY o.order_date;

Joining three tables

JOINs can be chained together. You can get "who bought what, and how many" in a single query.

SELECT u.name, p.name AS product, op.quantity
FROM orders o
JOIN users u          ON o.user_id = u.id
JOIN order_products op ON o.id = op.order_id
JOIN products p       ON op.product_id = p.id;