ANY and ALL with arrays in PostgreSQL - Step-by-Step Execution

Concept Flow - ANY and ALL with arrays

Start Query

↓

Evaluate Condition with ANY

↓

Check if value matches ANY element in array

Yes/No↓

Evaluate Condition with ALL

↓

Check if value matches ALL elements in array

Yes/No↓

Return rows matching condition

↓

End

The query checks if a value matches any or all elements in an array, then returns rows accordingly.

Execution Sample

PostgreSQL

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

This query returns products with price greater than any value in the array [10, 20, 30].

Execution Table

Step	Row ID	price	Condition	Evaluation	Result
1	1	5	5 > ANY([10,20,30])	5 > 10? No, 5 > 20? No, 5 > 30? No	False
2	2	15	15 > ANY([10,20,30])	15 > 10? Yes, 15 > 20? No, 15 > 30? No	True
3	3	25	25 > ANY([10,20,30])	25 > 10? Yes, 25 > 20? Yes, 25 > 30? No	True
4	4	35	35 > ANY([10,20,30])	35 > 10? Yes, 35 > 20? Yes, 35 > 30? Yes	True
5	1	5	5 > ALL([10,20,30])	5 > 10? No	False
6	2	15	15 > ALL([10,20,30])	15 > 10? Yes, 15 > 20? No	False
7	3	25	25 > ALL([10,20,30])	25 > 10? Yes, 25 > 20? Yes, 25 > 30? No	False
8	4	35	35 > ALL([10,20,30])	35 > 10? Yes, 35 > 20? Yes, 35 > 30? Yes	True

💡 All rows evaluated for ANY and ALL conditions; results determined by comparisons.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	Final
price	N/A	5	15	25	35	35
ANY condition result	N/A	False	True	True	True	True
ALL condition result	N/A	False	False	False	True	True

Key Moments - 3 Insights

Why does 'price > ANY(array)' return true if price is greater than just one element?

Because ANY means the condition is true if it matches at least one element in the array, as shown in execution_table rows 2-4.

Why does 'price > ALL(array)' require price to be greater than every element?

ALL means the condition is true only if price is greater than every element in the array, as shown in execution_table row 8 where all comparisons are true.

What happens if the array is empty?

ANY with an empty array always returns false, ALL with an empty array always returns true, because there are no elements to compare.

Visual Quiz - 3 Questions

Look at the execution_table, what is the result of 'price > ANY(array)' for price = 5?

A False

B True

C Error

D Depends on array

💡 Hint

Check row 1 in execution_table where price=5 and condition is evaluated.

At which row does 'price > ALL(array)' become true?

A Row 5

B Row 6

C Row 8

D Row 7

💡 Hint

Look at the last rows in execution_table for ALL condition results.

If the array was [40, 50, 60], what would be the result of 'price > ANY(array)' for price=35?

A True

B False

C Error

D Cannot determine

💡 Hint

Compare price=35 with each element in the new array; refer to how ANY works in execution_table.

Concept Snapshot

ANY and ALL with arrays in SQL:
- ANY checks if condition is true for at least one array element.
- ALL checks if condition is true for every array element.
- Syntax: value operator ANY(array) or value operator ALL(array).
- Useful for comparing a value against multiple values in one condition.
- Returns boolean to filter rows in WHERE clause.

Full Transcript

This visual execution trace shows how PostgreSQL evaluates ANY and ALL conditions with arrays. For ANY, the condition is true if the value matches at least one element in the array. For ALL, the condition is true only if the value matches every element. The execution table walks through each row's price compared to the array elements, showing step-by-step comparisons and final results. The variable tracker shows how the price and condition results change per row. Key moments clarify common confusions about ANY and ALL behavior. The quiz tests understanding by referencing specific steps in the execution table. This helps beginners see exactly how these array comparisons work in SQL 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