PostgreSQL ANY Operator (original) (raw)

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.

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