EXISTS vs IN vs JOIN for "Is There a Match": Duplicates and NULLs

EXISTS vs IN vs JOIN for "is there a match" in one line: all three can answer "keep the turbines that have a fault on record", but only EXISTS and IN are filters; a JOIN is a combination, so it repeats a row once per matching fault, and you end up adding DISTINCT to undo it. Between the two filters, the trap is negation: NOT IN against a list that contains a NULL returns no rows at all, while NOT EXISTS returns exactly the rows you meant. Modern planners often turn IN and EXISTS into the same semi-join plan, so pick by correctness first. Everything below is run live in SQLite.

Form	Right side has duplicates	Right side has a NULL
JOIN	the row is repeated once per match	the NULL never matches; harmless
IN / NOT IN	one row, never repeated	NOT IN returns nothing
EXISTS / NOT EXISTS	one row, never repeated	correct either way

The problem it solves

"Which turbines have at least one fault?" and "which turbines have no fault?" are among the most common questions in any schema with a parent table and a child table: customers with orders, users without a login, products never sold. All three forms are legal SQL for the first question, and two of them are legal for the second. They do not return the same rows. The choice is not style; it decides whether your count is right.

Scalar subqueries, and why a correlated subquery re-runs per row, are covered in SQL subqueries explained. Here the subquery is only a yes-or-no test.

The intuition

Think about what each form asks the engine to produce.

• JOIN builds pairs. Every turbine row is paired with every fault row whose turbine matches. A turbine with two faults appears twice. That is the right answer to "list the faults with their turbine" and the wrong answer to "list the turbines with a fault". DISTINCT repairs the output, at the cost of a sort or hash over it.
• IN compares a value with a set. name IN (SELECT turbine FROM fault) is true when name equals some value in the set. The row is kept once, however many faults it has. The set can also be a literal list: name IN ('T1', 'T3').
• EXISTS asks whether a query returns anything. EXISTS (SELECT 1 FROM fault f WHERE f.turbine = t.name) is true if at least one fault row matches this turbine. What the inner query selects is irrelevant, which is why people write SELECT 1. The engine can stop at the first matching row.

Then the NULL trap, which is about three-valued logic, not about subqueries. x NOT IN (a, b, NULL) is x <> a AND x <> b AND x <> NULL. The last comparison is NULL, not true, so the whole expression is NULL, and WHERE keeps only rows where the condition is true. One unlabelled fault, with no turbine name, makes NOT IN return no turbines at all. NOT EXISTS never compares with the NULL: the inner query for turbine T1 simply finds no row whose turbine equals 'T1', and the test is true.

On performance, the PostgreSQL manual describes IN and EXISTS subqueries as forms the planner can turn into a semi-join, and it notes that EXISTS only needs to know whether a row exists, so the subquery may stop early, as of October 2026. In practice the two usually produce the same plan. NOT IN is the one the planner cannot rewrite as freely, because of the NULL semantics above.

Watch it run

The animation opens with the question: which turbines underperformed? "Below average" is not a number you can type, because it depends on what the other machines did, so the bracketed query runs first. It reads all four rows and returns exactly one value, 300.0. With the number in hand, the outer query walks the rows and keeps T2 and T4. The last frame names the contrast: a correlated subquery names an outer column, so it re-runs for every single row. EXISTS is that correlated form, used as a yes-or-no test; IN with a subquery is the uncorrelated form, a set computed once.

The code

The lesson's turbine table and a fault table. T2 has two faults, T4 has one, and one fault was logged without a turbine:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE turbine (name TEXT, mwh REAL);
  INSERT INTO turbine VALUES ('T1', 410.0), ('T2', 180.0),
                             ('T3', 395.0), ('T4', 215.0);
  CREATE TABLE fault (turbine TEXT, code TEXT);
  INSERT INTO fault VALUES ('T2', 'gearbox'), ('T2', 'brake'),
                           ('T4', 'sensor'), (NULL, 'unlabelled');
""")

print(db.execute("""
  SELECT t.name, t.mwh FROM turbine t
  JOIN fault f ON f.turbine = t.name ORDER BY t.name""").fetchall())
# [('T2', 180.0), ('T2', 180.0), ('T4', 215.0)]

print(db.execute("""
  SELECT name, mwh FROM turbine
  WHERE name IN (SELECT turbine FROM fault) ORDER BY name""").fetchall())
# [('T2', 180.0), ('T4', 215.0)]

print(db.execute("""
  SELECT name, mwh FROM turbine t
  WHERE EXISTS (SELECT 1 FROM fault f WHERE f.turbine = t.name)
  ORDER BY name""").fetchall())
# [('T2', 180.0), ('T4', 215.0)]

print(db.execute("""
  SELECT DISTINCT t.name, t.mwh FROM turbine t
  JOIN fault f ON f.turbine = t.name ORDER BY t.name""").fetchall())
# [('T2', 180.0), ('T4', 215.0)]

The JOIN returned T2 twice, once per fault. IN and EXISTS returned each turbine once. DISTINCT repaired the join, but it repaired the output, not the question.

Now the negative question, "which turbines have no fault?", where the unlabelled fault bites:

print(db.execute("""
  SELECT name FROM turbine
  WHERE name NOT IN (SELECT turbine FROM fault)""").fetchall())
# []

print(db.execute("""
  SELECT name FROM turbine t
  WHERE NOT EXISTS (SELECT 1 FROM fault f WHERE f.turbine = t.name)
  ORDER BY name""").fetchall())
# [('T1',), ('T3',)]

print(db.execute("""
  SELECT name FROM turbine
  WHERE name NOT IN (SELECT turbine FROM fault WHERE turbine IS NOT NULL)
  ORDER BY name""").fetchall())
# [('T1',), ('T3',)]

print(db.execute("""
  SELECT name FROM turbine t
  LEFT JOIN fault f ON f.turbine = t.name
  WHERE f.turbine IS NULL ORDER BY name""").fetchall())
# [('T1',), ('T3',)]

print(db.execute("SELECT 'T1' NOT IN ('T2', 'T4'), 'T1' NOT IN ('T2', NULL)").fetchall())
# [(1, None)]

NOT IN returned nothing: not an error, not a warning, an empty result that looks like "every turbine has a fault". NOT EXISTS returned T1 and T3. Filtering the NULL out of the list rescues NOT IN, and the LEFT JOIN ... IS NULL anti-join gets the same answer by a third route. The last line is the whole mechanism in one row: 'T1' NOT IN ('T2', NULL) is NULL, and WHERE drops NULL.

Finally, what the planner does with each form. With an index on fault(turbine):

db.execute("CREATE INDEX fault_turbine ON fault(turbine)")

def plan(sql):
    for row in db.execute("EXPLAIN QUERY PLAN " + sql):
        print("  ", row[3])

print("IN:");     plan("SELECT name FROM turbine WHERE name IN (SELECT turbine FROM fault)")
print("EXISTS:"); plan("SELECT name FROM turbine t WHERE EXISTS (SELECT 1 FROM fault f WHERE f.turbine = t.name)")
print("JOIN:");   plan("SELECT DISTINCT t.name FROM turbine t JOIN fault f ON f.turbine = t.name")
# IN:
#    SCAN turbine
#    USING INDEX fault_turbine FOR IN-OPERATOR
# EXISTS:
#    SCAN t
#    CORRELATED SCALAR SUBQUERY 1
#    SEARCH f USING COVERING INDEX fault_turbine (turbine=?)
# JOIN:
#    SCAN t
#    SEARCH f USING COVERING INDEX fault_turbine (turbine=?)
#    USE TEMP B-TREE FOR DISTINCT

All three scan the four turbines and probe the index for faults. IN uses the index as a set; EXISTS probes it once per turbine and stops at the first hit; the JOIN does the same probe and then pays for a temporary B-tree to deduplicate what it produced.

The complexity

With an index on the child table's matching column, all three forms are O(parents × log children): one probe per parent row. The differences are constant factors and the extra work on the output:

• JOIN + DISTINCT produces one output row per match and then sorts or hashes them away, so its intermediate result is as large as the child table's matches.
• EXISTS stops at the first matching child row, so a parent with a thousand children costs one probe.
• IN with a subquery builds or probes the set once; with a literal list it is a sequence of comparisons.

Without an index, every form degrades to scanning the child table, and the planner's rewrites decide how many times.

Where it goes wrong

• NOT IN on a nullable column. The result is empty the day the first NULL arrives, and nothing tells you. Use NOT EXISTS, or add WHERE col IS NOT NULL inside the subquery.
• A JOIN where a filter was meant. Counts and sums double for parents with two children. If the child columns are not in the SELECT, you wanted EXISTS.
• DISTINCT as a reflex. It hides the duplication instead of removing its cause, and it costs a sort over the whole output.
• SELECT * inside EXISTS. Harmless, since the engine ignores the columns, but it reads as if the columns mattered. SELECT 1 says what you mean.
• Assuming IN is always a subquery. IN ('T1', 'T3') is a literal list; it has no NULL problem unless you write a NULL into it.

When it shows up in interviews

Usually as "find customers who have never placed an order", and the interviewer is listening for NOT EXISTS, or for the NULL caveat if you reach for NOT IN. The row-count version is "why does this report count each customer twice?", and the answer is the JOIN. Join types themselves are in SQL joins explained visually, and NULL in comparisons more generally is in SQL NULL in WHERE.

How to say it in an interview

"If I only need to know whether a match exists, I write EXISTS: it keeps each parent row once, stops at the first matching child, and works correctly under negation. IN with a subquery is equivalent for the positive case, and planners usually turn both into a semi-join, but NOT IN returns nothing if the subquery can produce a NULL, so I avoid it on nullable columns. A JOIN is for when I need the child's columns; as a filter it multiplies rows and forces a DISTINCT."

The inner-query-first mechanics are in the Subqueries lesson.

Frequently asked questions

What is the difference between EXISTS and IN in SQL?

IN compares a value with a set of values; EXISTS tests whether a correlated subquery returns any row. For a positive match they return the same rows, and planners usually run them the same way. They differ under negation: NOT IN returns nothing if the set contains a NULL, NOT EXISTS does not have that problem.

Why does NOT IN return no rows when the subquery has a NULL?

x NOT IN (a, NULL) expands to x <> a AND x <> NULL. A comparison with NULL is NULL, so the whole condition is NULL rather than true, and WHERE only keeps rows whose condition is true. Filter the NULLs out inside the subquery or use NOT EXISTS.

Is EXISTS faster than JOIN?

For "is there a match", usually yes or equal: EXISTS stops at the first matching row and never produces duplicates, while a JOIN outputs one row per match and then needs DISTINCT to remove them. When you need the matched table's columns, the JOIN is the right tool and the comparison does not apply.

Sources

• Subquery Expressions — PostgreSQL Documentation
• Subqueries with EXISTS or NOT EXISTS — MySQL 8.4 Reference Manual
• EXPLAIN QUERY PLAN — SQLite Documentation
• NULL Handling in SQLite — SQLite Documentation