Home » SQL & PL/SQL » SQL & PL/SQL » minus query (9i)
minus query [message #312325] Tue, 08 April 2008 10:13 Go to next message
j0zele
Messages: 15
Registered: December 2006
Junior Member
Hi,

i've a problem with a minus select in my hands,

the query is like that:

SELECT a1, a2
from (
      select a1,a2 from table_1
      minus
      select a1,a2 from table_2);


In this query i need to get out 2 more champs (a3, a4) from table_1 and table_2 doesnt have this champs, tables are too heavy and i dont want to do another join with table_1

Thanks!!!

[Updated on: Tue, 08 April 2008 10:35]

Report message to a moderator

Re: minus query [message #312331 is a reply to message #312325] Tue, 08 April 2008 10:36 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Define your terms:

a) what on earth is a champ ?
b) what is a table that is too "heavy" ?
c) why do you think you need another join ?
Re: minus query [message #312336 is a reply to message #312331] Tue, 08 April 2008 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"champ" is French word for "field".

I add d) why do you have an outer query? The inner one is complete.

Do you say you want to display 2 fields (a3,a4) that are not in the tables? So what are they?

Regards
Michel
Re: minus query [message #312339 is a reply to message #312325] Tue, 08 April 2008 10:47 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Sorry, I've been away from the forum for a while. I didn't realize the official language had been changed to franglais.
Re: minus query [message #312344 is a reply to message #312336] Tue, 08 April 2008 10:51 Go to previous messageGo to next message
j0zele
Messages: 15
Registered: December 2006
Junior Member
fields a3, a4 are in table_1, but not in table_2

we have added the outer query in order to prepare the query to extract fields a3, a4

a heavy table... more than 500,000 rows each table (around 1,000,000... or more)
Re: minus query [message #312345 is a reply to message #312344] Tue, 08 April 2008 10:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your tables definitions.
Post what you wanted from these tables.
Post what you already tried.

Read forum guide to know how to post and format.

Regards
Michel
Re: minus query [message #312350 is a reply to message #312345] Tue, 08 April 2008 11:16 Go to previous messageGo to next message
j0zele
Messages: 15
Registered: December 2006
Junior Member
create table table_1 (
a1 varchar2(30),
a2 varchar2(30),
a3 number(4),
a4 number(4));

create table table_2 (
a1 varchar2(30),
a2 varchar2(30));


I need to select all rows from table_1 not existing in table_2, but i need to extract fields a3 and a4 in the result without making another join with table_1...

This is what i dont want to do:
SELECT res.a1, res.a2, t1.a3, t1.a4
(select a1,a2 from table_1
minus
select a1,a2 from table_2) res, table_1 t1
where res.a1=t1.a1;




Re: minus query [message #312356 is a reply to message #312350] Tue, 08 April 2008 11:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SELECT t1.a1, t1.a2, t1.a3, t1.a4
from table_1 t1
where (a1,a2) in 
(select a1,a2 from table_1
minus
select a1,a2 from table_2);

Regards
Michel
Re: minus query [message #312383 is a reply to message #312350] Tue, 08 April 2008 13:28 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am not sure whether I am missing something here. It looks to me more like a anti join.
Anti Join version
SQL> with t
  2  as
  3  (select 1 id1, 1 id2, 1 id3, 1 id4 from dual union all
  4   select 1, 2, 2, 2 from dual union all
  5   select 1, 3, 3, 3  from dual union all
  6   select 2, 1, 1, 1 from dual union all
  7   select 2, 2, 2, 2 from dual union all
  8   select 2, 3, 3, 3 from dual union all
 select 3, 1, 4, 4 from dual union all
  9   10   select 3, 1, 5, 5 from dual
 11  ),
 12  t1
 13  as
 14  (select 1 id1, 2 id2 from dual union all
 15   select 1, 1 from dual union all
 select 3, 1 from dual
 16   17  )
 18  select t.* from t, t1
 19  where
 20  t.id1 = t1.id1 (+)
 21  and
 22  t.id2 = t1.id2 (+)
 23  and
 24  t1.id1 is null;


       ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
         2          3          3          3
         2          1          1          1
         2          2          2          2
         1          3          3          3

Minus version
SQL> SQL> with t
  2  as
  3  (select 1 id1, 1 id2, 1 id3, 1 id4 from dual union all
  4   select 1, 2, 2, 2 from dual union all
  5   select 1, 3, 3, 3  from dual union all
  6   select 2, 1, 1, 1 from dual union all
  7   select 2, 2, 2, 2 from dual union all
  8   select 2, 3, 3, 3 from dual union all
  9   select 3, 1, 4, 4 from dual union all
 10   select 3, 1, 5, 5 from dual
 11  ),
 12  t1
 13  as
 14  (select 1 id1, 2 id2 from dual union all
 15   select 1, 1 from dual union all
 16   select 3, 1 from dual
 17  )
select t.* from t, t1
 18   19  where
 20  t.id1 = t1.id1 (+)
 21  and
 22  t.id2 = t1.id2 (+)
 23  and
t1.id1 is null;
 24
       ID1        ID2        ID3        ID4
---------- ---------- ---------- ----------
         2          3          3          3
         2          1          1          1
         2          2          2          2
         1          3          3          3

Regards

Raj
Re: minus query [message #312447 is a reply to message #312383] Tue, 08 April 2008 21:24 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Or you could just us NOT IN

Ross Leishman
Re: minus query [message #312472 is a reply to message #312447] Wed, 09 April 2008 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Or you could just us NOT IN

Beware of NULLs.

Regards
Michel
Re: minus query [message #312526 is a reply to message #312325] Wed, 09 April 2008 02:41 Go to previous message
j0zele
Messages: 15
Registered: December 2006
Junior Member
Thanks to everybody
Previous Topic: Problem with VARRAYs
Next Topic: Merge command error
Goto Forum:
  


Current Time: Thu Dec 08 02:24:33 CST 2016

Total time taken to generate the page: 0.08750 seconds