ALL, ANY, SOME with subqueries in PostgreSQL - Step-by-Step Execution

Concept Flow - ALL, ANY, SOME with subqueries

Start Query

↓

Execute Subquery

↓

Get List of Values

↓

Compare Outer Value with List

↓

ALL: Check if condition true for every value

↓

ANY/SOME: Check if condition true for at least one value

↓

Return Rows Matching Condition

↓

End Query

The query runs a subquery to get a list of values, then compares each outer row's value against all or any of those values, returning rows that meet the condition.

Execution Sample

PostgreSQL

SELECT * FROM products WHERE price > ALL (SELECT price FROM products WHERE category = 'Books');

Select products whose price is greater than every price of products in the 'Books' category.

Execution Table

Step	Action	Subquery Result	Comparison	Rows Returned
1	Run subquery: SELECT price FROM products WHERE category = 'Books'	[10, 15, 20]	N/A	N/A
2	Check product with price=25: 25 > ALL [10,15,20]?	N/A	True (25 > all prices)	Include product
3	Check product with price=18: 18 > ALL [10,15,20]?	N/A	False (18 not > 20)	Exclude product
4	Check product with price=15: 15 > ALL [10,15,20]?	N/A	False (15 not > 20)	Exclude product
5	Check product with price=22: 22 > ALL [10,15,20]?	N/A	True (22 > all prices)	Include product
6	Query ends	N/A	N/A	Rows with price 25 and 22 returned

💡 All products checked; only those with price greater than all book prices included.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	After Step 5	Final
subquery_result	empty	[10, 15, 20]	[10, 15, 20]	[10, 15, 20]	[10, 15, 20]	[10, 15, 20]	[10, 15, 20]
current_product_price	N/A	N/A	25	18	15	22	N/A
comparison_result	N/A	N/A	True	False	False	True	N/A
rows_returned	empty	empty	[price=25]	[price=25]	[price=25]	[price=25, price=22]	[price=25, price=22]

Key Moments - 3 Insights

Why does the product with price 18 get excluded even though 18 is greater than some book prices?

Because ALL means the condition must be true for every value in the subquery result. Since 18 is not greater than 20, the condition fails (see execution_table step 3).

Are ANY and SOME different in how they work with subqueries?

No, ANY and SOME are synonyms in SQL and both check if the condition is true for at least one value in the subquery result.

What happens if the subquery returns no rows?

For ALL, the condition is true by default (no values to contradict). For ANY/SOME, the condition is false (no values to satisfy it).

Visual Quiz - 3 Questions

Look at the execution_table, what is the comparison result for product price 15 at step 4?

A False

B True

C Unknown

D Not evaluated

💡 Hint

Check the 'Comparison' column at step 4 in the execution_table.

At which step does the query include a product with price 22 in the results?

A Step 3

B Step 4

C Step 5

D Step 2

💡 Hint

Look at the 'Rows Returned' column in the execution_table for when price 22 is added.

If the subquery returned an empty list, what would be the result of 'price > ALL (empty list)'?

A Always false

B Always true

C Depends on price

D Error

💡 Hint

Refer to key_moments about behavior when subquery returns no rows.

Concept Snapshot

ALL, ANY, SOME with subqueries:
- ALL: condition must be true for every value in subquery result.
- ANY/SOME: condition true if true for at least one value.
- Subquery runs first, returns list.
- Outer query compares each row's value to list.
- Useful for filtering based on sets of values.

Full Transcript

This visual execution shows how SQL uses ALL, ANY, and SOME with subqueries. First, the subquery runs and returns a list of values. Then, for each row in the outer query, the condition compares its value to all or any of those values. ALL requires the condition to be true for every value, while ANY and SOME require it to be true for at least one. The example selects products priced higher than all book prices. Step by step, each product price is checked against the list. Products priced 25 and 22 pass the ALL condition and are included. The visual also clarifies common confusions, like the difference between ALL and ANY, and what happens if the subquery returns no rows.

Practice

1. What does the ALL keyword do in a PostgreSQL query with a subquery?

easy

A. Returns all rows from the main query regardless of the subquery.

B. Checks if a condition is true for at least one value returned by the subquery.

C. Checks if a condition is true for every value returned by the subquery.

D. Joins the main query with the subquery on matching columns.

Solution

1. Step 1: Understand the role of ALL

   The ALL keyword compares a value to every value returned by a subquery.

2. Step 2: Interpret the condition

   It returns true only if the condition holds for all values from the subquery.

3. Final Answer:

   Checks if a condition is true for every value returned by the subquery. -> Option C

4. Quick Check:

   ALL means every value must satisfy condition [OK]

Hint: ALL means every value must meet the condition [OK]

Common Mistakes:

• Confusing ALL with ANY or SOME
• Thinking ALL returns all rows without condition
• Assuming ALL works like a JOIN

2. Which of the following is the correct syntax to use ANY with a subquery in PostgreSQL?

easy

A. SELECT * FROM table WHERE column = ANY (SELECT col FROM othertable);

B. SELECT * FROM table WHERE column ANY = (SELECT col FROM othertable);

C. SELECT * FROM table WHERE ANY column = (SELECT col FROM othertable);

D. SELECT * FROM table WHERE column = (ANY SELECT col FROM othertable);

Solution

1. Step 1: Recall correct ANY syntax

   ANY is used as: column operator ANY (subquery), e.g., column = ANY (subquery).

2. Step 2: Check each option

   SELECT * FROM table WHERE column = ANY (SELECT col FROM othertable); matches correct syntax. Others have misplaced ANY or wrong order.

3. Final Answer:

   SELECT * FROM table WHERE column = ANY (SELECT col FROM othertable); -> Option A

4. Quick Check:

   ANY goes after operator and before subquery in parentheses [OK]

Hint: Use operator = ANY (subquery) syntax [OK]

Common Mistakes:

• Placing ANY before the column name
• Omitting parentheses around subquery
• Using ANY without an operator

3. Given the tables:
products(id, price)
and
discounts(min_price)
What does this query return?

SELECT id FROM products WHERE price > ALL (SELECT min_price FROM discounts);

medium

A. All product ids regardless of price.

B. Product ids with price greater than every min_price in discounts.

C. Product ids with price equal to any min_price in discounts.

D. Product ids with price greater than at least one min_price in discounts.

Solution

1. Step 1: Understand the ALL condition

   The condition price > ALL (subquery) means price must be greater than every min_price returned.

2. Step 2: Interpret the query result

   Only products with price higher than all discount min_prices are selected.

3. Final Answer:

   Product ids with price greater than every min_price in discounts. -> Option B

4. Quick Check:

   price > ALL means greater than all values [OK]

Hint: ALL means condition holds for every subquery value [OK]

Common Mistakes:

• Confusing ALL with ANY or SOME
• Thinking it returns products with price greater than some min_price
• Assuming it returns all products

4. Identify the error in this query:

SELECT name FROM employees WHERE salary > ANY SELECT bonus FROM bonuses;

medium

A. Using ANY instead of ALL causes syntax error.

B. Incorrect use of > operator with ANY.

C. Subquery should be in FROM clause, not WHERE.

D. Missing parentheses around the subquery after ANY.

Solution

1. Step 1: Check ANY syntax

   ANY must be followed by a subquery enclosed in parentheses.

2. Step 2: Identify missing parentheses

   The query lacks parentheses around SELECT bonus FROM bonuses after ANY.

3. Final Answer:

   Missing parentheses around the subquery after ANY. -> Option D

4. Quick Check:

   ANY requires parentheses around subquery [OK]

Hint: Always put subquery in parentheses after ANY [OK]

Common Mistakes:

• Omitting parentheses around subquery
• Misplacing ANY keyword
• Confusing ANY with ALL syntax

5. You want to find all customers whose order total is less than the maximum order total of any customer in the 'orders' table. Which query correctly uses ANY to achieve this?

hard

A. SELECT customer_id FROM orders WHERE total < ANY (SELECT total FROM orders);

B. SELECT customer_id FROM orders WHERE total < ALL (SELECT total FROM orders);

C. SELECT customer_id FROM orders WHERE total > ANY (SELECT total FROM orders);

D. SELECT customer_id FROM orders WHERE total = ANY (SELECT MAX(total) FROM orders);

Solution

1. Step 1: Understand the goal

   We want customers with order total less than the maximum order total of any customer.

2. Step 2: Analyze options

   SELECT customer_id FROM orders WHERE total < ANY (SELECT total FROM orders); checks if total is less than any total in orders, which includes the maximum, so it works.

3. Step 3: Why others are wrong

   SELECT customer_id FROM orders WHERE total < ALL (SELECT total FROM orders); uses ALL, meaning less than every total, which is too strict. SELECT customer_id FROM orders WHERE total > ANY (SELECT total FROM orders); checks greater than any total, wrong direction. SELECT customer_id FROM orders WHERE total = ANY (SELECT MAX(total) FROM orders); compares to MAX(total) with ANY, which is redundant and incorrect.

4. Final Answer:

   SELECT customer_id FROM orders WHERE total < ANY (SELECT total FROM orders); -> Option A

5. Quick Check:

   Use ANY to compare with multiple values including max [OK]

Hint: Use ANY to compare with multiple values including max [OK]

Common Mistakes:

• Using ALL instead of ANY for less than max
• Comparing with MAX inside ANY incorrectly
• Confusing > and < operators