By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH TAB_1 AS (
SELECT 1 AS KID_ID, 'RED' AS SHIRT_COLOR, 'BROWN' AS PANT_COLOR FROM dual UNION ALL
SELECT 2, 'BLUE', 'BROWN' FROM dual UNION ALL
SELECT 3, 'RED', 'BLACK' FROM dual UNION ALL
SELECT 4, 'BLUE', 'BLACK' FROM dual UNION ALL
SELECT 5, 'YELLOW', 'BROWN' FROM dual UNION ALL
SELECT 6, 'ORANGE', 'BLACK' FROM dual
),
TAB_2 AS (
SELECT 1 AS KID_ID, 'RED' AS SHIRT_COLOR, 'BROWN' AS PANT_COLOR FROM dual UNION ALL
SELECT 2, 'BLUE', 'BROWN' FROM dual UNION ALL
SELECT 3, 'RED', 'BLACK' FROM dual UNION ALL
SELECT 4, 'BLUE', 'BLACK' FROM dual UNION ALL
SELECT 5, 'YELLOW', 'BROWN' FROM dual UNION ALL
SELECT 6, 'GREY', 'BROWN' FROM dual
)
SELECT
COALESCE(t1.SHIRT_COLOR, t2.SHIRT_COLOR) AS SHIRT_COLOR,
COALESCE(t1.PANT_COLOR, t2.PANT_COLOR) AS PANT_COLOR,
CASE WHEN t2.KID_ID IS NULL THEN 'TAB_1' ELSE 'TAB_2' END AS TABLE_SOURCE
FROM TAB_1 t1
FULL OUTER JOIN TAB_2 t2
ON t1.SHIRT_COLOR = t2.SHIRT_COLOR AND
t1.PANT_COLOR = t2.PANT_COLOR
WHERE
t1.KID_ID IS NULL OR
t2.KID_ID IS NULL;
| SHIRT_COLOR | PANT_COLOR | TABLE_SOURCE |
|---|---|---|
| GREY | BROWN | TAB_2 |
| ORANGE | BLACK | TAB_1 |