Why does "= ALL (subquery)" evaluate to true if the subquery returnes no results?

Question

I would expect "= ALL (subquery)" to evaluate to false if the subquery returns no results.

However in testing I find that not to be the case:

--put one record in #Orders
SELECT 1 AS 'OrderID'
INTO #Orders;

--put one record in #OrderLines
SELECT
    1 AS 'OrderID'
    ,1 AS 'OrderLineID'
    ,3 AS 'Quantity'
INTO #OrderLines;

--as expected this returns the record in #Orders
SELECT *
FROM #Orders
WHERE 3 = ALL
    (
    SELECT Quantity
    FROM #OrderLines
    );

--now delete the record in #OrderLines
DELETE FROM #OrderLines;

--this still returns the record from #Orders even though the subquery returns no results
SELECT *
FROM #Orders
WHERE 3 = ALL
    (
    SELECT Quantity
    FROM #OrderLines
    );

Execution plan for the final select statement: https://www.brentozar.com/pastetheplan/?id=H1jQ2YgIK

Tested on:

• Microsoft SQL Server 2017 (RTM-CU20) (KB4541283) - 14.0.3294.2 (X64)
• Microsoft SQL Server 2017 (RTM-CU25) (KB5003830) - 14.0.3401.7 (X64)

When searching I find unofficial sources which say that "= ALL (subquery)" evaluates to true if the subquery returns no results:

• "The ALL must be preceded by the comparison operators and evaluates to TRUE if the query returns no rows" https://dotnettutorials.net/lesson/all-operator-sql-server/

• "The ALL must be preceded by the comparison operators and evaluates to TRUE if the query returns no rows" https://www.w3resource.com/sql/special-operators/sql_all.php

But I don't see anything in the official documentation (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/all-transact-sql?view=sql-server-ver15) that supports that idea, in fact it would seem to dispute it: "ALL requires the scalar_expression to compare positively to every value that is returned by the subquery"

Questions

1. Is it expected behavior in SQL Server to evaluate ALL as true if the subquery returns no results?
2. If the answer to #1 is "yes":
   • Is it documented somewhere?
   • What is the explanation for that behavior? In the code example above 3 does not compare positively with no results so it seems highly unintuitive that the query should return results

Thanks for any assistance and insight.

Answer 1

Paraphrasing the documentation:

... scalar_expression = ALL (subquery) would evaluate as FALSE if some of the values of the subquery don't meet the criteria of the expression.

It's subtle, but the intention seems to be return false if some values do not satisfy the condition, true otherwise. In the edge case of there being no values, there are no values that don't satisfy the condition, so it returns true.

The "problem" causing the perhaps surprising result is the word "some", which implies existence. If no values exist, there can't be "some" values that are false, so it's true.

You could say it's based on double negative logic where the edge case happens to fall in the unexpected half of the result.

---

As a side note, I have written a huge amount of SQL in my career and never used this keyword, nor seen it used.

Recommendation: Do not use.