ANY and ALL with arrays in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Using ANY with array comparison

Given the table products with a column price and an array {100, 200, 300}, what does the following query return?

SELECT name FROM products WHERE price = ANY (ARRAY[100, 200, 300]);

A All products with price less than 100, 200, or 300

B All products with price exactly 100, 200, or 300

C All products with price greater than 100, 200, or 300

D No products, because the syntax is invalid

💡 Hint

ANY checks if the price matches any element in the array.

The = ANY operator compares the price to each element in the array and returns true if any match is found. So it selects products priced exactly 100, 200, or 300.

❓ query_result

intermediate

Using ALL with array comparison

What does this query return?

SELECT name FROM products WHERE price > ALL (ARRAY[50, 75, 90]);

A Products with price less than 50, 75, and 90

B Products with price greater than 50 or 75 or 90

C Products with price greater than 50, 75, and 90

D Syntax error due to ALL usage

💡 Hint

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

price > ALL means price must be greater than every element in the array, so price must be greater than 90 (the largest).

📝 Syntax

advanced

Identify the syntax error with ANY and arrays

Which option contains a syntax error when using ANY with arrays in PostgreSQL?

A SELECT * FROM orders WHERE quantity = ANY [10, 20, 30];

B SELECT * FROM orders WHERE quantity = ANY (ARRAY[10, 20, 30]);

C ;)]03 ,02 ,01[YARRA( YNA = ytitnauq EREHW sredro MORF * TCELES

D SELECT * FROM orders WHERE quantity = ANY (ARRAY[10,20,30]);

💡 Hint

PostgreSQL requires ARRAY keyword and parentheses for arrays.

Option A uses square brackets without ARRAY keyword and parentheses, which is invalid syntax in PostgreSQL.

🧠 Conceptual

advanced

Understanding ALL with empty arrays

What is the result of this query if the array is empty?

SELECT * FROM products WHERE price > ALL (ARRAY[]::integer[]);

A Returns all rows because condition is true for all elements (none exist)

B Returns no rows because comparison fails

C Raises an error due to empty array

D Returns rows where price is NULL

💡 Hint

Think about how ALL behaves with no elements to compare.

ALL with an empty array always returns true because there is no element to contradict the condition, so all rows match.

❓ optimization

expert

Optimizing queries with ANY and large arrays

You have a large array of IDs and want to find matching rows in users table by id. Which query is most efficient?

A) SELECT * FROM users WHERE id = ANY (ARRAY[...large array...]);
B) SELECT * FROM users WHERE id IN (SELECT unnest(ARRAY[...large array...]));
C) SELECT * FROM users WHERE id = ANY (ARRAY[...large array...]) AND active = true;
D) SELECT * FROM users WHERE id IN (...large list of IDs...);

A Using = ANY with a large array directly

B Using IN with unnest of the array

C Using IN with a large list of IDs directly

D Using = ANY with array and extra filter active = true

💡 Hint

Adding filters can help the planner optimize the query better.

Option D allows the planner to use indexes on active and id together, improving performance over just matching ANY or IN with large arrays.

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