INNER JOIN

What is an INNER JOIN?

An INNER JOIN combines rows from two tables based on a shared column. It returns only the rows where a match exists in both tables. If a row in either table has no match in the other, it is excluded entirely from the results.

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

This returns only employees who have a matching department. Any employee without a department_id, or any department with no employees, would not appear.

The ON clause

The ON clause specifies which columns to match between the two tables. This is almost always a primary key in one table matching a foreign key in the other.

-- employees.department_id matches departments.department_id
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id;

Both sides of the ON condition must reference a column — one from each table. Using the full table.column format avoids ambiguity when both tables share a column name.

Adding WHERE, GROUP BY, and aggregates

An INNER JOIN just combines the tables — you can still use all the other clauses you know on the joined result.

-- Filter after joining
SELECT employees.first_name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
WHERE employees.hire_date > '2020-01-01';

-- Aggregate after joining
SELECT departments.department_name, COUNT(employees.employee_id) AS headcount
FROM employees
INNER JOIN departments ON employees.department_id = departments.department_id
GROUP BY departments.department_name;

Table aliases

When table names are long, you can assign them short aliases using AS (or just a space) to keep queries readable. The alias replaces the full table name everywhere in the query.

-- Using aliases e and d instead of full table names
SELECT e.first_name, e.last_name, d.department_name
FROM employees AS e
INNER JOIN departments AS d ON e.department_id = d.department_id;

Aliases are optional but become very helpful once you're joining three or more tables.

Common mistakes

Forgetting the ON clause — writing INNER JOIN departments without ON ... produces a syntax error. The join condition is always required.

Ambiguous column names — if both tables have a column called customer_id and you write just customer_id in SELECT or WHERE, the database won't know which table you mean and will error. Always qualify shared column names with table.column.

Expecting unmatched rows to appear — INNER JOIN silently drops rows with no match. If you're getting fewer results than expected, check whether some rows are missing a matching value in the other table. Use a LEFT JOIN if you need unmatched rows to appear.