ALL vs ANY in SQL Server

Question

I am just trying to execute the following 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 gives the following 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 evaluates to 1.

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

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

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

Is there anything wrong with my understanding?

According to ALL documentation , it evaluates a scalar value in a list of values.

And it returns true if:

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

How can I match (601, NULL) 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 should return null strings. Not zero. A sub query that returns zero rows gets NULL when used in scalar mode (as in the Grade column), but the query itself does not return this.

The SQL standard defines different behavior for ALL and ANY (AKA SOME ) when it comes to comparing with empty sets.

For ALL 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 a classic logic in which the statements “all cell phones in the room are turned off” and “all cell phones in the room are turned on” are considered true ( although unclear ) if there are no cell phones in the room.

For ANY / SOME must be at least one pair that actually matches.

The corresponding bit of the SQL standard for this 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.