Can you join three tables on the same key if that key could be missing from one or more of the tables?

Question

I'm taking three tables and joining them all together using the same primary key, but with no guarantee that all values exist across all three tables, and I want every value to end up in the final join.

For example, there's a TableA, TableB, and TableC, each with only one column (we'll just call the columns colA, colB, and colC). Value 10 is present in TableA and TableC, but not TableB.

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.colA = TableB.colB 
                     FULL OUTER JOIN TableC ON TableB.colB = TableC.colC;

+--------+  +--------+  +--------+
|  colA  |  |  colB  |  |  colC  |
+--------+  +--------+  +--------+
|      5 |  |      5 |  |      5 |
|     10 |  |     15 |  |     10 |
|     15 |  +--------+  |     15 |
+--------+              +--------+

In the above example, I end up with something like this after the joins:

+--------+--------+--------+
|  colA  |  colB  |  colC  |
+--------+--------+--------+
| 5      | 5      | 5      |
| null   | null   | 10     |
| 15     | 15     | 15     |
| 10     | null   | null   |
+--------+--------+--------+

My expected result is this:

+--------+--------+--------+
|  colA  |  colB  |  colC  |
+--------+--------+--------+
|      5 | 5      |      5 |
|     10 | null   |     10 |
|     15 | 15     |     15 |
+--------+--------+--------+

The logic for my join is to first join TableA and TableB, then join the result with TableC. In this specific scenario, the join does not work. The join can try to match on the column passed through from TableA or the column from TableB, but I'm not sure how to make it check both.

Because I first joined TableA and TableB, there was no matching value in TableB. When I try to join TableC on TableB, it also finds no match, even though the key exists in TableA.

The closest I've gotten to my desired result is by using this query:

SELECT * FROM TableA FULL OUTER JOIN TableB ON TableA.colA = TableB.colB 
                     RIGHT OUTER JOIN TableC ON TableC.colC IN (TableA.colA, TableB.colB);

+------+  +------+  +------+     +------+------+------+
| colA |  | colB |  | colC |     | colA | colB | colC |
+------+  +------+  +------+     +------+------+------+
|    5 |  |    5 |  |    5 |     | 5    | 5    |    5 |
|   10 |  |   15 |  |   10 | --> | 10   | null |   10 |
|   15 |  |   25 |  |   15 |     | 15   | 15   |   15 |
+------+  +------+  |   20 |     | null | null |   20 |
                    +------+     +------+------+------+

Expected result:

+------+------+------+
| colA | colB | colC |
+------+------+------+
| 5    | 5    | 5    |
| 10   | null | 10   |
| 15   | 15   | 15   |
| null | null | 20   |
| null | 25   | null |
+------+------+------+

This, unfortunately, does not include values that only exist in TableA or TableB in the final joined table, which is why I was using the FULL OUTER JOIN in the first place. With the FULL OUTER JOIN, I can't use the IN clause. Is there a solution that gives me what I want?

DB Fiddle: https://www.db-fiddle.com/f/faMLh4EAAKfBotXbaDofaQ/1

Answer 1

Here's a version that demonstrates a fully three way full outer join. A successful query will return seven rows:

+------+  +------+  +------+     +------+------+------+
| colA |  | colB |  | colC |     | colA | colB | colC |
+------+  +------+  +------+     +------+------+------+
|    1 |  |    2 |  |    4 |     |    1 | null | null |
|    3 |  |    3 |  |    5 | --> | null |    2 | null |
|    5 |  |    6 |  |    6 |     |    3 |    3 | null |
|    7 |  |    7 |  |    7 |     | null | null |    4 |
+------+  +------+  +------+     |    5 | null |    5 |
                                 | null |    6 |    6 |
                                 |    7 |    7 |    7 |
                                 +------+------+------+

select ColA, ColB, ColC
  from TableA
  full join TableB
    on ColA=ColB
  full join TableC
    on ColC=coalesce(ColA, ColB)
order by coalesce(ColA, ColB, ColC);

The secret is to coalesce the prior table keys in the join condition of each additional table.

You can see it in action here