ALL, ANY, SOME with subqueries in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the ALL keyword do when used with a subquery in SQL?

ALL compares a value to every value returned by the subquery. The condition is true only if the comparison holds for all values.

beginner

How does ANY differ from ALL in SQL subqueries?

ANY checks if the condition is true for at least one value returned by the subquery. It returns true if any single value satisfies the condition.

beginner

What is the synonym of ANY in SQL when used with subqueries?

SOME is a synonym for ANY. Both work the same way and can be used interchangeably.

intermediate

Write a simple example of using ALL with a subquery to find employees with salary greater than all salaries in department 10.

SELECT employee_name FROM employees WHERE salary > ALL (SELECT salary FROM employees WHERE department_id = 10);

intermediate

Explain why salary > ANY (subquery) might return more rows than salary > ALL (subquery).

salary > ANY (subquery) returns true if salary is greater than at least one value in the subquery, so it matches more rows. salary > ALL (subquery) requires salary to be greater than every value, which is stricter and returns fewer rows.

Which keyword checks if a value is greater than every value returned by a subquery?

A ALL

B ANY

C SOME

D IN

ALL requires the condition to be true for every value returned by the subquery.

What does SOME mean in SQL when used with subqueries?

A Equivalent to EXISTS

B Equivalent to ALL

C Equivalent to IN

D Equivalent to ANY

SOME is a synonym for ANY, meaning the condition must be true for at least one value.

If you want to find rows where a value is less than at least one value in a subquery, which keyword do you use?

A ALL

B ANY

C NONE

D EXISTS

ANY checks if the condition is true for any single value returned by the subquery.

What will the query SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Books'); return?

A Products with price greater than every book price

B Products with price greater than some book price

C Products with price equal to any book price

D All products in the 'Books' category

The query returns products priced higher than all books, meaning strictly greater than every book price.

Which of these is NOT true about ANY and ALL?

A <code>ANY</code> returns true if condition matches at least one value

B <code>ALL</code> returns true if condition matches every value

C <code>ANY</code> and <code>ALL</code> are interchangeable

D <code>SOME</code> is a synonym for <code>ANY</code>

ANY and ALL have different meanings and are not interchangeable.

Explain how the ALL keyword works with subqueries in SQL and give a simple example.

Think about how a value compares to every value in a list.

Describe the difference between ANY and ALL when used with subqueries and when you might use each.

Consider how strict the condition is for each keyword.