exists This chapter covers the WHERE clause’s EXISTS, ANY/SOME, and ALL predicates. The examples in this chapter use these tables:

Films

id	title	year	duration	genre_id
1	Die Hard	1988	132	7
2	Casablanca	1942	102	5
3	The Conversation	1974	113	1
4	1984	1956	90	4
5	The Godfather	1972	175	2
6	Tinker, Tailor, Soldier, Spy	2011	127	6
7	The Birdcage	1996	118	3

Genres

id	name
1	thriller
2	crime
3	comedy
4	scifi
5	drama
6	espionage
7	action

Directors

id	name
1	Mike Nichols
2	Michael Anderson
3	John McTiernan
4	Tomas Alfredson
5	Francis Ford Coppola
6	Michael Curtiz

EXISTS

exists The EXISTS predicate takes this form:

exists exists exists The WHERE EXISTS clause evaluates to true unless the resultset of subquery is empty. (WHERE NOT EXISTS reverses this.) As with IN, EXISTS allows a subquery to return multiple rows, but also allows it to return multiple columns, since it evaluates whether or not the subquery returns any rows rather than comparing an input and output value as IN does.

exists This query uses EXISTS to find all the directors who have directed a sci-fi film:

It would be more common to use SELECT id than SELECT *, but SELECT * demonstrates that multiple columns can be returned.

exists exists Syntactically, the essential difference between IN and EXISTS is that EXISTS moves the specification of the input expression being evaluated into the subquery (directors.id in the case of these examples).

exists You can do anything with a JOIN clause that you can do with an EXISTS predicate. For example, this is equivalent to the previous query:

exists exists Again, it’s worth benchmarking the different ways (IN/NOT IN, joins and EXISTS/NOT EXISTS) to get a resultset.

ANY, SOME and ALL

The ANY, SOME and ALL predicates take this form:

Where:

1. expression is analogous to expression in the IN predicate.
2. operator is a valid comparison operator: =, <, >, <=, >=, <>/!=.
3. query is the resultset returned by a valid query, with the restriction that it must have exactly one column.

The WHERE ANY clause evaluates to true if the result of (expression operator resultset_value) is true for any of the values in the resultset of query.

SOME is an alias for ANY; they are equivalent.

The WHERE ALL clause evaluates to true if the result of (expression operator resultset_value) is true for all of the values in the resultset of query.

Replacing ANY, SOME and ALL with IN, EXISTS or a Scalar Query

The ANY, SOME and ALL predicates are rarely used, perhaps because they don’t need to be. Any statement using one of these can also be written using either IN, EXISTS or a scalar query.

Here are some logical equivalencies:

Expression	Equivalent	Explanation
= ANY	IN	A value that is equal to some member of a set is also in that set.
< ANY	< max	A value that is less than any one member of a set is also less than the maximum value in the set.
> ANY	> min	A value that is greater than any one member of a set is also greater than the minimum value in the set.
<> ALL	NOT IN	If all members of a set are not equal to a value, the value is not in the set.
< ALL	< min	A value that is less than all members of a set is also less than the minimum value in the set.
> ALL	> max	A value that is greater than all members of a set is also greater than the maximum value in the set.

In any of the above equivalencies, <= can be substituted for <, or >= can be substituted for >, and they will still apply.

Here’s one example. Suppose we have two tables with these structures:

Now (for some contrived reason), I would like a list of all customers whose email is longer than the longest event name that has an s in it. This query will do the job:

However, so will this one:

In practice, max is much more common than ALL.