|
|
Posted by Hugo Kornelis on 11/23/95 11:59
On Fri, 29 Sep 2006 03:24:09 -0000, Dimitri Furman wrote:
(snip)
> the expression evaluates to TRUE
>when the select statement produces an empty set, which doesn't make sense
>to me.
Hi Dimitri,
Hmm, it actuallly makes perfect sense to me. This is a bit like me
bragging that I've slain every single dragon that has ever set fooot in
my back yard. And that I've rescued every princess that has been locked
away in a high tower during my life time.
Of course, I've never fought a dragon or rescued a princess - but since
no dragon has ever set foot in my back yard (see! they are THAT afraid
of me <g>) and no princess has been locked away in a high tower during
my life time, the statements above are still true.
> Even more interesting, the expression
>
>NULL = ALL (SELECT contract FROM dbo.authors WHERE state = '')
This is an interesting, since you can defend two answers. The simple
version is that any comparison involving NULL should result in unknown.
The other, almost equally simple, version is that if the subquery
produces an empty set, it doesn;t matter what value is on the left-hand
side; we can be sure that it'll be equal to all values in the empty set.
So we don't care if the value for the left-hand side is supplied or
missing, since we can evaluate anyhow.
Since both definitions have their merit, we'll have to resort to
consulting the documentation. Books Online says this about ALL:
"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."
Since there are no values in the set, there are no pairs - just as there
are no dragons in my back yard.
To double-check, I consulted the description of ALL in the ISO standard
SQL-2003. That one puts it even more bluntly, since it excplicitly
mentions that case that the subquery returns an empty set:
"Case:
"a) 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."
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|