INNER JOIN

A join combines rows from two tables based on a related column. INNER JOIN — the most common kind — returns only the pairs of rows that match. It's how you see order details with customer names, employees with their department names, and so on.

Your first join

SELECT orders.id, orders.order_date, customers.name
FROM orders
INNER JOIN customers ON customers.id = orders.customer_id
WHERE orders.status = 'pending';

For each order, the database finds the customer whose id equals the order's customer_id, and puts the columns side by side. The ON clause is the join condition.

Table aliases make joins readable

SELECT o.id, o.order_date, c.name, c.country
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id
WHERE o.status = 'pending';

JOIN on its own means INNER JOIN. Short aliases like o and c keep column references compact. When two tables have a column with the same name (both have id), you must say which one you mean.

Only matching rows

SELECT e.first_name, e.job_title, d.name AS department
FROM employees AS e
JOIN departments AS d ON d.id = e.department_id
ORDER BY d.name, e.first_name;

Count the rows: 11, not 12. Olivia has no department (NULL), so no department row matches her and an inner join leaves her out. The Research department has no employees, so it doesn't appear either. Lesson 24 shows how to keep unmatched rows.

How a join works

Conceptually, the database considers every combination of a row from the first table and a row from the second, and keeps the combinations where the ON condition is true. In practice it uses much faster methods (indexes, hashing), but the result is the same.

Joins with aggregates

SELECT d.name AS department, COUNT(*) AS staff, SUM(e.salary) AS payroll
FROM employees AS e
JOIN departments AS d ON d.id = e.department_id
GROUP BY d.name
ORDER BY payroll DESC;

Joining first and then grouping by a name from the joined table turns ids into readable labels.

Joins and filtering

SELECT p.name, oi.quantity, oi.unit_price
FROM order_items AS oi
JOIN products AS p ON p.id = oi.product_id
WHERE oi.order_id = 3;

For inner joins, it makes no difference to the result whether a filter goes in WHERE or in the ON clause. Keep join conditions in ON and filters in WHERE for clarity — for outer joins the difference matters (lesson 24).

USING and NATURAL JOIN

-- When both tables name the column identically:
SELECT * FROM orders JOIN payments USING (order_id);

USING (column) is a shortcut when the linking columns have the same name. NATURAL JOIN joins on every column with the same name automatically — avoid it, because adding a column to either table can silently change the join.

The old comma syntax

SELECT o.id, c.name
FROM orders AS o, customers AS c
WHERE c.id = o.customer_id AND o.id <= 3;

Listing tables with commas and putting the condition in WHERE is an older way to write an inner join. It works, but forgetting the condition silently produces every combination of rows. Prefer explicit JOIN … ON.

Try it yourself

List every order line with the product name and category, for orders placed in 2026.