Geek Answers Handbook

How to combine multiple tables that differ slightly in columns

I have several tables where there are approximately 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 did not exist in each particular source row table.

So my inputs look something like this:

table1 id | colA | colB table2 id | colA | colB | colC table3 id | colA | colB | colD

And I'm trying to do this:

 allTables id | colA | colB | colC | colD

In the above example, all rows from table1 will have NULL values ​​for colC and colD in all tables, all rows from table2 will have zero values ​​for colD, and all rows from table 3 will have zero values ​​in colC.

A few notes:

  • The column identifier does not match or is not related between tables.
  • My example shows 3 tables, but I have about 8-9.
  • Duplicate rows exist in each source table and must be preserved.

In particular, I am interested in whether there is an answer similar to the top one voted here or something like this, which is more general.

+4
source share
4 answers
 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 identifiers are not related, you can also keep track of which table the row came from if there are duplicates between the tables. To do this, simply enter a solid value with an alias with a different value in each of the three SELECT .

+4
source

You can use 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 change this with UNION / UNION DISTINCT if you want to undo duplicates from different tables. Thanks to the commentators.

+3
source

You can try the following:

 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
+3
source

You will need 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
0
source

More articles:


All Articles