SQL INNER JOIN — Return Only Matching Rows

What Is an INNER JOIN?

An INNER JOIN is the most common type of SQL join. It returns only the rows where the join condition is satisfied in both tables. If a row in the left table has no matching row in the right table, it is excluded from the result entirely — and vice versa.

Syntax

SELECT a.*, b.*
FROM employees AS a
INNER JOIN departments AS b
  ON a.department_id = b.id;

How It Works Step by Step

1. The database engine takes each row from employees (the left table).
2. For each row, it searches departments (the right table) for rows where b.id matches a.department_id.
3. When a match is found, the columns from both tables are combined into a single result row.
4. If no match is found, that employee row is skipped — it does not appear in the output.

Venn Diagram Interpretation

In the classic Venn diagram, INNER JOIN corresponds to the intersection — the overlapping region where both circles meet. Only data that exists in both tables (based on the join key) is included.

Performance Characteristics

INNER JOIN is typically the most performant join type because the database can skip non-matching rows early. Adding an index on the join columns (department_id and id) allows the engine to use index lookups instead of full table scans.

Common Pitfall

If either join column contains NULL values, those rows will never match because NULL = NULL evaluates to NULL (not TRUE) in SQL. Use IS NOT DISTINCT FROM or COALESCE if you need NULL-safe equality.