Understanding SQL Indexes and Query Performance

Part 11 of 16 · ~3 min

EXISTS vs. IN vs. JOIN for Large Datasets

A reorg happened. Several departments were dissolved and their rows deleted from Departments, but the employee records still carry the old department names in Employees.dept — nothing enforces otherwise, since the schema joins these two tables on a text column with no foreign key. The pay-band screen now needs to list only employees whose department still exists.

Three ways to express that, all correct:

-- EXISTS: stops at the first match per outer row; nothing is materialized
SELECT e.name, e.dept FROM Employees e
WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.dept = e.dept);

-- IN: conceptually needs the subquery's full result available up front
SELECT e.name, e.dept FROM Employees e
WHERE e.dept IN (SELECT dept FROM Departments);

-- JOIN: same rows, provided Departments.dept is unique — otherwise this duplicates
SELECT e.name, e.dept FROM Employees e
JOIN Departments d ON d.dept = e.dept;

On the code lab's data all three return the same five employees, because nothing there is orphaned yet — every Employees.dept value still has a row in Departments. In production, after the reorg, they are the difference between a clean screen and one listing people in departments that no longer exist.

The old advice that EXISTS is always faster has not aged well: modern optimizers routinely rewrite IN subqueries into semi-joins and produce identical plans for the first two. Check the plan before believing any ranking between them.

EXISTS is still the better default, for three concrete reasons rather than folklore.

It cannot silently duplicate rows. The JOIN version is only equivalent while Departments.dept is unique. The schema declares dept_id as the primary key, not dept — so nothing prevents two rows both named 'Sales', and the day that happens, every Sales employee appears twice in the results. EXISTS asks "is there at least one match" and stops there, so duplicates on the inner side cannot change its output. This is a real production bug class: a join that has always been fine starts double-counting because somebody inserted a lookup row.

It short-circuits. EXISTS needs one matching row and stops looking. On engines that do not optimize the two forms identically, IN may build the subquery's full result before the outer query starts, which matters when the subquery is large.

It sidesteps the NOT IN / NULL trap — and this schema has the ingredients. Reverse the question to "employees whose salary does not appear in IT" and the trap springs:

-- Returns ZERO rows. Dave's salary is NULL, and that is enough to empty the result.
SELECT name FROM Employees
WHERE salary NOT IN (SELECT salary FROM Employees WHERE dept = 'IT');

-- Returns Alice, Bob, Dave, Eve — the answer you meant.
SELECT e.name FROM Employees e
WHERE NOT EXISTS (
  SELECT 1 FROM Employees i WHERE i.dept = 'IT' AND i.salary = e.salary
);

NOT IN against a set containing a single NULL returns no rows, ever, for any input — because salary <> NULL evaluates to UNKNOWN rather than true, and NOT IN needs every comparison to be true. No error, no warning, just an empty result that looks like a legitimate answer. Note that EXISTS against the first query's Departments.dept is safe either way, since that column is declared NOT NULL — the danger is specifically a nullable column in the subquery, and salary is the nullable one here. The three-valued logic behind it is worked through in SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries.

The indexing point underneath all three forms is the same, and it is the one that actually decides the performance: every one of them repeatedly asks "does this dept value exist in Departments." That question wants an index on Departments.dept, which the schema does not give you — dept_id is the primary key, so dept is unindexed. Whichever form you write, add that index.