ANY and ALL with arrays in PostgreSQL

Introduction

ANY and ALL help you check if a value matches some or all items in a list. This makes it easy to compare one value against many at once.

You want to find rows where a number matches any number in a list.

You want to check if a value is greater than all values in an array.

You want to filter data where a string equals any string in a set.

You want to ensure a value meets a condition for every item in a list.

You want to compare a column against multiple possible values stored in an array.

Syntax

PostgreSQL

SELECT column_name
FROM table_name
WHERE column_name operator ANY (array_expression);

SELECT column_name
FROM table_name
WHERE column_name operator ALL (array_expression);

operator can be =, <, >, <=, >=, <> etc.

ANY means the condition is true if it matches at least one element in the array.

ALL means the condition must be true for every element in the array.

Examples

Selects products where price is 10, 20, or 30.

PostgreSQL

SELECT * FROM products WHERE price = ANY (ARRAY[10, 20, 30]);

Selects products where price is greater than 5, 7, and 9 (so price > 9).

PostgreSQL

SELECT * FROM products WHERE price > ALL (ARRAY[5, 7, 9]);

Edge case: empty array means no rows match because ANY with empty array is false.

PostgreSQL

SELECT * FROM users WHERE username = ANY (ARRAY[]::text[]);

Edge case: array with one element means condition compares to that single value.

PostgreSQL

SELECT * FROM orders WHERE quantity <= ALL (ARRAY[100]);

Sample Program

This creates a products table and adds four fruits with prices. Then it finds products priced at 10 or 30 using ANY. Next, it finds products priced greater than both 15 and 25 using ALL.

PostgreSQL

CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT, price INT);

INSERT INTO products (name, price) VALUES
('Apple', 10),
('Banana', 20),
('Cherry', 30),
('Date', 40);

-- Find products with price equal to any in the list
SELECT name, price FROM products WHERE price = ANY (ARRAY[10, 30]);

-- Find products with price greater than all in the list
SELECT name, price FROM products WHERE price > ALL (ARRAY[15, 25]);

Important Notes

ANY returns true if the condition matches at least one element in the array.

ALL returns true only if the condition matches every element in the array.

Using an empty array with ANY always returns false; with ALL it returns true.

Time complexity depends on array size because it checks each element.

Use ANY when you want to match any value; use ALL when you want to ensure all values meet the condition.

Summary

ANY checks if a value matches any element in an array.

ALL checks if a value matches all elements in an array.

These help compare one value against many easily in queries.