DBMS deep dive
SQL Joins (Inner, Left, Right, Full, Cross): deep dive
The fastest way to master SQL joins is to work them out by hand on a tiny dataset and predict the exact rows. This lesson does that for every join type, then covers the ON-versus-WHERE trap and the algorithms engines use to run joins.
The dataset
employees(id, name, dept_id) has 4 rows: (1, Asha, 10), (2, Ravi, 20), (3, Meena, NULL), (4, Karan, 10). departments(id, name) has 3 rows: (10, Eng), (20, Sales), (30, HR). Meena has no department and HR has no employees. Those two edge cases are where the join types differ.
Exact result of each join on e.dept_id = d.id
| Join | Rows (employee, department) | Count |
|---|---|---|
| INNER | Asha-Eng, Ravi-Sales, Karan-Eng | 3 |
| LEFT | Asha-Eng, Ravi-Sales, Karan-Eng, Meena-NULL | 4 |
| RIGHT | Asha-Eng, Ravi-Sales, Karan-Eng, NULL-HR | 4 |
| FULL OUTER | Asha-Eng, Ravi-Sales, Karan-Eng, Meena-NULL, NULL-HR | 5 |
| CROSS | every employee with every department, 4 × 3 | 12 |
| LEFT anti (WHERE d.id IS NULL) | Meena-NULL | 1 |
Notice two things. Meena's NULL never matches anything, not even another NULL, because NULL = NULL is unknown, not true. And Eng appears twice in the INNER result because two employees point to it. A join returns one row per matching pair, not one row per left-side row.
The ON vs WHERE trap
With an INNER JOIN, a filter means the same thing in ON or in WHERE. With an outer join it does not:
-- A: filter in WHERE runs after the join, so Ravi (Sales) and Meena (NULL) are removed
SELECT e.name, d.name
FROM employees e LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.name = 'Eng';
-- Asha-Eng, Karan-Eng (2 rows: this is effectively an INNER JOIN)
-- B: filter in ON limits which departments may match, all employees survive
SELECT e.name, d.name
FROM employees e LEFT JOIN departments d
ON e.dept_id = d.id AND d.name = 'Eng';
-- Asha-Eng, Ravi-NULL, Meena-NULL, Karan-Eng (4 rows)
-- Anti-join: departments with no employees (returns HR)
SELECT d.name FROM departments d
WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.id);Rule: conditions on the optional (right) table of a LEFT JOIN go in ON, unless you deliberately want to drop the unmatched rows. Also prefer NOT EXISTS over NOT IN for anti-joins. If the subquery returns even one NULL, NOT IN returns no rows at all. Try WHERE d.id NOT IN (SELECT dept_id FROM employees) on this data and you get nothing back, because of Meena.
Self join and semi join
A self join gives the same table two aliases, for example employees e JOIN employees m ON e.manager_id = m.id to list each employee next to their manager. A semi join (WHERE EXISTS (...)) returns each left row at most once, however many matches it has. Use it when you only care whether a match exists, because it avoids the duplicates that a plain JOIN plus DISTINCT would have to remove.
How engines actually execute joins
| Algorithm | How it works | Best when |
|---|---|---|
| Nested loop | For each outer row, look up matching inner rows (ideally through an index) | Outer side is small and the inner join column is indexed |
| Hash join | Build a hash table on the smaller input, then probe it with the larger one | Large unsorted inputs with an equality condition |
| Merge join | Walk two inputs sorted on the join key in step | Both inputs already sorted, such as from indexes, or the output must be ordered |
You never pick the algorithm in SQL. The planner does, based on row estimates. When a join is slow, the cause is usually a bad estimate or a missing index on the inner join column. The query optimization lesson shows how to spot that in a plan.
Common mistakes
- Joining two one-to-many children (orders and payments) to the same parent and then summing, which multiplies the totals. Aggregate each child in a subquery first.
- Putting right-table filters in WHERE and silently turning a LEFT JOIN into an INNER JOIN.
- Using
NOT INwith a nullable subquery column. - Fixing duplicate rows with
DISTINCTinstead of correcting the join condition. - Forgetting that
COUNT(*)counts the NULL-extended rows of a LEFT JOIN. UseCOUNT(d.id)to count real matches.
Interview questions
›How many rows can an INNER JOIN of tables with m and n rows return?
Anywhere from 0 to m × n. Zero when nothing matches, and m × n when every row matches every row, for example when all join keys share one value.
›How do you emulate FULL OUTER JOIN in MySQL?
MySQL has no FULL OUTER JOIN. Take the LEFT JOIN result and UNION ALL it with the rows of a RIGHT JOIN where the left key IS NULL. That adds only the unmatched right-side rows, so matched rows are not duplicated.
›What is the difference between a semi join and an inner join?
An inner join returns one row per matching pair, so a left row can repeat. A semi join, written as EXISTS or IN, returns each left row at most once and exposes no columns from the right table.
›What does USING do, and what is a NATURAL JOIN?
USING (col) joins on equally named columns and outputs that column once. NATURAL JOIN implicitly joins on every column with the same name. It is risky, because adding a column such as updated_at to both tables silently changes the join.
›Find the second highest salary per department using a join-free approach.
Use a window function: SELECT * FROM (SELECT e.*, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r FROM employees e) t WHERE r = 2. DENSE_RANK handles ties correctly.