PostgreSQL - ANY Operator

The ANY operator in PostgreSQL is a powerful tool for comparing a scalar value against a set of values returned by a subquery. From this article, we can better understand the ANY Operator in PostgreSQL.

Syntax

expression operator ANY(subquery)

Rules of ANY Operator

The below rules must be followed while using PostgreSQL ANY operator:

• The subquery must return exactly one column.
• The ANY operator must be preceded by one of the following comparison operators =, <=, >, <, > and <>.
• The ANY operator returns true if any value of the subquery meets the condition, otherwise, it returns false.

PostgreSQL ANY Operator Examples

For the sake of this article, we will be using the sample DVD rental database, which is explained here and can be downloaded by clicking on this link in our examples. Now let us take a look at some of the examples of ANY Operator in PostgreSQL.

Example 1: Finding Films with Maximum Length by Category

Here we will query for the maximum length of film grouped by film category from the "film" table of our sample database.

Query:

SELECT title
FROM film
WHERE length >= ANY(
    SELECT MAX( length )
    FROM film
    INNER JOIN film_category USING(film_id)
    GROUP BY  category_id );

Output: This query will return titles of films that have a length equal to or longer than the maximum length of films in any category.

Explanation: The subquery calculates the maximum length of films for each category by joining the 'film' and 'film_category' tables and grouping by 'category_id'. The outer query selects film titles where the film length is greater than or equal to any of these maximum lengths.

Example 2: Querying Films by Specific Categories

Here we will query for the films whose category is either 'Action'(category_id = 1) or 'Drama'(category_id = 7) from the "category" table of our sample database.

Query:

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'
    );

Output: This query will return titles and category IDs for films that are categorized as either "Action" or "Drama".

Explanation: The subquery selects 'category_id' for categories named "Action" or "Drama". The outer query joins the film and 'film_category' tables and retrieves films that belong to any of these categories.

Important Points About PostgreSQL ANY Operator

• The subquery used with ANY must return exactly one column. It can return multiple rows, but only one column is allowed.
• The ANY operator evaluates to true if the condition specified by the comparison operator holds true for at least one value in the set returned by the subquery. If no value satisfies the condition, it returns false.
• ANY is similar to IN but offers more flexibility, especially with comparison operators. Use IN for simpler cases where you are checking for membership in a list of values.
• The ANY operator must be used with one of the following comparison operators: =, <=, >, <, >=, or <>.