SQL Fundamentals: Joins, NULL, Aggregate Functions, and Subqueries

Part 13 of 18 · ~3 min

EXISTS vs. IN vs. JOIN

Back to the department-integrity question from the FULL OUTER JOIN subsection — "does this employee's dept actually exist in Departments" — because it's a good vehicle for comparing three ways of asking the same yes-or-no question:

-- EXISTS: checks each outer row for at least one match and moves on the instant it finds one
SELECT e.name FROM Employees e
WHERE EXISTS (SELECT 1 FROM Departments d WHERE d.dept = e.dept);

-- IN: compares the outer value against a whole materialized list from the subquery
SELECT e.name FROM Employees e
WHERE e.dept IN (SELECT dept FROM Departments);

-- JOIN: the natural choice once you also want to pull columns back from Departments
SELECT e.name FROM Employees e
JOIN Departments d ON d.dept = e.dept;

On today's five rows, all three hand back the identical five names — every Employees.dept value genuinely has a matching row in Departments right now, so there's nothing here to tell them apart. Don't lean on the received wisdom that one of these is inherently the fast choice, either; today's query planners are perfectly capable of recognizing an IN subquery and treating it exactly like EXISTS internally. If performance is the question, look at what the plan actually says rather than picking a winner from memory.

Where the three genuinely diverge is NOT IN, and it's sharp enough to be worth its own worked example rather than a warning in passing. Slide three's next question is "who in Sales isn't earning what anyone in IT earns" — a sanity check before publishing the pay-ladder slide:

SELECT name FROM Employees
WHERE dept = 'Sales'
  AND salary NOT IN (SELECT salary FROM Employees WHERE dept = 'IT');
-- returns ZERO rows — not "neither Alice nor Bob matches an IT salary," but nothing at all

By hand, the honest answer is obviously "both": neither Alice's $50,000 nor Bob's $60,000 shows up anywhere in IT's salary list. The database disagrees, and Dave is the reason. IT's salary subquery hands back (70000, NULL), and SQL quietly rewrites x NOT IN (70000, NULL) into x <> 70000 AND x <> NULL before evaluating it. That second comparison — anything at all compared against NULL with <> — lands on UNKNOWN no matter what x is, for Alice, for Bob, for anyone. And an AND chain with one UNKNOWN link in it can never resolve to TRUE, which is the same truth-table fact from a few chapters back showing up in a new spot. One stray NULL sitting inside a NOT IN list is enough to quietly empty the whole query, for every row, with nothing in the result set hinting at why.

NOT EXISTS sidesteps the whole mechanism, because it was never built around comparing one value against a list in the first place — it just asks, row by row, "does a match exist," and a NULL on the inner side simply fails to produce a match instead of contaminating a chain of comparisons:

SELECT e.name FROM Employees e
WHERE e.dept = 'Sales'
  AND NOT EXISTS (
    SELECT 1 FROM Employees i WHERE i.dept = 'IT' AND i.salary = e.salary
  );
-- Alice, Bob — the answer this query was supposed to give in the first place

Given that gap, defaulting to NOT EXISTS any time a subquery's column might someday hold a NULL is cheap insurance rather than paranoia — on a column that happens to be NOT NULL, the two forms behave identically anyway, so there's no real cost to picking the one that can never blow up.