JOINs (Inner, Left, Right, Full, Self)

Intermediate

⏱️ 25 mins read

First schema evolution of the roadmap: employees gains a manager_id column, and departments adds Marketing (id 4) — deliberately empty, making it the perfect anti-join target. Also joins employees (Topic 1), customers (Topic 6), and orders (Topic 7).

What You'll Learn

JOINs combine rows from two or more tables based on a related column. INNER JOIN: only matching rows from both tables. LEFT JOIN: all left rows + matching right rows (NULL if no match). RIGHT JOIN: all right rows + matching left rows. FULL JOIN: all rows from both. SELF JOIN: a table joined to itself.

Syntax

SELECT cols FROM t1
INNER JOIN t2 ON t1.id = t2.fk;

SELECT cols FROM t1
LEFT JOIN t2 ON t1.id = t2.fk;

Example

-- INNER JOIN: only matched orders
SELECT o.id, c.name, o.total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN: all customers, even without orders
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- Find customers with NO orders
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

-- SELF JOIN: employees and their managers
-- (manager_id is added to ShopCo in this topic)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Beyond the Basics

Data in play — reference tables for this topic
-- employees — manager_id added in this topic
-- id | name    | department_id | manager_id | salary
-- 1  | Ada     | 1             | NULL       | 145000  (CEO)
-- 2  | Grace   | 1             | 1          | 115000
-- 3  | Alan    | 2             | 1          | 92000
-- 4  | Edsger  | NULL          | 2          | 78000
-- 5  | Barbara | 3             | 3          | 64000

-- departments — Marketing (id 4) added, has NO employees
-- 1 Engineering, 2 Sales, 3 HR, 4 Marketing

-- customers (Topic 6): Karl, Ines, Omar, Sofia, Maike
-- orders (Topic 7): customers 1(×2), 2, 3, 4 — Maike has none

INNER vs LEFT: where Edsger disappears

Join employees to departments and INNER JOIN returns four rows — Edsger's NULL department_id matches no department, so he is silently gone. LEFT JOIN keeps all five employees and fills his department columns with NULL. Neither join ever shows Marketing: it exists only on the right side, and no employee points at it.

-- INNER: 4 rows — Edsger vanishes without an error
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- LEFT: 5 rows — Edsger survives, department = NULL
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
After any INNER JOIN, ask what the join condition could NOT match — those rows left without a trace, and only LEFT JOIN (plus NULL handling from Topic 7) tells you where they went.

The anti-join: LEFT JOIN ... WHERE right.id IS NULL

Flip the classic LEFT JOIN: keep only the rows where NO match was found. That answers 'which X has never Y?' — departments with no employees (Marketing), customers with no orders (Maike). It is one of the most-asked interview patterns, and it has a sharp edge worth knowing.

SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;
-- Marketing — the empty department finally surfaces

-- Gotcha: if the right side can match MULTIPLE rows per key,
-- one output row appears per missing match. NOT EXISTS is exact:
SELECT d.name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.id);
LEFT JOIN + IS NULL works for existence checks on one-to-one keys; NOT EXISTS says exactly what it means and never duplicates — prefer it when the right side is one-to-many.

Self-join: one table, two aliases — the org chart

A self-join lists a table twice under different aliases, turning 'rows that relate to other rows in the same table' into a plain join. With manager_id now in the schema, two FROM employees lines — e for the employee, m for the manager — draw the whole reporting tree. LEFT JOIN keeps Ada, who reports to nobody.

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Ada     → NULL    (the CEO — kept only by LEFT JOIN)
-- Grace   → Ada
-- Alan    → Ada
-- Edsger  → Grace
-- Barbara → Alan
The alias IS the role: e is the employee, m is the manager. Self-joins also power 'pairs in the same department' (e1.department_id = e2.department_id AND e1.id < e2.id) — the < stops rows pairing with themselves twice.

Common Mistakes

Forgetting the ON condition — this creates a Cartesian product (every row combined with every row), which can produce billions of rows. Also: MySQL doesn't support FULL OUTER JOIN — use LEFT JOIN UNION RIGHT JOIN.

Interview Tips

The 'find rows in A with no match in B' pattern is very common: LEFT JOIN + WHERE b.id IS NULL. Draw Venn diagrams. Know that INNER JOIN can be written as just JOIN. Most analytics questions involve LEFT JOINs.

Official References

Test Yourself — 6 questions
Self-check · 0/6 answered

1. employees INNER JOIN departments on department_id — what happens to Edsger?

2. The same join as a LEFT JOIN (from employees) — what changes?

3. The anti-join 'departments with no employees' is written as…?

4. In a LEFT JOIN, a right-table condition in WHERE (other than IS NULL) does what?

5. The self-join SELECT e.name, m.name FROM employees e LEFT JOIN employees m ON e.manager_id = m.id — what does Ada's row show?

6. Forgetting the ON condition in a join produces…?

Practice

Return each order_id with its total revenue (sum of quantity × product price).

⚡ Solve it in the SQL playground →

Frequently Asked Questions

Does it matter whether I filter in ON or WHERE with a LEFT JOIN?

Immensely. A condition in ON filters which right-side rows may match (left rows survive with NULLs). The same condition in WHERE runs after the join and filters the result — putting a right-table condition there (except IS NULL) silently turns your LEFT JOIN back into an INNER JOIN.

MySQL has no FULL OUTER JOIN — how do I emulate it?

UNION a LEFT JOIN and a RIGHT JOIN of the same pair: UNION (not UNION ALL) collapses the shared matched rows, leaving unmatched rows from both sides. PostgreSQL, SQL Server, and Oracle support FULL OUTER JOIN natively.

Back to Full Roadmap