Re: Why does this query do this?
Date: Wed, 16 Jul 2008 05:48:18 -0700 (PDT)
Message-ID: <d31a8be2-da55-4ccc-bdd8-37219595687b@p25g2000hsf.googlegroups.com>
On Jul 11, 10:51 pm, Mtek <m..._at_mtekusa.com> wrote:
> 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.
Another thing I have found is that "NOT IN" performs like a pig and I
have yet to see a "NOT IN" that couldn't be expressed as a "MINUS" and
get much better performance as a result. So "WHERE id NOT IN (SELECT
id FROM table_b)" can be expressed as
"WHERE id IN (SELECT id FROM table_a MINUS SELECT id FROM table_b)"
which will perform vastly better than using "NOT IN".
Received on Wed Jul 16 2008 - 07:48:18 CDT