Joins, and INNER JOIN

Step 1 Understand it

Why you need it. 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.

Watch it happen

Picture it

flowchart LR
  subgraph E["employees"]
    Ben["Ben · dept 1"]
    Dara["Dara · dept 2"]
    Gus["Gus · dept NULL"]
  end
  subgraph D["departments"]
    D1["1 · Engineering"]
    D2["2 · Sales"]
    D4["4 · HR"]
  end
  Ben -->|"match"| D1
  Dara -->|"match"| D2
  Gus -.->|"no match: dropped"| X["not in result"]
  D4 -.->|"no match: dropped"| X

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.

The shape of it

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

Words in lower case are the parts you fill in.

How it works, step by step

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.

Analogy

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.

Where the analogy breaks

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).

Worked examples

SELECT e.name, d.dept_name
FROM employees AS e
INNER JOIN departments AS d ON d.dept_id = e.dept_id
ORDER BY e.name;

SELECT o.order_id, c.name, o.amount
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_date < '2024-02-01'
ORDER BY o.order_id;

SELECT c.city, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
GROUP BY c.city
ORDER BY revenue DESC;

Must-know 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.

In other databases

• Old-style joins list tables with commas and put the condition in WHERE: FROM employees e, departments d WHERE d.dept_id = e.dept_id. It works everywhere, but explicit JOIN … ON is clearer and is what interviewers expect.

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Watch

  CS50's Introduction to Databases with SQL — Lecture 1: Relating

  Harvard, free

• Read

  W3Schools — SQL INNER JOIN

  short page with a try-it editor, free

• Practice

  SQLBolt — Lesson 6: Multi-table queries with JOINs

  interactive exercises in the browser, free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain what INNER JOIN does with Gus and with HR, and why.

   Show answer

   INNER JOIN pairs each employee with the department whose dept_id matches theirs, and keeps only the pairs where ON d.dept_id = e.dept_id is true.

   Gus: his dept_id is NULL. NULL equals nothing, not even another NULL, so he matches no department and is dropped.

   HR: it is department 4, and no employee has dept_id 4. It matches no employee, so it is dropped too.

   So INNER JOIN quietly loses rows from both sides that have no partner. You get 7 rows, not 8 employees or 4 departments. If you need the unmatched rows, use a LEFT (or FULL) JOIN.

2. Writefrom a blank page, then run it

   Write a query listing each order_items row with the product name and the quantity, for order 105.

   Show answer

   Start from order_items, since that is where the quantity is, and join products to swap product_id for a name. Order 105 has 1 backpack and 3 headphones: 30 + 3 × 40 = £150, its amount.

   sqlCopy

   SELECT oi.order_id, p.product_name, oi.quantity
   FROM order_items oi
   JOIN products p ON p.product_id = oi.product_id
   WHERE oi.order_id = 105
   ORDER BY p.product_name;

   Try it

   2 rows

   order_id	product_name	quantity
   105	Backpack	1
   105	Headphones	3

3. Predictsay it before you run it

   How many rows does SELECT * FROM orders o JOIN customers c ON c.customer_id = o.customer_id; return?

   Show answer

   10 rows, one per order. Every order has exactly one customer: customer_id is NOT NULL and points at a real customer. So each order finds one partner, and none is lost or repeated.

   Omar and the second Tom have no orders, so they don't appear, but that doesn't change the count: you start from orders. (It also returns 10 columns, 5 from each table, so the check below just counts.)

   sqlCopy

   SELECT COUNT(*) AS joined_rows
   FROM orders o
   JOIN customers c ON c.customer_id = o.customer_id;

   Try it

   1 row

   joined_rows
   10

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Which employees vanish from SELECT e.name, m.name FROM employees e JOIN employees m ON m.emp_id = e.manager_id;? Why?

   Show answer

   Asha vanishes. 7 rows come back, not 8.

   The join looks for the row m whose emp_id equals e's manager_id. Asha's manager_id is NULL, so no row matches her, and an INNER JOIN drops rows with no match. To keep her, with NULL as her manager, use LEFT JOIN.

   (The aliases employee and manager are added below, because both columns would otherwise be called name.)

   sqlCopy

   SELECT e.name AS employee, m.name AS manager
   FROM employees e
   JOIN employees m ON m.emp_id = e.manager_id
   ORDER BY e.emp_id;

   Try it

   7 rows

   employee	manager
   Ben	Asha
   Chen	Asha
   Dara	Asha
   Eli	Dara
   Fatima	Asha
   Gus	Dara
   Hana	Ben

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

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.

Follow-up questions

Tick each check when you can do it without notes