Using EXISTS, ANY, and ALL

The uses of the keywords EXISTS, ANY, and ALL are not intuitively obvious to the casual observer. EXISTS takes a subquery as an argument, and returns TRUE if the subquery returns anything and FALSE if the result set is empty. For example

						SELECT NAME, ORDEREDON
						FROM ORDERS
						WHERE EXISTS
						(SELECT *
						FROM ORDERS
						WHERE NAME ='TRUE WHEEL')

NAME         ORDEREDON
========== ===========

TRUE WHEEL 15-MAY-1996
TRUE WHEEL 19-MAY-1996
TRUE WHEEL  2-SEP-1996
TRUE WHEEL 30-JUN-1996
BIKE SPEC  30-JUN-1996
BIKE SPEC  30-MAY-1996
BIKE SPEC  30-MAY-1996
BIKE SPEC  17-JAN-1996
LE SHOPPE  17-JAN-1996
LE SHOPPE   1-JUN-1996
AAA BIKE    1-JUN-1996
AAA BIKE    1-JUL-1996
AAA BIKE    1-JUL-1996
JACKS BIKE 11-JUL-1996

Not what you might expect. The subquery inside ...

Get Sams Teach Yourself SQL in 21 Days, Fourth Edition now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.