|
|
Posted by Hugo Kornelis on 10/07/02 11:52
On 10 Jul 2006 15:29:37 -0700, Branco Medeiros wrote:
(snip)
>Attempting to find the unused records, I issued the following query:
>
>a)
>
> SELECT COD_RUA FROM RUA
> WHERE COD_RUA NOT IN (
> SELECT COD_RUA FROM CONSUMIDOR
> )
>
>To my surprise, the query came out empty. But the following query
>showed the 200 or so records which *are* being used:
(snip)
>I know that there are many other possible solutions to the query
>(including left joins), but what I don't understand is why the query a)
>failed.
>
>Can some of you, oh mighty gurus, enlighten me?
Hi Branco,
As you alreadyy found out, this has to do with rows in CONSUMIDOR that
have COD_RUA equal to NULL.
Suppose that CONSUMIDOR has only foru rows, with COD_RUA equal to 13,
27, 90 and NULL. In that case, the above query with NOT IN is equivalent
to this query:
SELECT COD_RUA FROM RUA
WHERE COD_RUA NOT IN (13, 27, 90, NULL)
And this, in turn, is equivalent to
SELECT COD_RUA FROM RUA
WHERE COD_RUA <> 13
AND COD_RUA <> 27
AND COD_RUA <> 90
AND COD_RUA <> NULL
Now, any comparisoon of any value against NULL is always the "third"
boolan value in three-valued logic: Unknown. Since True AND Unknown
evaluates to Unknown, and False AND Unknown evaluates to False, the
WHERE clause above can only ever evaluate to Unknown or False, but never
to True - and the only rows included in the result of the SELECT
statement are those for which the WHERE clause is True! That''s whhy
you'll never see any rows in the output from this query.
Best practice: use IN and NOT IN only wiith a list of literals. Never
use either of them with a subquery. Apart from the problem with NULLs,
there are other pproblems: they often don't perform well on SQL Server,
and you can't extend the syntax for multi-column comparisons.
Every [NOT] IN with a subquery can always be transformed to a [NOT]
EXISTS with a subquery - and [NOT] EXISTS has no problems with NULL
values in the subquery, supports multi-column comparisons and usually
performs as good or better as [NOT] IN.
SELECT r.COD_RUA
FROM RUA AS r
WHERE NOT EXISTS
(SELECT *
FROM CONSUMIDOR AS c
WHERE c.COD_RUA = r.COD_RUA)
--
Hugo Kornelis, SQL Server MVP
Navigation:
[Reply to this message]
|