PostgrSQL UNION by key-CodePudding

Can't come up with easy solution how to union tables with same columns but from second table add only rows which are present in first table.

t1:

id	A	B	C
1	xx	r	g
2	cc	r	g
5	d	g	e

t2:

id	A	B	C
101	jyj	u	j
5	y	jku	u
12	y	r	j

desired t1 union t2:

id	A	B	C
1	xx	r	g
2	cc	r	g
5	d	g	e
5	y	jku	u

Appreciate any help.

CodePudding user response：

We can use an IN clause and say the id of table 2 must appear in table 1:

SELECT id, A, B, C
FROM t1
UNION ALL
SELECT id, A, B, C
FROM t2
WHERE t2.id IN (SELECT id FROM t1);

Try out: db<>fiddle

CodePudding user response：

Something like:

SELECT id, a, b, c
from t1
union all
SELECT id, a, b, c
from t2
where exists (select *
              from t1
              where t1.id = t2.id);

CodePudding user response：

We can try the following union approach with the help of window functions:

WITH cte AS (
    SELECT id, A, B, C, 1 AS src FROM t1
    UNION ALL
    SELECT id, A, B, C, 2 FROM t2
),
cte2 AS (
    SELECT *, MIN(src) OVER (PARTITION BY id) AS min_src,
              MAX(src) OVER (PARTITION BY id) AS max_src
    FROM cte
)

SELECT id, A, B, C
FROM cte2
WHERE src = 1 OR min_src <> max_src
ORDER BY id, src;

CodePudding user response：

Something like this:

SELECT t1.id, t1.A, t1.B, t1.C
FROM t1
FULL OUTER JOIN t2 ON t1.id = t2.id
WHERE t1.id IS NOT NULL