ALL vs ANY evaluation in SQL Server -

ALL vs ANY evaluation in SQL Server -

i'm trying out below query:

select distinct code, case when id = (select distinct u.id unit u left bring together unit_const uc on u.id = uc.hid u.property = 502 , type = 'acq') 1 else 0 end case_eval, (select distinct u.id unit u left bring together unit_const uc on u.id = uc.hid u.property = 502 , type = 'acq') evaluation unit property = 502

which correctly gives below result:

+---------------------------------------+ | code case_eval evaluation | +---------------------------------------+ | tp2_u1 0 null | | tp2_u2 0 null | | tp2_u3 0 null | | tp2_u4 0 null | +---------------------------------------+

but if switch any all case statement evaluated 1.

+---------------------------------------+ | code case_eval evaluation | +---------------------------------------+ | tp2_u1 1 null | | tp2_u2 1 null | | tp2_u3 1 null | | tp2_u4 1 null | +---------------------------------------+

but can see select statement returns value compared in case null time.

how can case statement evaluate true? unit id not null (they 601, 602, 603 , 604 4 units), how when compared all(null) results true?

is there wrong in understanding?

as per all documentation evaluates scalar value list of values.

and returns true if:

"returns true when comparing specified true pairs (scalar_expression, x), when x value in single-column set; otherwise returns false."

how can pair(601, null) evaluate true?

select distinct u.id unit u left bring together unit_const uc on u.id = uc.hid u.property = 502 , type = 'acq'

the above statement must homecoming 0 rows. not null. sub query returns 0 rows given value of null when used in scalar fashion (as "evaluation" column) sub query doesn't homecoming that.

the sql standard defines different behaviour all , any (aka some) when comes comparing against empty sets.

for all comparing evaluates true

if t empty or if implied <comparison predicate> true every row rt in t, "r <comp op> <all> t" true.

this per classical logic statements "all cell phones in room turned off" , "all cell phones in room turned on" both regarded true (though vacuously) if room has no cell phones.

for any/some there must @ to the lowest degree 1 pair matches.

the relevant bit of sql standard below.

if t empty or if implied <comparison predicate> false every row rt in t, "r <comp op> <some> t" false.

sql-server sql-server-2008-r2