SQL Code

SQL, Chapter 6: the WHERE Clause’s EXISTS, ANY, SOME and ALL Predicates

posted in: Software Engineering, SQL | 0

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:

WHERE [ NOT ] EXISTS (subquery)

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 expression operator { ANY | SOME | ALL } (query)

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:

Customers
Column Type
id integer
first_name text
last_name text
phone character varying(10)
email text
Events
Column Type
id integer
name text
starts_at timestamp without time zone
base_price numeric(4,2)

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.