PC Slower Than It Used to Be?
A free scan shows the junk files, broken settings and background clutter dragging Windows down - then fixes them in one click.Free scan · Windows 10 & 11Crashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteiTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
In SQL, a comparison using the ALL quantifier is true when its comparison holds for every row returned by a subquery—including when that subquery returns no rows. For example, 10 > ALL (SELECT value FROM t) is true if the subquery is empty. This is a rule about SQL quantified comparisons, not a universal rule for comparison operators in every language.
What SQL ALL means
ALL is a quantifier used with a comparison operator. The expression x > ALL (subquery) asks whether x is greater than every value returned by that subquery. Firebird’s documentation describes this as a universal condition: the comparison must hold for all returned rows. Firebird language reference
If there are no rows, there is no value that makes the comparison fail. In logic, a statement that something is true for every member of an empty set is considered true; this is often called vacuous truth. Firebird explicitly documents that an empty subselect makes ALL true. Firebird language reference
Free tools Windows power users keep installed
One-click scans. No signup required.
How ALL differs from ANY and SOME
ANY and SOME ask whether the comparison is true for at least one returned row. When there are no rows, no qualifying row exists, so the result is false. Firebird documents this empty-subquery contrast, and the SQL-99 reference gives the same rule. Firebird language reference SQL-99 reference, Chapter 31
#1 Best Overall
| Predicate | Question it asks | Empty subquery |
|---|---|---|
comparison ALL (subquery) |
Does the comparison hold for every returned row? | True |
comparison ANY (subquery) or SOME |
Does the comparison hold for at least one returned row? | False |
For instance, if the subquery returns no values, 10 > ALL (SELECT value FROM t) is true, while 10 > ANY (SELECT value FROM t) is false. The examples illustrate the documented rule; they do not depend on particular table contents beyond the subquery being empty.
Why NULL changes the picture
An empty result and a non-empty result containing NULL are different cases. SQL comparisons involving NULL can evaluate to UNKNOWN, rather than true or false. Consequently, when a subquery returns rows that include NULL, the quantified predicate may not behave like an ordinary two-valued Boolean test. Firebird’s Null Guide notes the special empty-set result even if the left-hand expression is NULL, but that exception should not be generalized to non-empty sets containing null values. Firebird language reference Firebird Null Guide
Check the language and database before applying the rule
The phrase “comparison operator” is broader than SQL’s ALL predicate. Here, ALL is a quantifier paired with a comparison operator such as > or =; it is not itself an operator like >. Syntax and supported comparisons can depend on the database. Firebird, for example, documents its accepted operators and requires its quantifiers to take a subselect, so consult the reference for the database you use. Firebird language reference
Other languages may give comparison operators entirely different collection behavior. In PowerShell 7.4, comparing a collection on the left returns matching elements; if nothing matches, the result is an empty array, not the SQL ALL result. Microsoft also notes that containment and type operators are exceptions that always return Booleans. Microsoft Learn: about_Comparison_Operators
C++ has a separate operator called the three-way comparison operator, commonly nicknamed the spaceship operator (<=>). Its name does not make it equivalent to SQL ALL. WG21 paper P0768R0
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
