ALL vs ANY evaluation in SQL Server
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?
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.
上一篇: 实体化视图在添加约束时“无效”
下一篇: 所有与任何评估在SQL Server中