paradoxical query results?

[jbm05]

Hi -

I am getting perplexing, seemingly inconsistent results from two simple
queries, and I was wondering if I'm doing something obviously
brainless.

The first query is:

select colX, colY from
(select distinct colX from tblX) x full outer join
(select distinct colY from tblY) y
on x.colX=y.colY
order by y.colY

This query returns 28109 rows, the first 38 of which are NULL in colY,
but NOT NULL in colX. The second query is:

select colX from tblX where colX not in (select colY from tblY)

This query returns nothing, and that's the problem. Shouldn't this
query return the 38 values that are in colX but not colY? Shouldn't the
subquery return the list of values in colY, which as the first query
demonstrates, is lacking exactly 38 values that appear in colX?

Thanks for any help.

[David Portas]

I'm guessing that you have NULLs in ColY. In that case the NOT IN
expression will always evaluate UNKNOWN. Try:

SELECT colX
FROM tblX
WHERE colX NOT IN
(SELECT colY
FROM tblY
WHERE colY IS NOT NULL)

or use NOT EXISTS:

SELECT colX
FROM tblX
WHERE NOT EXISTS
(SELECT *
FROM tblY
WHERE colY = tblX.colX)

--
David Portas
SQL Server MVP
--

[jbm05]

I was completely unaware of that behavior; adding the where condition
to the subquery solved the problem. Thank you very much.