PostgreSQL ANY and ALL Operators

The ANY() and ALL() operators in PostgreSQL compare a value against elements in an array or subquery. ANY() returns true if the comparison is true for at least one element, while ALL() requires the comparison to be true for every element.

Syntax

-- ANY: true if comparison matches at least one element
value operator ANY(array)
value operator ANY(subquery)

-- ALL: true if comparison matches all elements
value operator ALL(array)
value operator ALL(subquery)

-- Common operators: =, !=, <>, <, >, <=, >=

Official Documentation

Example usage

-- Check if value equals any array element
SELECT * FROM products
WHERE category = ANY(ARRAY['electronics', 'computers', 'phones']);

-- Equivalent to IN
SELECT * FROM products
WHERE category IN ('electronics', 'computers', 'phones');

-- Check if value is greater than any element
SELECT * FROM products
WHERE price > ANY(ARRAY[100, 200, 300]);
-- True if price > 100 OR price > 200 OR price > 300
-- Effectively: price > 100 (minimum value)

-- Check if value is less than all elements
SELECT * FROM products
WHERE price < ALL(ARRAY[100, 200, 300]);
-- True if price < 100 AND price < 200 AND price < 300
-- Effectively: price < 100 (minimum value)

-- Use with table arrays
SELECT * FROM users
WHERE 'premium' = ANY(tags);

-- Check if any element matches condition
SELECT * FROM orders
WHERE order_date > ANY(
    SELECT shipment_date FROM shipments WHERE status = 'delayed'
);

-- ALL with subquery
SELECT * FROM products
WHERE price > ALL(
    SELECT price FROM products WHERE category = 'budget'
);
-- Finds products more expensive than ALL budget products

-- NOT EQUAL to any (at least one different)
SELECT * FROM products
WHERE status != ANY(ARRAY['active', 'pending']);
-- True if status is not 'active' OR not 'pending'

-- String comparison
SELECT * FROM users
WHERE email LIKE ANY(ARRAY['%@gmail.com', '%@yahoo.com', '%@hotmail.com']);

-- Array column contains value
SELECT * FROM products
WHERE 'sale' = ANY(tags);

-- Check if all elements meet condition
SELECT * FROM orders
WHERE total_amount > ALL(ARRAY[50, 100, 150]);
-- Order must be more than $150

-- Combine ANY with array operators
SELECT * FROM products
WHERE tags && ARRAY['featured', 'new'];  -- Overlap operator (alternative)

-- ANY with ARRAY constructor from subquery
SELECT * FROM employees
WHERE department_id = ANY(
    ARRAY(SELECT id FROM departments WHERE region = 'West')
);

-- Multiple ANY conditions
SELECT * FROM products
WHERE category = ANY(ARRAY['electronics', 'appliances'])
  AND price < ANY(ARRAY[500, 1000, 2000]);

-- ALL for range checking
SELECT * FROM measurements
WHERE value > ALL(ARRAY[0, -10, -20])
  AND value < ALL(ARRAY[100, 200, 300]);

-- Use with UPDATE
UPDATE products
SET on_sale = true
WHERE price > ANY(
    SELECT AVG(price) * 1.5 FROM products GROUP BY category
);

Common issues

1. ANY vs IN: = ANY() is equivalent to IN(), but ANY is more flexible with other operators.
2. NULL handling: If array contains NULL, results may be unexpected. NULL comparisons always return NULL.
3. Empty arrays: value = ANY(ARRAY[]::INT[]) always returns false. value = ALL(ARRAY[]::INT[]) always returns true.
4. Confusion with ALL: != ALL() means "different from all", while != ANY() means "different from at least one".

Best practices

1. Use = ANY() instead of IN() when working with array columns.
2. Use ALL for "greater/less than all elements" comparisons.
3. For simple membership testing in arrays, consider the @> containment operator with GIN indexes.
4. Be explicit about NULL handling if arrays might contain NULLs.

Frequently Asked Questions

Q: What's the difference between = ANY() and IN()?
A: They're equivalent for equality: WHERE id = ANY(ARRAY[1,2,3]) is the same as WHERE id IN (1,2,3). However, ANY works with other operators like >, <, !=, while IN only does equality.

Q: How does ANY differ from ALL?
A: ANY returns true if the condition is true for at least one element. ALL returns true only if the condition is true for every element. Think "any one" vs "all of them".

Q: What does != ANY() mean?
A: value != ANY(array) returns true if value is different from at least one array element. For "different from all elements", use != ALL() or NOT (value = ANY()).

Q: Can I use ANY with array columns?
A: Yes: WHERE 'value' = ANY(array_column) checks if 'value' exists in the array. Alternatively, use WHERE array_column @> ARRAY['value'] with a GIN index for better performance.

Q: What happens with empty arrays?
A: ANY(empty_array) always returns false. ALL(empty_array) always returns true (vacuous truth - all zero elements satisfy the condition).