ALL

Hook: Interviewers love ALL because it looks simple, but it quietly tests whether you understand subqueries, empty results, and SQL's true/false/unknown logic.

Question: What does ALL do in SQL?

Answer: ALL compares one value to every value returned by a subquery. The condition is true only if it is true for every row in that inner result. A very important detail is that ALL over an empty subquery is true, and NULL values can turn the result into unknown.

Interview-Ready Answer: In SQL, ALL means I want my comparison to hold against every row returned by the subquery. For example, salary > ALL (SELECT salary FROM sales) means the salary must be greater than every sales salary. Two things I always remember are that an empty subquery makes ALL true, and NULL values can change the result to unknown, so I often filter NULLs or rewrite to MAX or MIN when the logic allows it.

What ALL really means

Detailed Explanation: ALL is a quantifier, which means a word that tells SQL how to judge a whole set. In plain English, it means “for every row.” You will see it in expressions like > ALL, = ALL, < ALL, and <> ALL.

How it works under the hood

1. The database runs the inner subquery or CTE first and gets a set of values.
2. It compares the outer value against each inner value using the operator you chose.
3. If even one comparison is false, the whole ALL condition fails.
4. If every comparison is true, the whole condition passes.
5. If the inner result is empty, ALL is true because there is no row that can prove the condition false.
6. If the inner result contains NULL, SQL's three-valued logic applies: true, false, and unknown. A single unknown can make the overall result unknown unless a false already exists.

Why people use it

ALL is useful when you want a strong comparison against an entire set, such as “higher than every salary in Sales” or “cheaper than all competing offers.” It reads naturally when the business rule is about every row, not just one row or any row.

ALL vs ANY vs IN vs EXISTS

Performance notes: Logically, ALL must consider the inner set, so a naive plan can be proportional to the number of inner rows for each outer row. In a correlated subquery, that can become expensive fast: for example, 1 million outer rows times 10,000 inner rows can be disastrous if the optimizer cannot rewrite it. Good optimizers often transform simple cases like > ALL into a MAX check or an anti-join, and an index on the inner column can help a lot.

One subtle but important rewrite rule: x > ALL (subquery) is similar to x > (SELECT MAX(...)) only when the subquery is non-empty and you have handled NULLs. If the subquery is empty, ALL is true but MAX returns NULL, and x > NULL becomes unknown. That difference is a classic interview trap.

When to reach for it

• Use ALL when the rule is “must beat every value” or “must satisfy every condition.”
• Use a CTE when the inner logic is hard to read; the CTE is just a named subquery, so ALL works the same way.
• Prefer MAX/MIN rewrites only when the semantics match and empty-set behavior is safe.

Memory Hook: Picture a hallway of guards. ALL means every guard must say yes. One no blocks you, and an empty hallway means nobody said no, so you pass.

Real-World Example: In a checkout or risk-scoring service, a team might look for orders whose amount is greater than every amount in a customer's recent refund history, or employees whose salary beats every salary in a benchmark group. That kind of rule is a natural fit for ALL because the business question is literally “greater than all of them,” not “greater than one of them.”

What goes wrong when someone misunderstands it? A developer expects > ALL to behave like > MAX, but the filtered subquery is accidentally empty for a new region. Suddenly the condition becomes true for every row, and a fraud rule starts flagging or approving far too many orders. In production, this shows up as a sudden spike in alerts, confusing logs like “matched_rule=high_risk” for almost every transaction, and customers complaining that normal payments are being blocked.

Follow-up & Tricky Questions:

• How is ALL different from ANY? ALL needs every row to satisfy the comparison, while ANY needs just one. That difference changes both the result and the best rewrite strategy.
• Can ALL be rewritten with MAX or MIN? Sometimes. For > ALL, MAX is a good shortcut only if the subquery is not empty and you have handled NULLs correctly; otherwise the semantics are different.
• What happens when the subquery returns no rows? ALL is true, which surprises many people. That is because there is no counterexample to the condition.
• How do NULLs affect ALL? If a comparison against a NULL becomes unknown and no row proves the condition false, the whole result can become unknown, which behaves like false in a WHERE clause.
• Is ALL the same as NOT IN? No. x <> ALL (subquery) is close in spirit to x NOT IN (subquery), but NOT IN is especially dangerous when the subquery can produce NULL.
• What if the outer query is correlated? Then the inner query runs conceptually per outer row, so the optimizer matters a lot. A bad plan can be much slower than a simple aggregate rewrite.
• Tricky: Is salary > ALL (empty set) true or false? True. This is one of the easiest SQL gotchas to miss.
• Tricky: Is salary > ALL (1, 2, NULL) true for salary = 10? It is not safely true; the NULL makes one comparison unknown, so the overall result becomes unknown unless another row makes it false first.
• Tricky: Does = ALL mean the same as “equals the maximum”? No. It means the value equals every row in the set, so it only works when all rows are identical and non-null.

Common Mistakes:

• Thinking ALL means “at least one.” Correction: that is ANY; ALL means every row.
• Forgetting the empty-set rule. Correction: ALL on no rows is true, so test empty subqueries explicitly.
• Ignoring NULLs in the inner query. Correction: filter them out when the business rule should only consider real values.
• Rewriting to MAX or MIN too casually. Correction: preserve empty-set and NULL behavior before optimizing.

Memory Hook: All guards must say yes. One no blocks you; no guards means no one blocked you.

Cheat Sheet:

• ALL = every row in the subquery must satisfy the comparison.
• Empty subquery = TRUE.
• NULL can make the result UNKNOWN.
• > ALL often suggests a MAX rewrite; < ALL often suggests MIN.
• ANY is the opposite style: one row is enough.
• Use a CTE to make the inner set easier to read, but it does not change ALL's meaning.

Practice Tasks:

• Write a query for employees whose salary is less than every salary in Engineering.
• Rewrite that query once with ALL and once with MIN; compare the empty-set behavior.
• Add a NULL salary into the subquery and observe how the result changes.