How to combine multiple tables that vary slightly in columns

I have multiple tables where there are roughly 10 common columns, but some tables have 1-2 extra columns.

I would like to combine all these tables into one table with a row for each row from each table, with NULL values for any columns that didn't exist in each par开发者_如何学Cticular row's source table.

So my inputs look roughly like this:

table1
id  |  colA  | colB

table2
id  |  colA  | colB  | colC

table3
id  |  colA  | colB  | colD

And I am trying to get this:

allTables
id  |  colA  | colB  | colC | colD

In the above example all rows from table1 would have NULL values for colC and colD in allTables, all rows from table2 would have null values for colD, and all rows from table3 would have null values in colC.

A couple notes:

• The column id is not the same or related between tables in any way
• My example shows 3 tables, but I have about 8-9.
• Duplicate rows exist within each source table and should be preserved.

In particular I'm interested if there's an answer similar to the top voted one here or something like it that's more generalized.

---

SELECT
    id,
    colA,
    colB,
    NULL AS colC,
    NULL AS colD
FROM
    Table1
UNION ALL
SELECT
    id,
    colA,
    colB,
    colC,
    NULL AS colD
FROM
    Table2
UNION ALL
SELECT
    id,
    colA,
    colB,
    NULL AS colC,
    colD
FROM
    Table3

Since the ids are not related, you might also want to track which table the row came from in case there are duplicates between the tables. To do that, just have a hard-coded value with an alias with a different value in each of the three SELECT statements.

---

You could try this:

SELECT id, colA, colB, NULL AS colc, NULL AS cold
FROM table1

UNION ALL

SELECT id, colA, colB, colc, NULL AS cold
FROM table2

UNION ALL

SELECT id, colA, colB, NULL AS colc, cold
FROM table3

---

You can use a UNION:

          SELECT id, colA, colB, null AS colC, null AS colD FROM table1
UNION ALL SELECT id, colA, colB,         colC, null AS colD FROM table2
UNION ALL SELECT id, colA, colB, null AS colC,         colD FROM table3;

As discussed in the comments, you can vary this with UNION/UNION DISTINCT if you want to discard duplicates from among the different tables. Thanks to the commenters.

---

What you need is a UNION:

SELECT id, colA, colB, null as colC, null as colD
FROM table1
UNION
SELECT id, colA, colB, colC, null as ColD
FROM table2
UNION
SELECT id, colA, colB, null as colC, cold
FROM table3