Which students have a course in progress (a NULL grade)?
sql
SELECT s.name
FROM student s
WHERE EXISTS (
SELECT 1 FROM takes t
WHERE t.id = s.id AND t.grade IS NULL
);EXISTS (subquery)is TRUE when the subquery returns at least one row, FALSE when it returns none. It is never UNKNOWN.- The subquery's columns do not matter.
SELECT 1is the usual way to write it.
A correlated subquery
The subquery uses s.id, a column of the outer row. It is a correlated subquery. The database evaluates it once for each student:
s.id = 'S205'outer row
run the subquery
takes where id = 'S205' and grade IS NULL0 rows
EXISTS
FALSE: drop Eli Brandt
The scalar and IN subqueries of the last lessons did not use the outer row. They return the same answer for every row.
NOT EXISTS
NOT EXISTS keeps the rows for which the subquery returns nothing. It counts rows and never compares with a NULL in a list, so the NULL trap of NOT IN does not happen:
sql
SELECT i.name
FROM instructor i
WHERE NOT EXISTS (SELECT 1 FROM teaches t WHERE t.id = i.id);The result is the instructors who teach nothing.