A way to answer this question is to go through the Dorm table, one row at a time, and see if
there are, or are not, any students who list that dormitory as their own. Here is such a query:
SELECT Dorm
FROM Dorm
WHERE NOT EXISTS (
SELECT *
FROM Student
WHERE Student.Dorm = Dorm.Dorm
);
The outer query works through the rows of the Dorm table inspecting the Dorm column (Dorm.Dorm).
For each value of Dorm.Dorm, the inner query selects all the students who have that dormitory name in the
Dorm column of the Student table (Student.Dorm).
This query introduces the NOT EXISTS function (and, of course, there is also an EXISTS function).
EXISTS and NOT EXISTS are used with correlated subqueries to test for the presence or absence of
qualifying results in the subquery. In this case, whenever no qualifying student is found (the result NOT
EXISTS), then that row of the Dorm table qualifies. The result is a list of dormitories to which no students
are assigned.
Likewise, one could create a list of all dorms to which students have been assigned by changing the NOT
EXISTS to EXISTS.
A famous and mind-bending use of NOT EXISTS can find rows where all rows in the subquery satisfy
some condition. For instance, suppose one wants to know if there is any club to which all math majors belong.
Pages:
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430