By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3798840 fiddles created (41830 in the last week).
CREATE TABLE t1 (a INT, b INT, c INT);
CREATE TABLE t2 (a INT, b INT, c INT);
INSERT INTO t1 VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9), (13,14,NULL);
INSERT INTO t2 VALUES (4, 5, 6), (7, 8, 9), (10, 11, 12),(13,14,NULL);
✓
✓
4 rows affected
4 rows affected
hidden batch(es)
(TABLE t1 EXCEPT TABLE t2)
UNION
(TABLE t2 EXCEPT TABLE t1)
ORDER BY a, b, c
a
b
c
1
2
3
10
11
12
…
hidden batch(es)
SELECT *
FROM (
SELECT 't1' AS t1, t1.* FROM t1
) t1 NATURAL FULL JOIN (
SELECT 't2' AS t2, t2.* FROM t2
) t2
WHERE NOT (t1, t2) IS NOT NULL;
a
b
c
t1
t2
1
2
3
t1
13
14
t1
10
11
12
t2
13
14
t2
…
hidden batch(es)
SELECT
coalesce(t1.a, t2.a) AS a,
coalesce(t1.b, t2.b) AS b,
coalesce(t1.c, t2.c) AS c,
t1.t1,
t2.t2
FROM (
SELECT 't1' AS t1, t1.* FROM t1
) t1 FULL JOIN (
SELECT 't2' AS t2, t2.* FROM t2
) t2 ON (t1.a, t1.b, t1.c) IS NOT DISTINCT FROM (t2.a, t2.b, t2.c)
WHERE NOT (t1, t2) IS NOT NULL;
ERROR: FULL JOIN is only supported with merge-joinable or hash-joinable join conditions