ALL

Hook: Interviewers love ALL because it looks simple, but one tiny detail — especially the empty-set case — separates memorized syntax from real understanding.

Question: What does ALL mean in SQL?

Answer: ALL is a quantified comparison operator. It means the comparison must be true for every value returned by a subquery, such as > ALL (...) or = ALL (...). If the subquery returns no rows, the condition is usually treated as true, because there is no row that breaks the rule.

Interview-Ready Answer: In SQL, ALL means “this comparison must hold for every value from the subquery.” So x > ALL (subquery) means x is greater than every returned value. A key detail is that if the subquery is empty, the condition evaluates to true, which is a classic interview gotcha.

What ALL really does

Detailed Explanation: Think of ALL as a universal check: every row must pass. It appears after a comparison operator, not by itself, so you write patterns like > ALL, <= ALL, or = ALL. The database compares the left-hand value against each row produced by the subquery and only returns TRUE if none of those comparisons fail.

1. The subquery runs and produces a list of values.
2. The database compares the left expression to each value using the operator you chose.
3. If even one comparison is false, the whole ALL condition becomes false.
4. If all comparisons are true, the condition is true.
5. If the subquery returns no rows, there is nothing to disprove the condition, so ALL is true.

How it differs from ANY / SOME

ANY (also spelled SOME in standard SQL) is the opposite style: only one matching row is enough. That means ALL is a “every row” test, while ANY is an “at least one row” test.

When to use it

ALL is useful when your business rule is naturally universal: “salary must be higher than every salary in a department,” “score must be at least as high as all minimum thresholds,” or “a product must beat all competitor prices.” It is often clearer than rewriting the same logic with nested aggregates, especially when you want to express the rule directly.

Under the hood and performance

1. The optimizer may execute the subquery and compare each row, or it may rewrite the query into an aggregate form like MAX or MIN when that is logically safe.
2. For > ALL, the database only needs the maximum subquery value to decide the result, so a good optimizer can reduce work.
3. With indexes on the subquery column, the engine may stop early or use index-only access, but the exact plan depends on the database.

In big-O terms, a naive evaluation is O(n) over the subquery rows, with O(1) extra memory if rows are streamed. In practice, cost is usually dominated by how quickly the subquery can be filtered and whether the planner can rewrite it. For a small lookup table, the difference is tiny; for millions of rows, indexes and statistics matter a lot.

Important edge cases

• Empty subquery: x > ALL (empty set) is true.
• NULL values: NULL can make the result unknown, not true or false, because SQL uses three-valued logic. If the subquery returns only NULLs, the comparison often becomes UNKNOWN.
• = ALL is strict: it means every returned value must equal the left side. If the subquery can return two different values, this becomes false.
• Use with care on text and dates: the comparison is based on the operator’s normal ordering rules, so collation and date sorting still apply.

Memory Hook: Think “ALL = every apple in the basket.” One bad apple ruins the condition; an empty basket counts as not spoiled.

Good mental shortcut

For > ALL (subquery), many candidates mentally translate it to “greater than the maximum value,” and that works when the subquery is non-empty and non-NULL. For < ALL, think “less than the minimum.” Just remember the empty-set and NULL rules, because that is where interviews get tricky.

Real-World Example: Imagine a checkout service that stores discounts approved by regional managers. A pricing rule might say: “A flash-sale price can go live only if it is below all approved competitor prices for that region.” That is a natural < ALL rule.

What goes wrong when someone misunderstands it? They might use ANY instead of ALL, which lets the sale price pass if it beats just one competitor instead of every competitor. In production, you would see bad price drops, support tickets about incorrect charges, and logs showing valid-looking query results that still violate the business rule. The bug is dangerous because the SQL returns rows — just not the right ones.

Another classic failure is forgetting the empty-set behavior. If the competitor table is temporarily empty for a region, < ALL becomes true, and a new price may be approved when the intended behavior was “block until data exists.” That kind of issue shows up as sudden approvals in the logs during a feed outage, not as a syntax error.

Follow-up & Tricky Questions:

• How is ALL different from ANY? ALL requires every comparison to be true; ANY requires at least one. A good shortcut is “all = every, any = one.”
• What happens if the subquery returns no rows? The condition is true, because there is no counterexample. This is especially important for approval checks and safety rules.
• How do NULLs affect ALL? NULL can turn the result into UNKNOWN. In a WHERE clause, UNKNOWN acts like false, so you usually filter NULLs out if they are not meaningful.
• Can > ALL (subquery) be rewritten? Yes, often as a comparison with MAX when the subquery is known to be non-empty and non-NULL. The optimizer may do this automatically, but only when it is logically safe.
• Is = ALL the same as IN? No. IN means “matches at least one value,” while = ALL means “matches every value,” which is much stricter and often false if the subquery returns multiple different rows.
• Is x > ALL (subquery) the same as x > MAX(subquery)? Usually only when the subquery is guaranteed to return at least one non-NULL value. Without that guarantee, the empty-set and NULL behavior is different.
• Tricky: does x < ALL (subquery) mean smaller than all rows or smaller than any row? Smaller than all rows. Candidates often reverse this in their head; the word ALL is the clue.
• Tricky: if one value is NULL, is the result always false? No. It is often UNKNOWN, which is different from false in SQL’s three-valued logic.
• Tricky: if the subquery is empty, should an approval check pass or fail? SQL says the comparison is true, but your business rule may want fail-closed behavior. You may need an extra existence check if empty data is unsafe.

Common Mistakes:

• Using ANY when you mean ALL — correction: ask whether one match is enough or every row must pass.
• Forgetting the empty-set rule — correction: remember that ALL over no rows is true.
• Ignoring NULLs — correction: filter them out when they should not participate in the comparison.
• Rewriting to MAX or MIN too quickly — correction: only do that when the subquery cannot be empty and cannot contain meaningful NULLs.

Memory Hook: “ALL = every apple must be good.” If one apple is bad, the basket fails; if the basket is empty, SQL says there is no bad apple, so it passes.

Cheat Sheet:

• ALL = universal comparison: every row must satisfy the condition.
• > ALL often feels like “greater than the maximum.”
• < ALL often feels like “less than the minimum.”
• Empty subquery with ALL returns true.
• NULL can produce UNKNOWN; filter it out when needed.
• ANY is the opposite style: one match is enough.

Practice Tasks:

• Write a query that finds products priced lower than ALL competitor prices in the same category.
• Rewrite that query once using MAX and compare the results when the competitor set is empty.
• Add a NULL into the competitor prices and observe how the result changes before and after filtering NULLs.