Summary: in this tutorial, you will learn how to use the PostgreSQL ANY operator to compare a scalar value with a set of values returned by a subquery.
Introduction to PostgreSQL ANY operator
The PostgreSQL ANY operator compares a value to a set of values returned by a subquery. The following illustrates the syntax of the ANY operator:
1 | expresion operator ANY(subquery) |
In this syntax:
- The subquery must return exactly one column.
- The
ANYoperator must be preceded by one of the following operator =, <=, >, <, >, <> - The
ANYoperator returns true if any value of the subquery meets the condition, otherwise, it returns false.
Note that SOME is a synonym for ANY, meaning that you can substitute SOME for ANY in any SQL statement.
PostgreSQL ANY examples
We will use the following film and film_categorytables in the sample database for the demonstration.
The following example returns the maximum length of film grouped by film category:
1 2 3 4 5 6 7 8 | SELECT MAX( length ) FROM film INNER JOIN film_category USING(film_id) GROUP BY category_id; |
You can use this query as a subquery in the following statement that finds the films whose lengths are greater than or equal to the maximum length of any film category :
1 2 3 4 5 6 7 | SELECT title FROM film WHERE length (SELECT MAX( length ) FROM film INNER JOIN film_category USING(film_id) GROUP BY category_id ); |
Here is the result:
For each film category, the subquery finds the maximum length. The outer query looks at all these values and determines which film’s lengths are greater than or equal to any film category’s maximum length.
ANY vs. IN
The = ANY is equivalent to IN operator.
The following example gets the film whose category is either Action or Drama.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT title, category_id FROM film INNER JOIN film_category USING(film_id) WHERE category_id = ANY( SELECT category_id FROM category WHERE NAME = 'Action' OR NAME = 'Drama' ); |
The result is:
The following statement uses the IN operator which produces the same result:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | SELECT title, category_id FROM film INNER JOIN film_category USING(film_id) WHERE category_id IN( SELECT category_id FROM category WHERE NAME = 'Action' OR NAME = 'Drama' ); |
Note that the <> ANY operator is different from NOT IN. The following expression:
1 | x <> ANY (a,b,c) |
is equivalent to
1 | x <> a OR <> b OR x <> c |
The following example uses the <> ANY operator to find films that are out of stock (or not in the inventory).
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT title FROM film WHERE film_id <> ANY( SELECT film_id FROM inventory ) ORDER BY title; |
In this tutorial, you have learned how to use the PostgreSQL ANY operator to compare a value to a set of values returned by a subquery.