Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/postgresql/postgresql-any-operator/ below:

PostgreSQL - ANY Operator - GeeksforGeeks

Last Updated : 12 Jul, 2025

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.

expression operator ANY(subquery)

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.

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.

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.

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.

• 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 <>.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4