0

Subqueries, EXISTS & IN

SQLused in ~75% of apps

"Posts liked more than average" and "authors who have never commented" both need a query answered before the query you're actually writing can run. That inner query is a subquery, and it comes in three shapes. Pick the wrong one — specifically, reach for NOT IN instead of NOT EXISTS the moment real data can contain a NULL — and your query doesn't crash. It quietly returns nothing, forever, and the bug ships.

Core idea. A subquery is a SELECT nested inside another query, and what it's compared against decides its shape: a scalar subquery stands in for one value (`= (select avg(...))`), an IN subquery stands in for a list to test membership against, and EXISTS/NOT EXISTS asks a correlated yes-or-no question — does at least one matching row exist? — and never inspects what columns that inner query selects. NOT IN and NOT EXISTS look interchangeable until the list on the right can contain a NULL, at which point NOT IN silently breaks and NOT EXISTS does not.
Playground, this isn't a screenshot
Live demointeractive
Real Postgres · in your browseridle
dave has never left a comment, so you'd expect him back. Run it. Then change not in to not exists — using where not exists (select 1 from comments c where c.user_id = pr.id) — and run again.
Cmd/Ctrl + Enter
↑ play a little first, then dig in