"NOT IN" phrase in WHERE clause doesn't work

Question

Post reply

"NOT IN" phrase in WHERE clause doesn't work

jim Etheridge

SSC-Addicted

Points: 430
More actions
March 31, 2009 at 3:22 pm

#72605

I have a work table (THINGS) that contains a single numeric column (THING_ID). When I use the following SQL, I get no rows returned:
SELECT * FROM BIG_THINGS
WHERE THING_ID NOT IN
(SELECT THING_ID FROM THINGS)
- the table BIG_THINGS also has the numeric column THING_ID.
- I know that some of the THING_IDs in BIG_THINGS do not exist in THINGS.
- if I take out the "NOT", the query works.
- if I use the classic LEFT JOIN solution on THING_ID, it works.
Why doesn't "NOT IN" work here?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if you use NOT IN, you must exclude the NULLs; anything IN = or LIKE NULL is undefined:

SELECT * FROM BIG_THINGS

WHERE THING_ID NOT IN

(SELECT THING_ID FROM THINGS WHERE THING_ID IS NOT NULL)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jim Etheridge SSC-Addicted Points: 430 More actions · Answer 2

Thanks, Lowell, that was the answer.

I didn't think I had any nulls in the THINGS table, but there was ONE record ...

Lowell SSC Guru Points: 323518 More actions · Answer 3

yeah when your nose is lost in the details,things like that still ding me once in a while, even after I've worked around it a zillion times.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!