Full joining of a same table on the same column without the need for UNION

Question

First of all, the title of the question sucks... I hope the description below will clarify the situation.

The problem is as follows; consider this table:

id    part1    part2    v
-------------------------
id1   p1       p2       v1old
id1   p3       p4       v2
id2   p1       p2       v1new
id2   p5       p6       v3

Now, I am given two values of the id column; let's say id1 and id2 from the sample data above. The result I seek is:

part1    part2    vold    vnew
------------------------------
p1       p2       v1old   v1new
p3       p4       v2      NULL
p5       p6       NULL    v3

After some investigation and the help of this question, I could build this query which does the trick:

select t1.part1, t1.part2, t1.v as vold, t2.v as vnew
    from (select part1, part2, v from t where id = 1) t1
    left join (select part1, part2, v from t where id = 2) t2
    on t1.part1 = t2.part1 and t1.part2 = t2.part2
union
select t2.part1, t2.part2, t1.v as vold, t2.v as vnew
    from (select part1, part2, v from t where id = 1) t1
    right join (select part1, part2, v from t where id = 2) t2
    on t1.part1 = t2.part1 and t1.part2 = t2.part2
;

The problem here is duplicates which union will happily get rid of... But there are a lot of them to remove. Is there a version which can avoid generating those duplicates?

Gordon Linoff · Answer 1 · 2016-08-05T16:17:28.857

0

I think this is what you want:

select coalesce(t.part1, t2.part1) as part1,
       coalesce(t.part2, t2.part2) as part2,
       t.v as vold, t2.v as vnew
from this t full join
     this t2
     on t.part1 = t2.part1 and t.part2 = t2.part1
where (t.id = $id1 or t.id is null) and
      (t2.id = $id2 or t2.id is null)

edited Aug 05 '16 at 16:17

answered Aug 05 '16 at 14:54

Gordon Linoff

1,242,037
58
646
786

I am not sure what you coalesce for here? – fge Aug 05 '16 at 15:02
`full join` returns `NULL` values in key columns when the rows don't match. – Gordon Linoff Aug 05 '16 at 16:17

score 0 · Answer 2 · answered Aug 06 '16 at 13:59

OK, I found a solution via another route:

select t1.*, t2.value as vold, t3.value as vnew
from (select distinct part1, part2 from t where id in (1, 2)) t1
left join t t2 on t2.id = 1 and t2.part1 = t1.part1 and t2.part2 = t1.part2
left join t t3 on t3.id = 2 and t3.part1 = t1.part1 and t3.part2 = t1.part2
;

Full joining of a same table on the same column without the need for UNION

2 Answers2