ANY and ALL with arrays in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ANY keyword do when used with arrays in PostgreSQL?

ANY checks if a value matches any element in an array. It returns true if the value satisfies the condition for at least one element.

beginner

How does ALL differ from ANY when used with arrays?

ALL checks if a value matches all elements in an array. It returns true only if the value satisfies the condition for every element.

intermediate

Write a simple example query using ANY with an array.

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

This finds products with price greater than at least one of 10, 20, or 30.

beginner

What will this query return?<br>

SELECT 5 = ALL (ARRAY[5, 5, 5]);

It returns true because 5 equals all elements in the array.

intermediate

Can ANY and ALL be used with operators other than =? Give an example.

Yes. For example:<br>

SELECT * FROM orders WHERE quantity < ALL (ARRAY[10, 20, 30]);

This finds orders where quantity is less than every number in the array.

What does price > ANY (ARRAY[100, 200, 300]) mean?

A Price equals any of 100, 200, or 300

B Price is greater than all of 100, 200, and 300

C Price equals all of 100, 200, and 300

D Price is greater than at least one of 100, 200, or 300

ANY means the condition is true if price is greater than at least one element in the array.

What will SELECT 10 = ALL (ARRAY[10, 10, 10]); return?

A false

B null

C true

D error

10 equals every element, so ALL returns true.

Which keyword checks if a value matches every element in an array?

A ALL

B ANY

C SOME

D IN

ALL requires the condition to be true for all elements.

Is this query valid?<br>SELECT * FROM table WHERE col < ALL (ARRAY[5, 10, 15]);

A Yes, it checks if col is less than all array elements

B No, <code>ALL</code> cannot be used with <

C No, arrays cannot be used with <code>ALL</code>

D Yes, but it checks if col is less than any element

ALL works with comparison operators like <, checking the condition against all array elements.

What does value = ANY (array) return if the array is empty?

A error

B false

C null

D true

If the array is empty, ANY returns false because no elements match.

Explain how ANY and ALL work with arrays in PostgreSQL and give a simple example for each.

Think about comparing a value to multiple values stored in an array.

Describe a real-life scenario where using ANY or ALL with arrays in a query would be helpful.

Imagine checking if a price is higher than some or all given thresholds.