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.