By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE a (id, name, value, pid) AS
SELECT 1, 'a', 55, 27 FROM DUAL UNION ALL
SELECT 2, 'b', 56, 23 FROM DUAL UNION ALL
SELECT 3, 'c', 57, 22 FROM DUAL;
3 rows affected
CREATE TABLE b (id, name, value, pid) AS
SELECT 1, 'a', 55, 29 FROM DUAL UNION ALL
SELECT 5, 'd', 58, 23 FROM DUAL UNION ALL
SELECT 6, 'e', 59, 22 FROM DUAL;
3 rows affected
SELECT id,
value,
name,
pid
FROM (
SELECT id,
value,
name,
pid,
ROW_NUMBER() OVER (PARTITION BY id, value, name ORDER BY priority) AS rn
FROM (
SELECT id, value, name, pid, 1 AS priority FROM a UNION ALL
SELECT id, value, name, pid, 2 AS priority FROM b
)
)
WHERE rn = 1;
| ID | VALUE | NAME | PID |
|---|---|---|---|
| 1 | 55 | a | 27 |
| 2 | 56 | b | 23 |
| 3 | 57 | c | 22 |
| 5 | 58 | d | 23 |
| 6 | 59 | e | 22 |