CODEWITHSUNDEEP
sqlsql-serverjoin
Code Ninja
Code Ninja

Reputation: 765

Return all combinations including nulls

I think this question is very close to what I need but I've not been able to unpick it to give the data I require.

I have 4 sets of data:

TableA    TableB    TableC   TableD
1         10        20       34
2         15        21
3         16
          17

I want to be able to get every combination, including the blank rows, so my result would look like:

TableA_id  TableB_id  TableC_id  TableD_id
1          NULL       NULL       NULL
1          10         NULL       NULL
1          10         20         NULL
1          10         20         34
1          10         21         NULL
1          10         20         34
1          15         NULL       NULL
1          15         20         NULL
1          15         20         34
1          15         21         NULL
1          15         21         34
... but then to also include ...
NULL       10         NULL       NULL
NULL       10         20         NULL
NULL       10         20         34   
NULL       10         21         NULL
NULL       10         21         34   
...
NULL       NULL       NULL       34

Using the CROSS JOIN below would get me all the full combinations but to get the partials as well the only solution I can see is to UNION separate queries each getting me all the single/double/triple value rows the original.

SELECT TableA.id AS TableA_id, TableB.id AS TableB_id, TableC.id AS TableC_id, TableD.id AS TableD_id
FROM TableA CROSS JOIN
    TableB CROSS JOIN
    TableC CROSS JOIN
    TableD

Upvotes: 0

Views: 598

Answers (1)

forpas
forpas

Reputation: 164214

Use UNION ALL to add a row with a NULL value in each table and then CROSS JOIN all the queries:

select *
from (select id as id_a from tablea union all select null) a 
cross join (select id as id_b from tableb union all select null) b 
cross join (select id as id_c from tablec union all select null) c
cross join (select id as id_d from tabled union all select null) d
where coalesce(a.id_a, b.id_b, c.id_c, d.id_d) is not null

See the demo.

Upvotes: 1

Related Questions