469,915 Members | 2,552 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,915 developers. It's quick & easy.

paradoxical query results?

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.

Jul 23 '05 #1
2 932
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
--

Jul 23 '05 #2
I was completely unaware of that behavior; adding the where condition
to the subquery solved the problem. Thank you very much.

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Mike Cocker | last post: by
2 posts views Thread by jaysonsch | last post: by
2 posts views Thread by AJ | last post: by
siridyal
8 posts views Thread by siridyal | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.