UNION with unequal columns in joined tables

Question

When building a query to display results from 2 tables where 2 are identical but the third is not (has fewer fields but does share the 4 columns which need to be returned in the result), what is the proper way to build the query?

Table1: sku field1 field2 field3 field 4 field 5 field 6 field 7

Table2: sku field1 field2 field3 field 4 field 5 field 6 field 7

Table3: sku field1 field2 field3 field 4

Both of these Fails of course:

SELECT * FROM table1 UNION SELECT * FROM table2 UNION SELECT * FROM table3 ORDER BY sku ASC

SELECT * FROM table1 UNION SELECT * FROM table2 UNION SELECT sku, field1, field2, field3, field4 FROM table3 ORDER BY sku ASC

in another SO post I saw where the query was using NULL AS fieldname to make the tables equal for the query but if table1 & table2 had say 80 fields and table3 had 5, does that mean I have to add NULL AS fieldname6...7...8....> for 75 fields?

Just a whim I tried it as below which also failed:

SELECT * FROM table1 UNION SELECT * FROM table2 UNION SELECT sku, field1, field2, field3, field4, NULL AS * FROM table3 ORDER BY sku ASC

Thanks for any help.

Do you really need to do a SELECT *? Can't you just pick the fields you need, or do you really need every field? — andrewsi, Dec 22 '13 at 15:45
the actual tables( 1 and 2) do in fact have 75 fields of which 23 are needed for the query. select all seemed better than a query that laid out 23 column names — DMSJax, Dec 22 '13 at 15:47
List out the column names. Using asterisks in production code (excluding their use in EXISTS/ NOT EXISTS clauses) is not a good practice. If it is that _painful_ to write out the column names, then familiarize yourself with the **information_schema** and use that to generate a list of column names that you can copy and paste. — AgRizzo, Dec 22 '13 at 16:10

Gordon Linoff · Accepted Answer · 2013-12-22T16:57:56.500

Try this:

SELECT * FROM table1
UNION
SELECT * FROM table2
UNION
SELECT sku, field1, field2, field3, field4, NULL, NULL, NULL
FROM table3
ORDER BY sku ASC

All the subqueries have to have the same number of columns. By the way, union all is better than union if you don't care about duplicates. Union all runs faster because there is no duplicate removal.

EDIT:

In fact, you should just explicitly list the columns that you want from each table:

SELECT sku, field1, field2, field3, field4, field5, field6, field7
FROM table1
UNION
SELECT sku, field1, field2, field3, field4, field5, field6, field7
FROM table2
UNION
SELECT sku, field1, field2, field3, field4, NULL, NULL, NULL
FROM table3
ORDER BY sku ASC;

And, yes, you have to do this for each component of the union/union all.

gordon, the actual tables in question have 75 fields (table 1 & 2) of which 23 are needed/shared tor the query results, so for the table 3 part of the select do I have to add 52 NULL's in the last SELECT? — DMSJax, Dec 22 '13 at 15:50

score 0 · Answer 2 · answered Dec 22 '13 at 16:01

Has to be the same number of columns and type (or at least there needs to be an implicit conversion. NB if you do any aliasing you only need to do it in the first select, it's that one that gives you the column names in the result. So all the selects have to have 23 columns based on what you said in the question, you only need the 75 if you are using select *, which in my opinion would be a mistake in that changes to any of the three tables could if you are lucky break it.

SELECT sku, column2, etc FROM table1
UNION
SELECT sku, Column2, etc FROM table2
UNION
SELECT sku, column2, etc, NULL, NULL, NULL
FROM table3
ORDER BY sku ASC

If you don't want to name all the columns all three times, a temporary table and three insert into's would do it, but don't do that just to avoid typing.

UNION with unequal columns in joined tables

2 Answers2