Assuming the very simple schema
table main
main_id, ...
1 , ...
2 , ...
table sub1
main_id, sub1_data
1 , a
1 , b
2 , g
table sub2
main_id, sub2_data
1 , 1
2 , 1
2 , 2
As output I would like
main_id, sub1_data, sub2_data
1 , a , 1
1 , b , <null>
2 , g , 1
2 , <null> , 2
Using a union I can get close but then each list get's it's own rows
SELECT main_id, sub1_data, NULL
FROM sub1
UNION
SELECT main_id, NULL, sub2_data
FROM sub2
result
main_id, sub1_data, sub2_data
1 , a , <null>
1 , b , <null>
1 , <null> , 1
2 , g , <null>
2 , <null> , 1
2 , <null> , 2
So is there a way to get them two share rows and just use as many rows as the longest list?
One thing which can give you this "interleaved" output is the unnest() function with multiple parameters (or its equivalent: the ROWS FROM(...) construct):
Table functions may also be combined using the
ROWS FROMsyntax, with the results returned in parallel columns; the number of result rows in this case is that of the largest function result, with smaller results padded with null values to match.
...
The special table functionUNNESTmay be called with any number of array parameters, and it returns a corresponding number of columns, as ifUNNESThad been called on each parameter separately and combined using theROWS FROMconstruct.
select main_id, sub1_data, sub2_data
from (select main_id, array_agg(sub1_data) sub1_arr from sub1 group by main_id) s1
full join (select main_id, array_agg(sub2_data) sub2_arr from sub2 group by main_id) s2 using (main_id)
cross join unnest(sub1_arr, sub2_arr) u(sub1_data, sub2_data)
http://rextester.com/QSWESJ84203
Note: this is a "relatively" new feature: it was introduced in 9.4