By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE AAA(NUM,TXT) AS
SELECT 1, 'One' FROM DUAL UNION ALL
SELECT 2, 'too' FROM DUAL UNION ALL
SELECT 3, 'Three' FROM DUAL UNION ALL
SELECT 4, 'Four' FROM DUAL;
4 rows affected
CREATE TABLE BBB(NUM,TXT) AS
SELECT 1, 'One' FROM DUAL UNION ALL
SELECT 3, NULL FROM DUAL UNION ALL
SELECT 4, 'Four' FROM DUAL UNION ALL
SELECT 2, 'Two' FROM DUAL UNION ALL
SELECT 5, 'Five' FROM DUAL;
5 rows affected
SELECT CASE
WHEN a.num IS NOT NULL
THEN 'only A'
ELSE 'only B'
END AS what,
COALESCE(a.num, b.num) AS num,
CASE
WHEN a.num IS NOT NULL
THEN a.txt
ELSE b.txt
END AS txt
FROM aaa a
FULL OUTER JOIN bbb b
ON a.num = b.num AND a.txt = b.txt
WHERE a.num IS NULL
OR b.num IS NULL
ORDER BY num, what
| WHAT | NUM | TXT |
|---|---|---|
| only A | 2 | too |
| only B | 2 | Two |
| only A | 3 | Three |
| only B | 3 | null |
| only B | 5 | Five |