I have 3 table and need exclusive left join

Table_1:

A
B
C

Table_2:

A | 4
B | 5
B | 6
C | 7

Table_3:

A | 7
B | 8
C | 9

Using 2 left joins I got this:

A | 4 | 7
B | 5 | 8
B | 6 | 8
C | 7 | 9

But I do group over the first column and 8 is accumulated twice, I wish to get:

A | 4 | _
A | _ | 7
B | 5 | _
B | _ | 8
B | 6 | _
C | 7 | _
C | _ | 9

I also wish to avoid UNION ALL because my first table is actually a complex query, I wish to avoid its recalculation.

https://dbfiddle.uk/?rdbms=postgres_12&fiddle=d6012e281e3ba43487b1bfec85fd886c

Answer

I am not sure if that works without UNION ALL. For example, all answers here (SQL left join two tables independently) work with it.

So, my solution is:

demo:db<>fiddle

SELECT
    a.id,
    b.val,
    NULL
FROM
    a
LEFT JOIN b ON a.id = b.id

UNION ALL

SELECT
    a.id,
    NULL,
    c.val
FROM
    a
LEFT JOIN c ON a.id = c.id

ORDER BY 1,2,3

---

Edit: Found a way using GROUPING SETS:

demo:db<>fiddle

SELECT
    ab.*,
    c.val
FROM (
    SELECT
        a.id,
        b.val
    FROM a
    LEFT JOIN b ON a.id = b.id
    GROUP BY GROUPING SETS ((a.id), (a.id, b.val))
) ab
LEFT JOIN c ON ab.id = c.id AND ab.val IS NULL