Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: MINUS or a Nested Select
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 CorporationReceived on Wed Feb 02 2005 - 10:10:50 CST