ALL vs ANY evaluation in SQL Server

Question

I'm just trying out now the below query:

SELECT DISTINCT code,
                CASE
                  WHEN id = ANY (SELECT DISTINCT u.id
                                 FROM   unit u
                                        LEFT JOIN unit_const uc
                                               ON u.id = uc.hid
                                 WHERE  u.property = 502
                                        AND type = 'Acq') THEN 1
                  ELSE 0
                END                       AS Case_Eval,
                (SELECT DISTINCT u.id
                 FROM   unit u
                        LEFT JOIN unit_const uc
                               ON u.id = uc.hid
                 WHERE  u.property = 502
                        AND type = 'Acq') AS Evaluation
FROM   unit
WHERE  property = 502 

which correctly gives the below result:

+---------------------------------------+
| Code       Case_Eval   Evaluation     |
+---------------------------------------+
| TP2_U1     0           NULL           |
| TP2_U2     0           NULL           |
| TP2_U3     0           NULL           |
| TP2_U4     0           NULL           |
+---------------------------------------+

But if I switch from ANY to ALL then the CASE statement is evaluated to 1.

+---------------------------------------+
| Code       Case_Eval   Evaluation     |
+---------------------------------------+
| TP2_U1     1           NULL           |
| TP2_U2     1           NULL           |
| TP2_U3     1           NULL           |
| TP2_U4     1           NULL           |
+---------------------------------------+

But as you can see the SELECT statement that returns the value to be compared in the CASE is NULL all the time.

How can the CASE statement evaluate this to true? The unit ID is not NULL (they are 601, 602, 603 and 604 for the 4 units), so how when compared to ALL(NULL) is results into true?

Is there something incorrect in my understanding?

As per ALL documentation it evaluates a scalar value to a list of values.

And it returns true if:

"Returns TRUE when the comparison specified is TRUE for all pairs (scalar_expression, x), when x is a value in the single-column set; otherwise returns FALSE."

How can pair(601, NULL) evaluate to True?

Answer 1

SELECT DISTINCT u.id
FROM   unit u
       LEFT JOIN unit_const uc
         ON u.id = uc.hid
WHERE  u.property = 502
       AND type = 'Acq' 

The above statement must return zero rows. Not null. A sub query that returns zero rows is given a value of NULL when used in a scalar fashion (as with your "Evaluation" column) but the sub query itself doesn't return that.

The SQL Standard defines different behaviour for ALL and ANY (AKA SOME) when it comes to comparing against empty sets.

For ALL the comparison evaluates to true

If T is empty or if the implied <comparison predicate> is true for every row RT 
in T, then "R <comp op> <all> T" is true.

This is per classical logic where the statements "all cell phones in the room are turned off" and "all cell phones in the room are turned on" are both regarded as true (though vacuously) if the room has no cell phones.

For Any/Some there must be at least one pair that actually matches.

The relevant bit of the SQL Standard for that is below.

If T is empty or if the implied <comparison predicate> is false for every row RT 
in T, then "R <comp op> <some> T" is false.