INNER JOIN

Real databases spread information across tables. employees stores a dept_id, not a department name; the name lives in departments. A join puts them back together.

SQL
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
ORDER BY d.name, e.first_name;

JOIN means INNER JOIN — the word INNER is optional and usually omitted.

Reading a join

Three parts, and each answers a question:

FROM employees e — the first table, aliased e
JOIN departments d — the second table, aliased d
ON d.dept_id = e.dept_id — which rows correspond

The ON clause is the important one. For each employee, the database finds the department rows where the condition is true and pairs them up. Get ON wrong and you do not get an error, you get a different answer.

Why the aliases

Two tables can have columns with the same name — both of these have dept_id, and departments has a name while employees has first_name. Qualifying every column with its table (e.first_name, d.name) removes the ambiguity before it starts. Referring to a bare dept_id here is an "ambiguous column" error, and it is the second most common join mistake.

Short aliases are the convention. e and d are fine; single letters stop being fine somewhere around the fourth table.

INNER JOIN keeps only the matches

This is the part that costs people money.

SQL
SELECT COUNT(*) AS employees_total FROM employees;

SQL
SELECT COUNT(*) AS after_the_join
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id;

Ten employees; nine after the join. One employee has a NULL dept_id, and NULL = anything is never true, so that person matched nothing and was dropped.

No warning appears. A headcount report built on this join is simply wrong by one person, and the only way to notice is to compare it against the unjoined count — which is a habit worth forming.

The same thing happens in the other direction: a department with no employees never appears in the result at all, because nothing matched it. Both problems have the same answer, LEFT JOIN, which is the next lesson.

Joining more than two tables

Each JOIN adds one table and one ON:

SQL
SELECT c.name AS customer, o.order_id, o.amount, p.method
FROM customers c
JOIN orders   o ON o.customer_id = c.customer_id
JOIN payments p ON p.order_id    = o.order_id
ORDER BY o.order_id;

Five rows out of eight orders. Each INNER JOIN narrows further: orders with no matching customer go first, then orders with no payment. Chaining inner joins is a reliable way to quietly lose rows, and the more tables you chain, the less obvious it is which link dropped them.

Join conditions are not only equality

ON takes any boolean expression. ON a.x = b.x (an equi-join) is the overwhelming majority, but ON o.placed_at BETWEEN c.start AND c.end is perfectly legal and useful for date-range matching.

You can also put extra conditions in ON:

SQL
SELECT e.first_name, d.name AS department
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id AND e.active = 1
ORDER BY e.emp_id;

For an INNER JOIN, a condition in ON and the same condition in WHERE give the same result. For an outer join they do not — which is the whole subject of the lesson after next.

USING, when the columns share a name

SQL
SELECT first_name, name AS department
FROM employees
JOIN departments USING (dept_id)
ORDER BY emp_id;

USING (dept_id) is shorthand for ON e.dept_id = d.dept_id, and it merges the two columns into one so dept_id is no longer ambiguous. It works on PostgreSQL, MySQL, SQLite and Oracle, but not SQL Server.

Avoid NATURAL JOIN, which joins on every commonly-named column automatically. It reads well and then breaks silently the day someone adds a created_at column to both tables.

Common mistakes

Forgetting ON — you get a cross join, covered in lesson 22.
A bare ambiguous column — qualify everything.
Not noticing dropped rows — compare against the unjoined count.
NATURAL JOIN — convenient until a schema change makes it wrong.

Interview question

What is the difference between an INNER JOIN and a LEFT JOIN?

INNER returns only rows with a match on both sides; LEFT returns every row from the left table, filling the right side with NULLs where nothing matched. The detail that separates answers: rows with NULL in the join key never match anything, even in an inner join to a table that contains NULLs too.

Check yourself

List every order with its customer's name.
Why does joining employees to departments return nine rows, not ten?
What does USING (dept_id) do that ON does not?