Re: Misbehaving query

No problem. You are trying to create a cartesian product of the two queries. Now, what is a Cartesian product of the two sets, A and B? It is a set of all ordered pairs (a,b) where a is element of A and b is element of B. The result of your firs query is an ampty set:

SQL> select * from junk minus select * from junk2 ;  

no rows selected

Cartesian product of an empty set with anything is an empty set itself. A little bit set theory saves the day. If whe insert 'Y' into JUNK, everything works as advertised:

  1 select a.*, b.*
  2 from
  3 ( select * from junk minus select * from junk2 ) a,   4* ( select * from junk2 minus select * from junk ) b SQL> /   T T
- -
Y X

On 12/12/2003 03:04:33 PM, "Bellow, Bambi" wrote:
> Hi!
>
> I have a query that I think is behaving oddly; and, it may just be that I'm
> blind and am doing something silly (*there's* a first!), or it may be
> environment specific, but, I'm thinkin it may just be a bug. I have filed a
> TAR with Oracle, and they keep sending workarounds, when I told them from
> the start that I've got one, this is only a question of why this query isn't
> working. So, two parts, really... is this query really not working, and if
> so, does anyone have a clue as to why?
>
> We're on 9.2.0.2 on SunOS. The overall issue is easy. I have two tables.
> I want to find the difference in the contents. This is easily accomplished
> by doing
>
> A MINUS B
> UNION
> B MINUS A
>
> *That's* not the issue. The issue is that if I do this through inlines, the
> query fails. I'll put everything out there so you can just cut and paste
> iffen you wanna...
>
> SQL> create table junk (test char(1));
>
> Table created.
>
> SQL> insert into junk values ('A');
>
> 1 row created.
>
> SQL> insert into junk values ('B');
>
> 1 row created.
>
> SQL> insert into junk values ('C');
>
> 1 row created.
>
> SQL> create table junk2 as select * from junk;
>
> Table created.
>
> SQL> insert into junk2 values ('X');
>
> 1 row created.
>
> SQL> select * from junk;
>
> T
> -
> A
> B
> C
>
> SQL> select * from junk2;
>
> T
> -
> A
> B
> C
> X
>
> SQL> select * from junk minus select * from junk2
> 2 union
> 3 select * from junk2 minus select * from junk;
>
> T
> -
> X
>
> SQL> select a.*
> 2 from
> 3 ( select * from junk2 minus select * from junk ) a;
>
> T
> -
> X
>
> SQL> select a.*, b.*
> 2 from
> 3 ( select * from junk2 minus select * from junk ) a,
> 4 ( select * from junk2 minus select * from junk ) b;
>
> T T
> - -
> X X
>
> SQL> select a.*, b.*
> 2 from
> 3 ( select * from junk minus select * from junk2 ) a,
> 4 ( select * from junk2 minus select * from junk ) b;
>
> no rows selected
>
>
> Anyone?
> Bambi.
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Bellow, Bambi
> INET: bbellow_at_chi.navtech.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).
>

Mladen Gogala
Oracle DBA

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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).