title: How can I join multiple tables with same primary key to one table with one row per key?
body: <h2 id="what-i-have">What I have</h2>
  <p>I have multiple tables with a primary key <code>key</code> and a value. Each key might be present in all tables or only in a subset of tables.</p>
  <p>Here are example tables:</p>
  <p>Tab1:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">key</th>
  <th style="text-align: left;">value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">1</td>
  </tr>
  <tr>
  <td style="text-align: left;">C</td>
  <td style="text-align: left;">3</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Tab2:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">key</th>
  <th style="text-align: left;">value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">10</td>
  </tr>
  <tr>
  <td style="text-align: left;">D</td>
  <td style="text-align: left;">10</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Tab3:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">key</th>
  <th style="text-align: left;">value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">B</td>
  <td style="text-align: left;">20</td>
  </tr>
  <tr>
  <td style="text-align: left;">D</td>
  <td style="text-align: left;">10</td>
  </tr>
  </tbody>
  </table>
  </div><h2 id="what-i-want">What I want</h2>
  <p>I want to join the tables in such a fashion, that I get one row for each existing key and one column for each table. If a key is not present in one of the tables, <code>null</code> should be assumed. So for the example above I'd like to get this result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">key</th>
  <th style="text-align: left;">tab1</th>
  <th style="text-align: left;">tab2</th>
  <th style="text-align: left;">tab3</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">A</td>
  <td style="text-align: left;">1</td>
  <td style="text-align: left;">10</td>
  <td style="text-align: left;">NULL</td>
  </tr>
  <tr>
  <td style="text-align: left;">B</td>
  <td style="text-align: left;">NULL</td>
  <td style="text-align: left;">NULL</td>
  <td style="text-align: left;">20</td>
  </tr>
  <tr>
  <td style="text-align: left;">C</td>
  <td style="text-align: left;">3</td>
  <td style="text-align: left;">NULL</td>
  <td style="text-align: left;">NULL</td>
  </tr>
  <tr>
  <td style="text-align: left;">D</td>
  <td style="text-align: left;">NULL</td>
  <td style="text-align: left;">10</td>
  <td style="text-align: left;">10</td>
  </tr>
  </tbody>
  </table>
  </div><h2 id="what-i-have-tried-so-far">What I have tried so far</h2>
  <p>Here is what I came up with, which works:</p>
  <pre class="lang-sql prettyprint-override"><code>with tab1 as (
      select * from (values('A', 1), ('C', 3))x(key, value)
  ),
  tab2 as (
      select * from (values('A', 10), ('D', 10))x(key, value)
  ),
  tab3 as (
      select * from (values('B', 20), ('D', 10))x(key, value)
  )
  select
      coalesce(tab1.key, tab2.key, tab3.key) &quot;key&quot;,
      tab1.value tab1,
      tab2.value tab2,
      tab3.value tab3
  from tab1
  full outer join tab2 on tab2.key = tab1.key
  full outer join tab3 on tab3.key = coalesce(tab1.key, tab2.key)
  order by coalesce(tab1.key, tab2.key, tab3.key)
  </code></pre>
  <h2 id="the-question">The question</h2>
  <p>In the case above my query looks still quite OK, however in the real case I need to join 6 tables and the key is actually distributed across three columns. This leads to very long join statements. The statement for the sixth table looks like this:</p>
  <pre class="lang-sql prettyprint-override"><code>full outer join tab6
      on tab6.key1 = coalesce(tab1.key1, tab2.key1, tab3.key1, tab4.key1, tab5.key1)
      and tab6.key2 = coalesce(tab1.key2, tab2.key2, tab3.key2, tab4.key2, tab5.key2)
      and tab6.key3 = coalesce(tab1.key3, tab2.key3, tab3.key3, tab4.key3, tab5.key3)
  </code></pre>
  <p>The explosion of copy&amp;pasted <code>coalesce</code>-calls is somewhat disturbing from a code quality perspective.</p>
  <p><strong>Is there another, simpler way to get the result I want?</strong></p>