Correlated subqueries, EXISTS and the NOT IN trap

Part of the free LogicWiz SQL & Databases course, Module 6: Subqueries and set operations. The queries and practice run in the app on real Postgres.

Meher's query for "departments with no students" returns nothing. But Mathematics clearly has nobody. One student with a missing department, Manish, broke the whole query. How?

You'll learn to

• Write a correlated subquery that depends on the outer row
• Test for existence with EXISTS and NOT EXISTS
• Explain why NOT IN returns nothing when the list has a NULL
• Choose NOT EXISTS for "never" questions

Learn

A subquery that looks outside

So far the inner query stood alone. A correlated subquery uses a column of the outer row.

"Students above their own department's average": for Aarav (CS) the inner query must average CS; for Riya (Electronics) it must average Electronics. So the inner query says WHERE s2.dept_id = s.dept_id, where s is the outer student.

Picture it running once for each outer row. (Postgres often finds a faster plan, but the result is the same.)

Above their department average

SELECT s.name, s.dept_id, s.cgpa
FROM students s
WHERE s.cgpa > (SELECT AVG(s2.cgpa)
                FROM students s2
                WHERE s2.dept_id = s.dept_id)
ORDER BY s.dept_id, s.name;

Two aliases of the same table: s is the student being checked, s2 is everyone in that student's department. Manish (no department) is compared with an average over nobody, which is NULL, so he drops out.

EXISTS: is there at least one?

EXISTS (subquery) is true when the subquery returns at least one row. It does not care what the row contains, so people write SELECT 1 inside.

NOT EXISTS is true when it returns no rows: the natural way to say "never". "Students who never enrolled in anything" means: no row in enrollments has this student's id.

Postgres can stop at the first match, so EXISTS is usually fast.

Who never enrolled?

SELECT s.student_id, s.name
FROM students s
WHERE NOT EXISTS (SELECT 1
                  FROM enrollments e
                  WHERE e.student_id = s.student_id);

Aditya Rao joined this year and has no enrollment yet. This is the same anti-join as LEFT JOIN … IS NULL from Module 5, written as a question.

The NOT IN trap

-- departments with no students? returns 0 rows!
SELECT name
FROM departments
WHERE dept_id NOT IN (SELECT dept_id FROM students);

-- NOT EXISTS gets it right: Mathematics
SELECT d.name
FROM departments d
WHERE NOT EXISTS (SELECT 1
                  FROM students s
                  WHERE s.dept_id = d.dept_id);

Manish Yadav has dept_id NULL, so the list is (1, 1, 2, …, NULL). The first query finds nothing; the second finds Mathematics.

Why NOT IN breaks: 6 NOT IN (1, 2, NULL) means 6 <> 1 AND 6 <> 2 AND 6 <> NULL. The last part is unknown, and true AND unknown is unknown, so the row is never kept. One NULL in the list empties the whole result. Use NOT EXISTS, or add WHERE dept_id IS NOT NULL inside the subquery.

Which to use

Interviewers love: "Why does my NOT IN query return no rows?" Answer in one line: the subquery returned a NULL, so every x <> NULL test is unknown. Also asked: correlated vs non-correlated (a correlated one refers to the outer query and is evaluated per outer row), and EXISTS vs IN.

In one line

A correlated subquery uses the outer row; use NOT EXISTS for "never", because NOT IN returns nothing if the list has a NULL.