HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How to union four different tables

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
tablesunionfourdifferenthow

Problem

I have two tables whereby both of them use sub queries. These two tables are combined by using UNION ALL.
I have two other tables that only uses a WHERE clause and both of these are combined using UNION ALL.
I am struggling to join all 4 tables as I keep getting below error when I use UNION ALL and UNION.


Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

Solution

It means the number of columns you select in the top query must be the same as the number of columns in the second. If you don't have the same number, you can work around it. Below I can add a NULL to the second query because it's missing a third column.

SELECT col1,col2,col3
FROM t1
UNION ALL
SELECT col1,col2, NULL
FROM t2

Code Snippets

SELECT col1,col2,col3
FROM t1
UNION ALL
SELECT col1,col2, NULL
FROM t2

Context

StackExchange Database Administrators Q#101682, answer score: 6

Revisions (0)

No revisions yet.