0
Challenge · 25SQL III: Relationships & Joins
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.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
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