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 [EMAIL PROTECTED] 214.954.1781 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 > [EMAIL PROTECTED] > 214.954.1781 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Larry Elkins INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
