Having two tables (table1, table2) with the same column names (generation, parent), the desired output would be the combination of all columns of both tables. Thereby the rows of table2 should join table1 so that the rows of table2 are matching those of table1 on generation column. The parent number should be ordered ascending for the entries in table1 as well as in table2. The number of rows of the query results should be equal of those of table1.

Given the following tables
table1:

| generation | parent |
|:----------:|:------:|
| 0          | 1      |
| 0          | 2      |
| 0          | 3      |
| 1          | 3      |
| 1          | 2      |
| 1          | 1      |
| 2          | 2      |
| 2          | 1      |
| 2          | 3      |

table2:

| generation | parent |
|:----------:|:------:|
| 1          | 3      |
| 1          | 1      |
| 1          | 3      |
| 2          | 1      |
| 2          | 2      |
| 2          | 3      |

The following queries are thought for creating and populating two sample tables as shown above:

create table table1(generation integer, parent integer);
insert into table1 (generation, parent) values(0,1),(0,2),(0,3),(1,3),(1,2),(1,1),(2,2),(2,1),(2,3);
create table table2(generation integer, parent integer);
insert into table2 (generation, parent) values(1,3),(1,1),(1,3),(2,1),(2,2),(2,3);

the imagined query should lead to the following desired result:

| table1_generation | table1_parent | table2_generation | table2_parent |
|:-----------------:|:-------------:|:-----------------:|:-------------:|
| 0                 | 1             |                   |               |
| 0                 | 2             |                   |               |
| 0                 | 3             |                   |               |
| 1                 | 1             | 1                 | 1             |
| 1                 | 2             | 1                 | 3             |
| 1                 | 3             | 1                 | 3             |
| 2                 | 1             | 2                 | 1             |
| 2                 | 2             | 2                 | 2             |
| 2                 | 3             | 2                 | 3             |

Current query looks as follows:

with 
  p as (
    select 
      generation,
      parent 
    from 
      table1
    order by
      generation,
      parent
  ), o as(
    select
      generation,
      parent 
    from 
      table2 
    order by
      generation,
      parent
  )
select
  p.generation as table1_generation,
  p.parent as table1_parent,
  o.generation as table2_generation,
  o.parent as table2_parent
from
  p
left join o on 
  o.generation=p.generation;

Which leads to the following result:

| table1_generation | table1_parent | table2_generation | table2_parent |
|:-----------------:|:-------------:|:-----------------:|:-------------:|
| 0                 | 1             |                   |               |
| 0                 | 2             |                   |               |
| 0                 | 3             |                   |               |
| 1                 | 1             | 1                 | 1             |
| 1                 | 1             | 1                 | 3             |
| 1                 | 1             | 1                 | 3             |
| 1                 | 2             | 1                 | 1             |
| 1                 | 2             | 1                 | 3             |
| 1                 | 2             | 1                 | 3             |
| 1                 | 3             | 1                 | 1             |
| 1                 | 3             | 1                 | 3             |
| 1                 | 3             | 1                 | 3             |
| 2                 | 1             | 2                 | 1             |
| 2                 | 1             | 2                 | 2             |
| 2                 | 1             | 2                 | 3             |
| 2                 | 2             | 2                 | 1             |
| 2                 | 2             | 2                 | 2             |
| 2                 | 2             | 2                 | 3             |
| 2                 | 3             | 2                 | 1             |
| 2                 | 3             | 2                 | 2             |
| 2                 | 3             | 2                 | 3             |

This link led to the conclusion, that any join command might not what is necessary here ... But union does only append rows... so for me it is absolutely unclear, how the desired result can be achieved o.O
Any help is highly appreciated. Thanks in advance!

CodePudding user response：

The main misunderstanding on this question arose from the fact that you mentioned join, which is a very precisely mathematically defined concept based on the Cartesian product and can be applied to any two sets. So the current output is clear. But as you wrote in the title, you want to put two tables side by side. You take advantage of the fact that they have the same number of rows (triples).

This select returns the output you want.
I made artificial join columns, row_number() OVER (order by generation, parent) as rnum, and moved the second table using the addition of three. I hope this helps you:

with 
  p as (
    select 
      row_number() OVER (order by generation, parent) as rnum,
      generation,
      parent 
    from 
      table1
    order by
      generation,
      parent
  ), o as(
    select
      row_number() OVER (order by generation, parent) as rnum,
      generation,
      parent 
    from 
      table2 
    order by
      generation,
      parent
  )
select
  p.generation as table1_generation,
  p.parent as table1_parent,
  o.generation as table2_generation,
  o.parent as table2_parent
from
  p
left join o on 
  o.rnum 3=p.rnum
order by 1,2,3,4;

Output: