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.

Practice

1. What does the ANY keyword do when used with an array in PostgreSQL?

easy

A. It checks if a value matches at least one element in the array.

B. It checks if a value matches all elements in the array.

C. It returns all elements of the array.

D. It returns the first element of the array.

Solution

1. Step 1: Understand the ANY keyword

   ANY compares a single value to each element in an array and returns true if it matches any one of them.

2. Step 2: Compare with ALL keyword

   ALL requires the value to match every element, which is different from ANY.

3. Final Answer:

   It checks if a value matches at least one element in the array. -> Option A

4. Quick Check:

   ANY = matches any element [OK]

Hint: ANY means match at least one element in array [OK]

Common Mistakes:

• Confusing ANY with ALL
• Thinking ANY returns all elements
• Assuming ANY returns first element only

2. Which of the following is the correct syntax to check if the number 5 is equal to any element in the array ARRAY[3,5,7] in PostgreSQL?

easy

A. SELECT 5 = ANY ARRAY[3,5,7];

B. SELECT 5 = ANY[3,5,7];

C. SELECT 5 = ANY(ARRAY[3,5,7]);

D. SELECT 5 = ALL ARRAY(3,5,7);

Solution

1. Step 1: Check correct ANY syntax

   The correct syntax uses = ANY(ARRAY[...]) with parentheses around ARRAY.

2. Step 2: Identify wrong options

   SELECT 5 = ANY ARRAY[3,5,7]; misses parentheses around ARRAY, causing syntax error. SELECT 5 = ANY[3,5,7]; uses square brackets incorrectly. SELECT 5 = ALL ARRAY(3,5,7); uses ALL instead of ANY and wrong ARRAY syntax without [].

3. Final Answer:

   SELECT 5 = ANY(ARRAY[3,5,7]); -> Option C

4. Quick Check:

   = ANY(ARRAY[...]) [OK]

Hint: Use = ANY(ARRAY[...]) with parentheses [OK]

Common Mistakes:

• Omitting parentheses around ARRAY
• Using square brackets instead of ARRAY keyword
• Mixing ANY and ALL keywords

3. Given the query:
SELECT 4 > ALL ARRAY[2,3,5];
What is the result?

medium

A. true

B. null

C. false

D. syntax error

Solution

1. Step 1: Understand ALL with comparison

   The query uses 4 > ALL ARRAY[2,3,5] but misses required parentheses around the array.

2. Step 2: Correct syntax and error

   Correct is 4 > ALL (ARRAY[2,3,5]). Without (), it causes syntax error. (Note: if fixed, 4>5 is false so result false, but as given: error.)

3. Final Answer:

   syntax error -> Option D

4. Quick Check:

   Missing () around ARRAY -> syntax error [OK]

Hint: op ALL requires (ARRAY[...]) [OK]

Common Mistakes:

• Assuming query runs and gets false
• Forgetting parentheses around ARRAY
• Confusing with ANY behavior

4. Identify the error in this query:
SELECT * FROM users WHERE age = ALL [20, 30, 40];

medium

A. ALL cannot be used with numbers

B. Using square brackets instead of ARRAY keyword

C. Missing comparison operator

D. No error, query is correct

Solution

1. Step 1: Check array syntax in WHERE clause

   PostgreSQL requires arrays to be written with ARRAY keyword, not square brackets alone.

2. Step 2: Identify correct syntax

   The correct syntax is age = ALL (ARRAY[20, 30, 40]). Using just [20, 30, 40] causes syntax error.

3. Final Answer:

   Using square brackets instead of ARRAY keyword -> Option B

4. Quick Check:

   Arrays need ARRAY keyword, not just brackets [OK]

Hint: Use ARRAY[...] not just brackets for arrays [OK]

Common Mistakes:

• Using [] instead of ARRAY[]
• Thinking ALL can't compare numbers
• Missing operator after WHERE

5. You want to find all products priced less than every price in the array ARRAY[100, 200, 300]. Which query correctly achieves this?

hard

A. SELECT * FROM products WHERE price < ALL (ARRAY[100, 200, 300]);

B. SELECT * FROM products WHERE price < ANY ARRAY[100, 200, 300];

C. SELECT * FROM products WHERE price > ALL ARRAY[100, 200, 300];

D. SELECT * FROM products WHERE price = ALL ARRAY[100, 200, 300];

Solution

1. Step 1: Understand the requirement

   We want products with price less than every price in the array, meaning price must be less than 100 (the minimum).

2. Step 2: Choose correct operator and keyword

   Using price < ALL (ARRAY[100, 200, 300]) checks if price is less than all elements. ANY would only require less than one.

3. Final Answer:

   SELECT * FROM products WHERE price < ALL (ARRAY[100, 200, 300]); -> Option A

4. Quick Check:

   Less than ALL means less than every element [OK]

Hint: Use < ALL (ARRAY[...]) to check less than every element [OK]

Common Mistakes:

• Using ANY instead of ALL for all elements
• Using > instead of < for less than
• Using = ALL which checks equality