Home » SQL & PL/SQL » SQL & PL/SQL » Full outer join (Oracle, 10g, Windows Server 2003)
Full outer join [message #444239] Fri, 19 February 2010 09:33 Go to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
Consider following scenario

Below is the table creation script


SQL> Create table t1(num1 number,rating varchar2(5));

Table created

SQL> Create table t2(num1 number,rating varchar2(5));

Table created



Data insertion script is shown below


SQL> Insert Into t1 Values(1000,'AB');

1 row inserted

SQL> Insert Into t1 Values(1000,'CD');

1 row inserted

SQL> Insert Into t2 Values(1000,'AB');

1 row inserted

SQL> Insert Into t2 Values(1000,'CD');

1 row inserted

SQL> Insert Into t1 Values(2000,'CD');

1 row inserted

SQL> Insert Into t2 Values(3000,'CD');

1 row inserted


I executed following query on sql developer


SQL> SELECT T1.NUM1 "T1.NUM1",T1.RATING "T1.RATING",T2.NUM1 "T2.NUM1",T2.RATING "T2.RATING"
FROM T1 FULL OUTER JOIN T2
ON (T1.NUM1 = T2.NUM1);


[b]T1.NUM1 T1.RATING T2.NUM1 T2.RATING[/b]   
      1000 CD           1000 AB
      1000 AB           1000 AB
      1000 CD           1000 CD
      1000 AB           1000 CD
      2000 CD           null null
      null null         3000 CD


My requirement is


[b]T1.NUM1 T1.RATING T2.NUM1 T2.RATING[/b]   
      1000 AB           1000 AB
      1000 CD           1000 CD
      2000 CD           null null
      null null         3000 CD


Is this possible, please help me on this



Regards,
Ritesh

[Updated on: Fri, 19 February 2010 09:39]

Report message to a moderator

Re: Full outer join [message #444240 is a reply to message #444239] Fri, 19 February 2010 09:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And your requirement is what, with words?
Obviously it is NOT a full outer join.

Regards
Michel
Re: Full outer join [message #444241 is a reply to message #444240] Fri, 19 February 2010 09:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Or maybe it is a full outer join on ALL columns:
SQL> SELECT T1.NUM1 "T1.NUM1",T1.RATING "T1.RATING",T2.NUM1 "T2.NUM1",T2.RATING "T2.RATING"
  2  FROM T1 FULL OUTER JOIN T2
  3  ON (T1.NUM1 = T2.NUM1 and T1.RATING=T2.RATING);
   T1.NUM1 T1.RA    T2.NUM1 T2.RA
---------- ----- ---------- -----
      1000 AB          1000 AB
      1000 CD          1000 CD
      2000 CD
                       3000 CD

4 rows selected.

Regards
Michel
Re: Full outer join [message #444245 is a reply to message #444239] Fri, 19 February 2010 09:56 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi Michel,
My requirement is that

Consider record for T1.NUM1=1000, output should contain only
two records for 1000 showing its Rating
from table T1 (AB and CD) and T2 (AB and CD).

For T1.NUM1=2000 output record is correct.

For T1.NUM1=3000 output record is correct.


Regards,
Ritesh

[Updated on: Fri, 19 February 2010 09:58]

Report message to a moderator

Re: Full outer join [message #444246 is a reply to message #444245] Fri, 19 February 2010 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And my previous answer does not fit this?

Regards
Michel
Re: Full outer join [message #444248 is a reply to message #444245] Fri, 19 February 2010 10:19 Go to previous messageGo to next message
getritesh
Messages: 235
Registered: July 2006
Location: INDIA
Senior Member

Hi,
Issue is solved,Thanks Michel

Regards,
Ritesh

[Updated on: Fri, 19 February 2010 10:20]

Report message to a moderator

Re: Full outer join [message #444385 is a reply to message #444239] Sun, 21 February 2010 06:02 Go to previous messageGo to next message
daizhicun
Messages: 6
Registered: February 2010
Location: china
Junior Member

when you use out join .
it well be very slowly.

you can see execute plan .
Re: Full outer join [message #444388 is a reply to message #444385] Sun, 21 February 2010 06:10 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe but what do you want to use instead that will be faster?

Regards
Michel
Previous Topic: Datatype Issue
Next Topic: dynamic collection set
Goto Forum:
  


Current Time: Sat Dec 10 20:36:19 CST 2016

Total time taken to generate the page: 0.12741 seconds