Strange results from "not in" query

[Branco Medeiros]

Hi all,

Using SQL Server 2000, SP4.

I have a table of street names (Rua) whose ids (cod_rua) are foreign
keys into a consumer table (Consumidor). It turns out that the "Rua"
table has many unused records which I'd like to wipe out. For instance,
there are some 2800 unused records in the "Rua" table, and only some
200 records actually being used by the "Consumidor" table (which,
itself, has some 5000 records).

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:

b)

SELECT COD_RUA FROM RUA
WHERE COD_RUA IN (
SELECT COD_RUA FROM CONSUMIDOR
)

I've found two solutions for the query to list the records *not
existing* in the Consumidor table:

c)

SELECT COD_RUA FROM RUA
WHERE COD_RUA NOT IN (
SELECT COD_RUA FROM CONSUMIDOR
WHERE COD_RUA IS NOT NULL
)

d)

SELECT COD_RUA FROM RUA
WHERE COD_RUA NOT IN (
SELECT COD_RUA FROM RUA
WHERE COD_RUA IN (
SELECT COD_RUA FROM CONSUMIDOR
)
)

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?

For the record, here's how both tables are (partially) declared:

CREATE TABLE Rua (
Cod_Rua int NOT NULL ,
Rua varchar (35) NULL ,
-- ...
-- other unrelated fields
-- ...

CONSTRAINT Pk_CodRua
PRIMARY KEY (Cod_Rua)
)
CREATE TABLE Consumidor (
Cod_Consumidor int NOT NULL ,
Cod_Rua int NULL ,
-- ...
-- other unrelated fields
-- ...

CONSTRAINT Pk_CodConsumidor
PRIMARY KEY(Cod_Consumidor) ,

CONSTRAINT Fk_CodRua_Consumidor
FOREIGN KEY (Cod_Rua)
REFERENCES Rua (Cod_Rua)
)

Regards,

Branco Medeiros

[Hugo Kornelis]

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

[Branco Medeiros]

Hugo Kornelis wrote:
<snip>

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)

Thanks, Hugo!

That was fast and *extremely* cool.

As a side note, I really missed an idiom to verify the (non) existence
of items accross tables and it seems that the syntax of [NOT] EXISTS
that you suggest is exactly what I needed.

Thanks a lot.

Best regards,

Branco.

[--CELKO--]

Another suggestion: Change the DDL to make the column NOT NULL. This
is "mop the floor *and* fix the leak" philosophy.