DevLearningTools

LEARN · SQL

SQL ALL

ALL compares a value against every row a subquery returns, true only if the comparison holds for every one of them. Learn its syntax and how it relates to ANY.

← SQL ANYSQL SELECT INTO →

ALL is ANY's counterpart: instead of being true when a comparison matches at least one row a subquery returns, ALL is true only when it matches every single one.

Learning Objectives

  • Use > ALL and < ALL against a subquery's results.
  • Explain how ALL relates to MIN()/MAX(), the same way ANY does.
  • Choose correctly between ANY and ALL for a given question.

Basic ALL

This returns only employees earning more than every single Marketing employee, in practice, more than the highest Marketing salary. salary > ALL (subquery) requires the comparison to hold against all of the subquery's rows, a much stricter condition than > ANY.

MySQL / MariaDB / PostgreSQL / SQL Server / Oracle
SELECT first_name, salary
FROM employees
WHERE salary > ALL (
  SELECT salary FROM employees WHERE department_id = 3
);

ANY vs ALL

> ANYAt least one
  • True if greater than the smallest value
  • Equivalent to > MIN(subquery)
  • The looser, easier-to-satisfy condition
> ALLEvery one
  • True only if greater than the largest value
  • Equivalent to > MAX(subquery)
  • The stricter, harder-to-satisfy condition

The Portable Rewrite

Same SQLite gap as ANY, ALL isn't implemented there either. Replacing > ALL (subquery) with > MAX(subquery) produces the same result and runs everywhere, the same pattern used for ANY in the previous lesson, just with MAX() instead of MIN().

SQLite-compatible, and arguably clearer everywhere
SELECT first_name, salary
FROM employees
WHERE salary > (
  SELECT MAX(salary) FROM employees WHERE department_id = 3
);

Try It Yourself

Write a query, portable to SQLite, that returns employees earning less than every employee in Engineering.

Answer
SELECT first_name, salary
FROM employees
WHERE salary < (
  SELECT MIN(salary) FROM employees WHERE department_id = 1
);
NOTE

"Less than every value in a set" rewrites to "less than the set's minimum," the mirror image of the > ALL / MAX() relationship above, this is < ALL / MIN().

Frequently Asked Questions

What does ALL return if the subquery returns zero rows?

True, unconditionally, the opposite of ANY's behavior. With nothing to fail the comparison against, "true for every row returned" is vacuously satisfied.

Can ALL be used with equality, = ALL?

Syntactically yes, but it's only true if the subquery returns exactly one distinct value (or zero rows, per the rule above), rarely the intended question. <> ALL (not equal to every value) is far more common, and is equivalent to NOT IN, with the same NULL caveats NOT IN has.

Common Beginner Mistakes

Mixing up which one, ANY or ALL, matches the intended question

"Higher than anyone in Marketing" is > ANY (the minimum). "Higher than everyone in Marketing" is > ALL (the maximum). Reading the English question carefully, at least one vs every single one, is the reliable way to pick correctly.

Expecting <> ALL to behave like NOT IN without NOT IN's NULL risk

It doesn't, <> ALL shares the exact same NULL-handling danger as NOT IN, since it's defined the same way underneath. NOT EXISTS, from two lessons back, remains the NULL-safe option for a "no match anywhere" question.

Best Practices

  • Translate the English question literally, "at least one" is ANY, "every one" is ALL, before writing the query.
  • Prefer a MIN()/MAX() rewrite for portability and often for readability too, even on databases that do support ALL.
  • Avoid <> ALL as a NOT IN substitute, it carries the identical NULL risk, reach for NOT EXISTS instead.

Interview Questions

What's the difference between > ANY and > ALL against the same subquery?

> ANY is true if the value exceeds the subquery's smallest result, equivalent to comparing against MIN(). > ALL is true only if the value exceeds the subquery's largest result, equivalent to comparing against MAX(), a stricter condition.

Why would you rewrite salary > ALL (subquery) as salary > (SELECT MAX(...)) instead of using ALL directly?

ALL isn't supported on every database, SQLite notably lacks it, so the MAX() rewrite is portable everywhere. It's also often more immediately readable: MAX() states the actual comparison value directly, rather than requiring the reader to mentally translate what ALL implies.

Summary

ALL requires a comparison to hold against every row a subquery returns, equivalent to comparing against MAX() for > and >=, or MIN() for < and <=, the stricter counterpart to ANY. Like ANY, SQLite doesn't support it, a MIN()/MAX() rewrite is the portable, often clearer, alternative.

What's Next?

SELECT INTO next, a way to copy query results into a brand-new table, with one of the biggest syntax splits in this entire course.

</>

Ready to Practice SQL?

Write and run real SQL queries online, right in your browser, no install or signup required. Pick a playground based on which database you want to practice on.

SQLite / PostgreSQL

Fast, no server, no ads.

Open DB Fiddle ↗

MySQL / SQL Server

Switch databases with one dropdown.

Open SQL Fiddle ↗

Both open a third-party SQL playground in a new tab. External site terms and privacy policy apply.

← SQL ANYSQL SELECT INTO →