RE: Strange behavior

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

> -----Original Message-----
> From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On 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: StephenAndert_at_firsthealth.com
>
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Bobak, Mark
> INET: Mark.Bobak_at_il.proquest.com
>
> 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).
>

-- 
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).