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 > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of > Bobak, Mark > Sent: Wednesday, January 07, 2004 2:09 PM > To: Multiple recipients of list ORACLE-L > Subject: RE: Strange behavior > > > Stephen, > > Do any of your sub-selects (the queries inside the IN() clause) return > NULL? If so, that will definitely cause zero rows to be returned by the > outer query. > > -Mark > > 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 2:49 PM > To: Multiple recipients of list ORACLE-L > > > Greetings fellow-DBA-folk: > > When I run several queries, I am getting very strange results. > Selecting * from apple returns the expected data records, but count(*) > does not. > Selecting anything other than * from apple says no records. > The subquery for pear works fine on it's own. > Selecting anything other than * from apple returns the expected records > when about 900+ values are placed literally in the subquery as shown in > example 4 below. > > Am I missing something? Anyone have a direction for me to look for the > cause of this? > > Thanks > Stephen > > EX 1 > select rowid from common.apple > where appleinter1 in (select pearinternal from common.pear where > pear_clnt_src_cd = 'CCN') > and appletermd is null > and rownum < 10 ; > > no rows selected > > EX 2 > select rownum from common.apple > where appleinter1 in (select pearinternal from common.pear where > pear_clnt_src_cd = 'CCN') > and appletermd is null > and rownum < 10 ; > > no rows selected > > EX 3 > select * from common.apple > where appleinter1 in (select pearinternal from common.pear where > pear_clnt_src_cd = 'CCN') > and appletermd is null > and rownum < 10 ; > > --- 9 rows returned, I removed them for confidentiality reasons --- > > EX 4 > select count(*) from common.apple > where appleinter1 in ( 7009 , 7010 , 7011 , 7012 , 7013 , 7014 , 7015 , > 7016 , 7017 , 7018 , 7019) > and appletermd is null > and rownum < 10 ; > > --- 9 rows returned, I removed them for confidentiality reasons --- > > EX 5 > select count(*) from common.apple > where appleinter1 in (select pearinternal from common.pear where > pear_clnt_src_cd = 'CCN') > and appletermd is null; > > COUNT(*) > ------------ > 0 > > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Stephen Andert > 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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Bobak, Mark > 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). > -- 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bobak, Mark 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).