Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: MINUS or a Nested Select

Re: MINUS or a Nested Select

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 2 Feb 2005 08:10:50 -0800
Message-ID: <117360650.00003e18.092@drn.newsguy.com>


In article <1107357476.213517.228920_at_l41g2000cwc.googlegroups.com>, Thiko! says...
>
>Hi
>
>How comes this works:
>
>SELECT COUNT(IndexID) FROM [Table1]
>MINUS
>SELECT COUNT(IndexID) FROM [Table2];
>
>But this doesnt:
>
>SELECT COUNT(IndexID) FROM [Table1]
>WHERE IndexID NOT IN (SELECT IndexID FROM [Table2]);
>
>I would have thought that they do the same thing??
>Many thanks
>
>Thiko!
>

wrong for a couple of reasons.

the second query counts the number of non-null indexid values in table1 that are verifiably not in the select from table2.

I said verifiably :)

first, if t1 has indexId = 1 and t2 has indexId = 2 and that is all -- then count(indexid) from t1 is 1 and count(indexId) from t2 is 1 -- so the first query says "the same"

the second query however would say "not the same" -- because 1 is not in (2).

The first query simply compare the not null counts of indexid from t1 to t2.

the second query counts rows in t1 that are verifiably not in t2 (there is that word again....)

Consider:

ops$tkyte_at_ORA10GR1> create table t1 ( x int ); Table created.  

ops$tkyte_at_ORA10GR1> create table t2 ( x int ); Table created.  

ops$tkyte_at_ORA10GR1> insert into t1 values ( 1 ); 1 row created.  

ops$tkyte_at_ORA10GR1> insert into t1 values ( null ); 1 row created.  

ops$tkyte_at_ORA10GR1> insert into t2 values ( 2 ); 1 row created.  

ops$tkyte_at_ORA10GR1> insert into t2 values ( null ); 1 row created.  

ops$tkyte_at_ORA10GR1> select count(x) from t1;  

  COUNT(X)


         1

ONE, not TWO, one -- nulls are not counted when you use count(<expression>)  

ops$tkyte_at_ORA10GR1> select count(x) from t2;  

  COUNT(X)


         1

Your minus would say "the same" (even though they are not)  

ops$tkyte_at_ORA10GR1> select * from t1 where x not in ( select x from t2 );  

no rows selected

Since t2 contains NULL, it is 'not known' if x is not in t2!  

ops$tkyte_at_ORA10GR1> select * from t1 where x not in ( select x from t2 where x is not null );  

         X


         1  

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation
Received on Wed Feb 02 2005 - 10:10:50 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US