INNER JOIN — Linking Tables

Why tables are split

Every query so far read one table. Real databases are not built that way: they keep each kind of fact in its own table, so a department's name lives once in departments and every employee just stores a department_id that points at it. Splitting data like this avoids contradiction — change a department's city in one place and every employee sees the update.

The cost is that the answer to "who works in Oslo?" is now scattered: names and department ids in employees, cities in departments. A join is how you stitch them back together for a single question, following the id pointers from one table to the next.

erDiagram
  DEPARTMENTS ||--o{ EMPLOYEES : "has"
  DEPARTMENTS {
    int id
    text name
  }
  EMPLOYEES {
    int id
    text name
    int department_id
  }

INNER JOIN keeps only rows where both tables have a matching link.

INNER JOIN keeps the overlap

INNER JOIN takes two tables and returns only the rows that match on both sides — the overlap in a Venn diagram. The ON clause tells the database how two rows count as a match, almost always by following a foreign key:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments
  ON employees.department_id = departments.id;

Read the ON aloud as "an employee's department id equals a department's id." Forget the ON and you get a Cartesian product — every employee paired with every department, hundreds of nonsense rows — which is almost never what you want.

Run this, then try swapping the table order or adding a WHERE clause.

SELECT e.name AS employee_name, d.name AS department_name, d.city
FROM employees e
JOIN departments d ON e.department_id = d.id;

Aliases save typing

Typing employees.department_id on every line gets old fast and obscures the query. A table alias is a short nickname you give a table for the duration of one query:

SELECT e.name, d.name
FROM employees AS e
INNER JOIN departments AS d
  ON e.department_id = d.id;

The AS is optional — FROM employees e means the same thing — but spelling it out makes the nickname obvious to the next reader. Once you alias a table, use that alias everywhere; mixing e and employees in the same query works but reads as careless.

Reach across a join: each project and the city of its owning department.

SELECT p.name AS project, d.city AS city
FROM projects p
JOIN departments d ON p.department_id = d.id;

flowchart LR
  A["Ada, dept 1"] -->|"matches dept 1"| M["Kept in result"]
  B["Omar, dept NULL"] -.->|"no department to match"| D["Dropped"]
  style M fill:#0e7490,color:#fff
  style D fill:#b45309,color:#fff

INNER JOIN keeps matched rows and silently drops any row with no partner.

Matches you do not see

Because INNER JOIN keeps only matching rows, anything without a partner vanishes from the result. Omar Aziz has a NULL department_id, so no department row matches him — he is simply absent from every inner join of employees to departments, even though he is still an employee.

This is correct behaviour, not a bug, but it is easy to forget. If a count suddenly looks low, ask whether the join quietly excluded rows with no match. The next lesson introduces LEFT JOIN, which keeps the unmatched rows and fills their missing side with NULL instead of dropping them — exactly for questions like "list every employee, and their department if they have one."

Joining more than two tables

The same JOIN ... ON shape chains to a third table and beyond. Each join follows one more set of id pointers, and you simply add another join for each table you need to reach.

To see every project alongside the department that owns it and the city that department sits in, join projects to departments on the project's department id — one join, two tables — and the city comes along for free because it already lives on the department row. Add a second join only when the answer truly lives in a third table; each extra join is another set of row-pairings to keep straight.

Joins compose with WHERE and ORDER BY

A join is just part of the FROM clause, so everything you already know still applies. Filter the combined rows with WHERE, sort them with ORDER BY, and pick columns with SELECT — all after the join has stitched the tables together.

The order to keep in mind: the join runs first and builds the matched rows, then WHERE trims them, then ORDER BY arranges the survivors. You are never choosing between a join and a filter; a real query usually uses both.

Exercise

List every employee's name and their department's name. Use table aliases e and d.

SELECT e.name, d.name
FROM employees e
-- add your JOIN and ON here
;

SELECT e.name, d.name FROM employees e JOIN departments d ON e.department_id = d.id;

Exercise

Return the name of each active project and the name of the department that owns it. Only include rows where the project status is 'active'.

SELECT p.name, d.name
FROM projects p
-- join and filter here
;

SELECT p.name, d.name FROM projects p JOIN departments d ON p.department_id = d.id WHERE p.status = 'active';

Exercise

This query pairs each project with its owning department, but it joins on the wrong column — p.id = d.id matches a project's id against a department's id, which is meaningless and returns nonsense pairs. Fix the ON condition to follow the real foreign-key link.

SELECT p.name, d.name
FROM projects p
JOIN departments d ON p.id = d.id
;

SELECT p.name, d.name FROM projects p JOIN departments d ON p.department_id = d.id;

Exercise

List the name of every employee who works in the Engineering department. Filter by the department's name (not its id), which means you must join to departments first.

SELECT e.name
FROM employees e
-- join to departments and filter by name here
;

SELECT e.name FROM employees e JOIN departments d ON e.department_id = d.id WHERE d.name = 'Engineering';

Recap

A join follows id pointers to reassemble data split across tables.
INNER JOIN ... ON keeps only rows that match on both sides.
Forget ON and you get a Cartesian product — every row times every row.
Alias tables (employees AS e) and qualify shared columns (e.name).
INNER JOIN silently drops rows with no match — Omar disappears.

Next you'll meet LEFT JOIN, which keeps those unmatched rows instead of dropping them.

INNER JOIN — Linking Tables

Why tables are split

INNER JOIN keeps the overlap

Aliases save typing

Matches you do not see

Joining more than two tables

Joins compose with WHERE and ORDER BY

Recap

Checkpoint quiz

Go deeper — technical resources