Re: Why does this query do this?

From: Michael D O'Shea <michael.oshea_at_tessella.com>
Date: Sat, 12 Jul 2008 05:17:42 -0700 (PDT)
Message-ID: <acafaabb-9bb4-4419-a4b4-c0160303d4ad@k13g2000hse.googlegroups.com>

On Jul 12, 3:51 am, 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.

Hi John, NULL is unknown, not a value, and treated as such. Thus the observation in your post.

The following tutorial type article will help you understanding the difference with examples, http://articles.techrepublic.com.com/5100-22_11-5319615.html

For your simple query, you might also wish to consider the common set operations, eg.

SELECT id FROM table_a MINUS SELECT id FROM table_b

hth

Mike

TESSELLA Michael.OShea_at_tessella.com

__/__/__/  Tessella Support Services plc
__/__/__/  3 Vineyard Chambers, ABINGDON, OX14 3PX, England
__/__/__/  Tel: (44)(0)1235-555511  Fax: (44)(0)1235-553301

www.tessella.com Registered in England No. 1466429 Received on Sat Jul 12 2008 - 07:17:42 CDT