postgresql - SQL Join instead of UNION

Question

I have the following tables:

Table T1
--------------------------
ZID   | NAME 
------------------------
1     | Test 

Table C1 
--------------------------
ZID   | VALUE1 
------------------------
1     | A 
1     | B

Table S1 
--------------------------
ZID   | VALUE2 
------------------------
1     | C 
1     | D

I would like a select query to fetch the values below

----------------------------------
ZID   | NAME  | VALUE1  | VALUE2  
----------------------------------
1     | Test  |    A    |   null 
1     | Test  |    B    |   null 
1     | Test  |   null  |    C 
1     | Test  |   null  |    D

I am able to achieve the above by using a UNION by faking a column in each select as below.

SELECT
    ZID,
    NAME,
    VALUE1,
    NULL AS VALUE2
FROM
    T1
    LEFT JOIN C1 ON (
        T1.ZID = C1.ZID
    )
UNION ALL 
(
SELECT
    ZID,
    NAME,
    NULL AS VALUE1,
    VALUE2
FROM
    T1
    LEFT JOIN S1 ON (
        T1.ZID = S1.ZID
    )
);

Would it be possible to retrieve data as above in a single select using JOINS? I have tried using left join and however, I end up with a Cartesian product of data in the results.

question from:https://stackoverflow.com/questions/65928133/sql-join-instead-of-union

Answer 1

You could use:

select t1.*, c1.value1, s1.value2
from c1 full join
     s1
     on false join
     t1
     on t1.id in (c1.id, s1.id)

A full join on false is a lot like union all.