Why does this query do this?

From: Mtek <mtek_at_mtekusa.com>
Date: Fri, 11 Jul 2008 19:51:20 -0700 (PDT)
Message-ID: <7e6ae528-6c85-423e-8dc0-772efe562737@c65g2000hsa.googlegroups.com>

Hi,

Ok, I need help with this. Here is my data:

TABLE A

---

2468
3690
1357

TABLE B

---

3690
1234
NULL
1357
NULL
8900

Ok, I want to get a list of items in TABLE A which do not exist in TABLE B. Simple right?

SELECT id FROM table_a WHERE id NOT IN (SELECT id FROM table_b);

Guess what? It does not work. I get nothing. If I use IN, it returns things, but if I use NOT IN I get nothing However, if I do this:

SELECT id FROM table_a WHERE id NOT IN (SELECT id FROM table_b WHERE id IS NOT NULL);

Well, it works fine. Why do I need a NOT NULL on my subquery???

Thanks,

John. Received on Fri Jul 11 2008 - 21:51:20 CDT