By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table#1 ( c1, c2, c3 )as
select 111111, 200, 14 from dual union all
select 111111,-200, 14 from dual union all
select 111111, 400, 15 from dual union all
select 111111, -400, 15 from dual ;
4 rows affected
create table table#2 ( c1, c2, c3 ) as
select 111111, 201, 14 from dual union all
select 111111,-200, 14 from dual union all
select 111111, 400, 15 from dual union all
select 111111, -400, 15 from dual ;
4 rows affected
(
select c1, c2, c3, 'in table#1' location from table#1
minus
select c1, c2, c3, 'in table#1' from table#2
)
union all
(
select c1, c2, c3, 'in table#2 (not in table#1)' from table#2
minus
select c1, c2, c3, 'in table#2 (not in table#1)' from table#1
);
| C1 | C2 | C3 | LOCATION |
|---|---|---|---|
| 111111 | 200 | 14 | in table#1 |
| 111111 | 201 | 14 | in table#2 (not in table#1) |
select
A.c1
--, B.c1
, A.c2
, B.c2
, A.c3
--, B.c3
from
(
select * from table#1
minus
select * from table#2
) A join (
select * from table#2
minus
select * from table#1
) B on A.c1 = B.c1 and A.c3 = B.c3
;
| C1 | C2 | C2 | C3 |
|---|---|---|---|
| 111111 | 200 | 201 | 14 |