COL1 COL2 ---------- ---------- a aa a ab a ac a ad
SQL> select * from t_b;
COL1 COL2 ---------- ---------- a aa a aa a bb a ac a cc
SQL> SQL> select decode(a.col1, null, b.col1, a.col1), 2 decode(a.col2, null, b.col2, a.col2), 3 count(b.col1) 4 from t_a a 5 full join t_b b on a.col1 = b.col1 6 and a.col2 = b.col2 7 group by decode(a.col1, null, b.col1, a.col1), 8 decode(a.col2, null, b.col2, a.col2);
DECODE(A.COL1,NULL,B.COL1,A.CO DECODE(A.COL2,NULL,B.COL2,A.CO COUNT(B.COL1) ------------------------------ ------------------------------ ------------- a ac 1 a ab 0 a ad 0 a cc 1 a aa 2 a bb 1
SQL> create table t_a
2 (col1 varchar(10),
3 col2 varchar(10));
Table created
SQL>
SQL> create table t_b
2 (col1 varchar(10),
3 col2 varchar(10));
Table created
SQL> select * from t_a;
COL1 COL2
---------- ----------
a aa
a ab
a ac
a ad
SQL> select * from t_b;
COL1 COL2
---------- ----------
a aa
a aa
a bb
a ac
a cc
SQL>
SQL> select decode(a.col1, null, b.col1, a.col1),
2 decode(a.col2, null, b.col2, a.col2),
3 count(b.col1)
4 from t_a a
5 full join t_b b on a.col1 = b.col1
6 and a.col2 = b.col2
7 group by decode(a.col1, null, b.col1, a.col1),
8 decode(a.col2, null, b.col2, a.col2);
DECODE(A.COL1,NULL,B.COL1,A.CO DECODE(A.COL2,NULL,B.COL2,A.CO COUNT(B.COL1)
------------------------------ ------------------------------ -------------
a ac 1
a ab 0
a ad 0
a cc 1
a aa 2
a bb 1
6 rows selected
SQL>