INNER JOIN: Combining Rows From Two Related Tables

Overview

An INNER JOIN combines rows from two tables based on a matching condition, and keeps only the rows where a match exists on both sides. It's the join you reach for by default whenever you need to enrich one table with descriptive data from another — for example, turning a department_id foreign key on an employee row into a readable department name and location.

You'll use inner joins constantly in reporting and dashboards: raw tables are normalized to avoid repeating data, so joins are how you reassemble a human-readable view at query time.

The Query

Step-by-Step Breakdown

1. FROM employees e — start from the employees table, aliased e so we can reference its columns unambiguously.
2. INNER JOIN departments d ON e.department_id = d.id — for every employee row, find the department row whose id matches that employee's department_id. Only rows that satisfy this condition survive.
3. SELECT e.first_name, e.last_name, d.name, d.location — pull columns from both aliased tables into one result row.
4. ORDER BY d.name, e.last_name — sort first by department, then by last name within each department.

Because every employee in this dataset has a valid, non-null department_id, no rows get dropped here. If an employee had department_id = NULL or a value that didn't match any departments.id, the inner join would silently exclude that employee from the results.

Variations

Filter the same join down to a single department:

The WHERE clause here filters after the join has matched rows. You could also express "give me employees in department 1" without a join at all (WHERE department_id = 1), but joining in the department name is what lets you filter by a readable value like 'Engineering' instead of memorizing IDs.

Common Mistakes

• Omitting the ON condition (or writing FROM employees, departments without a matching WHERE) produces a cartesian product — every employee paired with every department, not just their own.
• Ambiguous column references. Both tables have a name-like column in spirit (departments.name), and many schemas share exact column names like id. Always qualify with the table alias once you're joining.
• Assuming INNER JOIN never loses rows. It silently drops any row on either side that has no match — including rows where the foreign key is NULL. If you need to keep unmatched rows, you want LEFT JOIN instead.
• Filtering in the wrong place. Putting a filter in the ON clause vs. the WHERE clause gives identical results for INNER JOIN, but the two behave very differently once you switch to an outer join — a common source of confusion when a query gets refactored later.