How to combine multiple tables that vary slightly in columns

Question

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 particular 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.

score 4 · Accepted Answer · answered Jul 19 '11 at 17:07

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.

Your last part has ColC where there is none. – Jacob Jul 19 '11 at 17:08 — Jacob, Jul 19 '11 at 17:08

score 3 · Answer 2 · edited Jul 19 '11 at 19:23

3

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.

edited Jul 19 '11 at 19:23

Andriy M

76,112
17
94
154

answered Jul 19 '11 at 17:07

Kerrek SB

464,522
92
875
1,084

You might be better off with UNION ALL. Got my vote otherwise. – bobs Jul 19 '11 at 17:10
UNION is going to lose any duplicate rows. The question doesn't specify whether or not that's ok, but it should be pointed out. – Tom H Jul 19 '11 at 17:10

score 3 · Answer 3 · answered Jul 19 '11 at 17:08

3

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

answered Jul 19 '11 at 17:08

bobs

21,844
12
67
78

score 0 · Answer 4 · answered Jul 19 '11 at 17:08

0

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

answered Jul 19 '11 at 17:08

phlogratos

13,234
1
32
37

How to combine multiple tables that vary slightly in columns

4 Answers4

Linked