Re: odd sub-select problem

In article <12bb17fa.0110291011.32821eff_at_posting.google.com>, usenet_at_schmolze.com says...
>
>I'm trying to do something which I think is very simple. I have a
>table, A, which contains about 13000 rows, and a table B which
>contains about 1600 rows.
>
>Both these tables have a NAME column in the same domain. I want to
>display the NAMEs in A which are not in B. To do this, I'm using the
>following query:
>
>SELECT NAME FROM A
>WHERE NAME NOT IN (SELECT NAME FROM A)
>
>This returns no rows. If, however, I do the following:
>
>SELECT NAME FROM A
>WHERE NAME IN (SELECT NAME FROM A)
>
>(In other words, show me all the names in A that *are* in B)
>
>I get about 1500 rows, as expected. Can anyone shed some light on
>this?
>
>Thanks.

assuming you really meant "from B" in the subqueries -- I'll guess you have NULLS in the table B.

see
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:442029737684

if you do a NOT IN ( subquery ) and the subquery returns a NULL - you are assured of getting 0 rows back.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp