title: postgresql: query two tables with same column names and show the result side by side ordered their column names, which occur in both tables
body: <p>Having two tables (<em>table1</em>, <em>table2</em>) with the same column names (<em>generation</em>, <em>parent</em>), the desired output would be the combination of all columns of both tables. Thereby the rows of <em>table2</em> should join <em>table1</em> so that the rows of <em>table2</em> are matching those of <em>table1</em> on <em>generation</em> column. The parent number should be ordered ascending for the entries in <em>table1</em> as well as in <em>table2</em>. The number of rows of the query results should be equal of those of <em>table1</em>.</p>
  <p>Given the following tables<br />
  <em>table1</em>:</p>
  <pre><code>| generation | parent |
  |:----------:|:------:|
  | 0          | 1      |
  | 0          | 2      |
  | 0          | 3      |
  | 1          | 3      |
  | 1          | 2      |
  | 1          | 1      |
  | 2          | 2      |
  | 2          | 1      |
  | 2          | 3      |
  </code></pre>
  <p><em>table2</em>:</p>
  <pre><code>| generation | parent |
  |:----------:|:------:|
  | 1          | 3      |
  | 1          | 1      |
  | 1          | 3      |
  | 2          | 1      |
  | 2          | 2      |
  | 2          | 3      |
  </code></pre>
  <p>The following queries are thought for creating and populating two sample tables as shown above:</p>
  <pre><code>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);
  </code></pre>
  <p>the imagined query should lead to the following <strong>desired result</strong>:</p>
  <pre><code>| 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             |
  </code></pre>
  <p>Current query looks as follows:</p>
  <pre><code>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;
  </code></pre>
  <p>Which leads to the following result:</p>
  <pre><code>| 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             |
  </code></pre>
  <p><a href="https://www.postgresqltutorial.com/postgresql-joins/" rel="nofollow noreferrer">This link</a> 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<br />
  Any help is highly appreciated. Thanks in advance!</p>