Joins, and INNER JOIN

A JOIN combines rows from two tables wherever a condition (usually matching keys) is true, and INNER JOIN keeps only the rows that found a match on both sides.

Why it matters

The employees table knows Ben is in department 1, but not that department 1 is called Engineering. That lives in departments. To show "Ben — Engineering" you need columns from both tables in one row. That is a join.

The idea

You name two tables and a join condition after ON: employees e JOIN departments d ON d.dept_id = e.dept_id. The database pairs every employee with every department whose dept_id matches, and each pair becomes one result row holding the columns of both.

INNER JOIN (plain JOIN means the same thing) keeps only pairs that matched. So Gus, whose dept_id is NULL, matches nothing and disappears — and HR, which no employee points to, disappears too.

The short names e and d are table aliases; they save typing and say which table each column comes from.

Matching odd socks from two baskets by colour. A red sock from basket A is paired with a red sock from basket B. INNER JOIN only keeps the pairs; any sock without a partner stays in its basket.

Socks pair one-to-one. Joins don't have to: if one department matches four employees, the department's row is repeated four times — once per partner. That repetition is normal, but it is also where double-counting comes from (see Join traps).

Syntax

SELECT a.column, b.column
FROM table_a AS a
INNER JOIN table_b AS b ON b.key = a.key;

How it works

  1. FROM employees AS e: take every employee and call the table e.
  2. JOIN departments AS d ON d.dept_id = e.dept_id: for each employee, find departments with the same dept_id.
  3. Ben (dept_id 1) matches Engineering, so one row: Ben, Engineering.
  4. Gus (dept_id NULL) matches nothing, because NULL never equals anything — so INNER JOIN drops him.
  5. HR (dept_id 4) is never matched by any employee, so it doesn't appear either.
  6. SELECT picks e.name and d.dept_name from each matched pair.

Key points

Common mistakes

Not true: Leaving out the ON condition makes a join return no rows.
In fact: Without ON, every row is paired with every row (a cross join): 8 employees and 4 departments give 32 rows.

In the interview

A join combines rows from two tables based on a condition, normally a foreign key matching a primary key. INNER JOIN returns only rows that match on both sides, so rows with no partner — or a NULL key — are excluded. In a one-to-many join the one side repeats for each match, which is correct but means I have to be careful when summing columns from the one side.

Part of Unit 3: Joins: combining tables in the free SQL course.