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
FROM employees AS e: take every employee and call the table e.JOIN departments AS d ON d.dept_id = e.dept_id: for each employee, find departments with the same dept_id.- Ben (dept_id 1) matches Engineering, so one row: Ben, Engineering.
- Gus (dept_id NULL) matches nothing, because NULL never equals anything — so INNER JOIN drops him.
- HR (dept_id 4) is never matched by any employee, so it doesn't appear either.
- SELECT picks e.name and d.dept_name from each matched pair.
Key points
- A join pairs rows from two tables where the ON condition is true; each pair is one result row.
- INNER JOIN (or just JOIN) keeps only matched rows — unmatched rows on either side vanish.
- NULL keys never match, so rows with a NULL key always drop out of an INNER JOIN.
- Table aliases (e, d) keep queries short. Always prefix columns with them once there is more than one table.
- One-to-many joins repeat the "one" side once per match. That is expected.
Common mistakes
- Forgetting the ON condition, which pairs every row with every row (a cross join): 8 × 4 = 32 rows.
- Leaving out the prefix on a column both tables have (dept_id), which gives an "ambiguous column" error.
- Using INNER JOIN when the question says "all customers" or "every department" — the ones with no match disappear.
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.