PostgreSQL - ANY Operator

PostgreSQL ANY Operator

The ANY operator in PostgreSQL is a powerful tool for comparing a scalar value to a set of values, which can be provided by an array or a subquery. It simplifies queries that would otherwise require multiple OR conditions.

The ANY operator returns TRUE if the comparison is true for at least one of the values in the set. It returns FALSE if the comparison is false for all values or if the set is empty.

Syntax

The ANY operator is used after a standard comparison operator (=, <>, <, >, <=, >=):

scalar_expression operator ANY (array_expression)
-- or
scalar_expression operator ANY (subquery)

The = ANY(...) construct is equivalent to the IN operator, but ANY can be used with any comparison operator, making it more versatile.

Example of ANY Operator

Let’s create a table to manage job candidates and their skills and test scores. This is a perfect use case for array data types and the ANY operator.

CREATE TABLE candidates(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    skills TEXT[],
    scores INT[]
);

INSERT INTO candidates(name, skills, scores) VALUES
('Alice', ARRAY['Python', 'SQL', 'GCP'], ARRAY[85, 90, 75]),
('Bob', ARRAY['Java', 'PostgreSQL'], ARRAY[70, 80, 95]),
('Charlie', ARRAY['Go', 'SQL', 'Docker'], ARRAY[65, 75, 85]),
('Diana', ARRAY['C++', 'Python'], ARRAY[80, 85, 90]);

Use Case 1: Finding a Candidate with a Specific Skill

We want to find all candidates who list ‘SQL’ as one of their skills. Using ANY is much cleaner than trying to match against a text representation of the array.

SELECT name, skills
FROM candidates
WHERE 'SQL' = ANY(skills);

This query produces the following output:

name	skills
Alice	{Python,SQL,GCP}
Charlie	{Go,SQL,Docker}

Use Case 2: Checking for a Minimum Score

Now let’s find candidates who scored 90 or higher in at least one of their tests. Here we use ANY with the >= operator.

SELECT name, scores
FROM candidates
WHERE 90 <= ANY(scores);

This query returns candidates who have at least one score that is 90 or greater.

name	scores
Alice	{85,90,75}
Bob	{70,80,95}
Diana	{80,85,90}

Using ANY with a Subquery

The ANY operator is also frequently used with subqueries. For example, let’s find all candidates who have a score higher than any score achieved by Charlie.

SELECT name, scores
FROM candidates
WHERE id <> 3 AND scores > ANY (
    SELECT scores FROM candidates WHERE name = 'Charlie'
);

This query finds candidates (excluding Charlie) who have at least one score greater than 65, 75, or 85. Essentially, anyone with a score greater than 65.