Home » RDBMS Server » Performance Tuning » Replace Outer Join with Unions
icon3.gif  Replace Outer Join with Unions [message #109124] Tue, 22 February 2005 05:09 Go to next message
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
Re: Replace Outer Join with Unions [message #109131 is a reply to message #109124] Tue, 22 February 2005 08:00 Go to previous message
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:
  


Current Time: Fri Mar 29 06:22:51 CDT 2024