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:
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With