FULL Outer join Oracle

Question

I have the below data set from different table

Table1
-------
EMPID    DEPID   C1  C2  SUM
---------------------------
100      200     2    3   111
100      200     4    4   222

 Table2
-------
EMPID    DEPID   C1  C2  SUM
---------------------------
100      200     4    4   333
100      200     5    6   444

Result I am looking for ---

Table1
-------
EMPID    DEPID   T1.C1  T1.C2  T2.C1   T2.C2    T1.SUM   T2.SUM
--------------------------------------------------------------
100      200       2      3     NULL    NULL     111      NULL
100      200       4      4      4        4      222      333
100      200       NULL   NULL   5        6      NULL     444

Any help is much appriciated. I used Full Outer Join but could not achive this.

Answer 1

You want an INNER JOIN on (emp_id, dep_id) with a FULL OUTER JOIN on (c1, c2). I think you won't be able to write this join condition in a single step. I propose a two-step query (a FULL OUTER JOIN followed by a filter subquery).

SQL> WITH t1 AS (
  2  SELECT 100 emp_id, 200 dep_id, 2 c1, 3 c2, 111 sum FROM dual UNION ALL
  3  SELECT 100 emp_id, 200 dep_id, 4 c1, 4 c2, 222 sum FROM dual
  4  ), t2 AS (
  5  SELECT 100 emp_id, 200 dep_id, 4 c1, 4 c2, 333 sum FROM dual UNION ALL
  6  SELECT 100 emp_id, 200 dep_id, 5 c1, 6 c2, 444 sum FROM dual UNION ALL
  7  SELECT 100 emp_id, 300 dep_id, 7 c1, 8 c2, 555 sum FROM dual
  8  )
  9  SELECT COALESCE(t1.emp_id, t2.emp_id) emp_id,
 10         COALESCE(t1.dep_id, t2.dep_id) dep_id,
 11         t1.c1, t1.c2, t2.c1, t2.c2, t1.sum, t2.sum
 12    FROM t1
 13    FULL OUTER JOIN t2
 14      ON t1.emp_id = t2.emp_id
 15     AND t1.dep_id = t2.dep_id
 16     AND t1.c1 = t2.c1
 17     AND t1.c2 = t2.c2
 18   WHERE (COALESCE(t1.emp_id, t2.emp_id), COALESCE(t1.dep_id, t2.dep_id))
 19         IN (SELECT t1.emp_id, t1.dep_id
 20               FROM t1
 21               JOIN t2 ON t1.emp_id = t2.emp_id
 22                      AND t1.dep_id = t2.dep_id)
 23  ORDER BY t1.c1;

    EMP_ID     DEP_ID         C1         C2         C1         C2  SUM  SUM
---------- ---------- ---------- ---------- ---------- ---------- ---- ----
       100        200          2          3                        111 
       100        200          4          4          4          4  222  333
       100        200                                5          6       444

Note that I added a row in T2 from your example to demonstrate the INNER JOIN.