Re: SELECT .... NOT IN (SELECT ....) Oracle 8.1.7 without resultset

In article <9pkdt4$iopmi$1_at_ID-71421.news.dfncis.de>, "Andre says...
>
>hi,
>
>the following statement should return only rows returned by the first query
>but not by the second:
>
>SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT WHERE TRIM(LOWER(TT.EMAIL)) NOT
>IN
>
>(SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
>KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)
>
>The resultset of this query is empty!!!
>

you've got nulls in the subquery i'll bet.

consider:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t1 ( x int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 2 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t1 values ( 1 );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t where x not in ( select x from t1 );

         X

         2

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t1 values ( null );

1 row created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t where x not in ( select x from t1 );

no rows selected

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from t where x not in ( select x from t1 where x is NOT NULL );

         X

         2

>Exactly the same two queries combined with MINUS:
>
>SELECT DISTINCT(TT.EMAIL) FROM TBL_TEST TT
>
>MINUS
>
>(SELECT DISTINCT(TRIM(LOWER(kp.emailname))) AS mailadress FROM
>KONTAKTPERSONEN kp WHERE kp.emailname IS NOT NULL)
>
>...now it returns 752 rows which are only in the first query!
>
>what iam doing wrong? i tried everything!
>
>thanks...
>
>andre
>
>
>
>
>
>
>
>
>

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