OraFAQ Forum: Performance Tuning » Replace Outer Join with Unions

Home » RDBMS Server » Performance Tuning » Replace Outer Join with Unions

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Replace Outer Join with Unions [message #109124]

Tue, 22 February 2005 05:09

superbob
Messages: 2
Registered: February 2005

Junior Member

Hello All,

I need help with replacing an outer join with an union all in a view. How can i replace the following snippet:

SELECT *
FROM A_A, B_B
WHERE A_A_DBG = B_B_DBG (+)
AND A_A_AKL = B_B_AKL (+)
AND A_A_KLG = B_B_KLG (+)
AND A_A_BLG = B_B_BLG (+)

Thanks,
Bob

Report message to a moderator

Re: Replace Outer Join with Unions [message #109131 is a reply to message #109124]

Tue, 22 February 2005 08:00

Master.Richard
Messages: 15
Registered: February 2005

Junior Member

Hi, Bob

I'll give u an example. u can try it.

CREATE TABLE TB1(A INT,B INT);
CREATE TABLE TB2(A INT,B INT);

SQL817> SELECT * FROM TB1;

         A          B
---------- ----------
         3          3
         1          1
         2          4

SQL817> SELECT * FROM TB2;

         A          B
---------- ----------
         2          2
         4          4
         3          3

SQL817> SELECT TB1.A,TB1.B,TO_NUMBER(NULL) A,TO_NUMBER(NULL) B
  2    FROM TB1
  3    WHERE (A,B) NOT IN (SELECT A,B FROM TB2)
  4  UNION ALL
  5  SELECT TB1.A,TB1.B,TB2.A,TB2.B
  6    FROM TB1,TB2
  7    WHERE TB1.A=TB2.A AND TB1.B=TB2.B;

         A          B          A          B
---------- ---------- ---------- ----------
         1          1
         2          4
         3          3          3          3

good luck

[Updated on: Tue, 22 February 2005 08:18]

Report message to a moderator

Previous Topic:	Using Consumer groups
Next Topic:	Pass parameter to DML or new procedure?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Mar 06 11:19:56 CST 2025