Lesson 4 of 12 · 22 min
NOT EXISTS and the NOT IN + NULL trap
By the end of this lesson you can write anti-joins with NOT EXISTS, explain with three-valued logic why NOT IN fails when the subquery returns a NULL, and choose a safe pattern.
EXISTS asks is there at least one related row? Its opposite, NOT EXISTS, asks is there no related row at all? A query that keeps rows without a match is called an anti-join, and it is one of the most useful patterns in reporting.
It works exactly like EXISTS: correlate the subquery to the outer row, and PostgreSQL keeps the outer row only when the subquery finds nothing.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons