RE: Strange behavior

The NULL's angle is the first reaction many of us have when we see the words "sub-query" and "strange results" or "no rows". I think I *finally* remember after all these years, but you bet I still did the quick test case before replying just in case I mis-remembered things ;-)

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of
> Bobak, Mark
> Sent: Wednesday, January 07, 2004 3:09 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Strange behavior
>
>
> D'oh!
>
> You're absolutely right, Larry. I don't know what I was thinking!
>
> Mark J. Bobak
> Oracle DBA
> ProQuest Company
> Ann Arbor, MI
> "Imagination was given to man to compensate him for what he is not, and
> a sense of humor was provided to console him for what he is." --Unknown
>
>
> -----Original Message-----
> Sent: Wednesday, January 07, 2004 3:40 PM
> To: Multiple recipients of list ORACLE-L
>
>
> That would apply to a NOT IN, but not IN. Try the following sample:
>
> select *
> from dept
> where deptno in (select deptno from emp union all select to_number(null)
> from dual)
>
> We still get the desired results of dept's 10, 20, and 30. Now in the
> case
> of NOT IN, you are correct about the impact of nulls. For example,
> compare
> the following two queries:
>
> select *
> from dept
> where deptno not in (select deptno from emp union all select
> to_number(null)
> from dual)
>
> select *
> from dept
> where deptno not in (select deptno from emp)
>
> The first query returns no rows because of the null included in the
> sub-query results. The second query returns deptno 40 as expected.
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net
> 214.954.1781

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).