CODEWITHSUNDEEP
sqlpostgresql
Tobia
Tobia

Reputation: 18831

Join tables by stitching columns, without making a cartesian product

Is there a formula or a trick to join tables by just stitching the columns together, without making a cartesian product?

For example, so that:

select 
    *
from 
    generate_series(1,3) a
??
    generate_series(10,11) b
??
    generate_series(21,24) c

would give:

a       b       c
------  ------  ------
1       10      21
2       11      22
3       (null)  23
(null)  (null)  24

given a mystery ?? operator.

I actually have a few sub-selects that I'd like to stitch together in this way. They may not have the same number of rows. The generate_series() above is just a way to get some sample data.

Upvotes: 2

Views: 88

Answers (1)

user330315
user330315

Reputation:

You can add the option with ordinality to generate a row number for each row from generate_series(). Then do a full outer join base on the row number:

select a.a, b.b, c.c
from generate_series(1,3) with ordinality a (a,rn)
  full outer join generate_series(10,11) with ordinality b (b, rn) using (rn)
  full outer join generate_series(21,24) with ordinality c (c, rn) using (rn)

this returns:

a | b  | c 
--+----+---
1 | 10 | 21
2 | 11 | 22
3 |    | 23
  |    | 24

If your source isn't a generate_series() then you need to add a row_number() to each sub-select:

select ...
from (
   select ...,  
          row_number() over (order by ...) as rn
   from table_one
) as a (..., rn)
  full outer join (
    select ...,  
           row_number() over (order by ...) as rn
    from table_two
  ) as b (..., rn) using (rn)
....

Upvotes: 4

Related Questions