|
|
Error Message
Server: Msg 205, Level 16, State 1, Line 1
All queries in an SQL statement containing a UNION operator
must have an equal number of expressions in their target
lists.
Causes:
This error occurs when doing either a UNION or UNION ALL on two tables wherein the number of columns specified in the SELECT statement in the first table does not match the number of columns specified in the SELECT statement in the second table.
|
To illustrate on how the error can be reproduced, let’s say you have two tables for the company employees, one which contains existing employees and the other containing previous employees of the company.
CREATE TABLE [dbo].[Employees] (
[EmployeeID] INT,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Position] VARCHAR(50)
)
CREATE TABLE [dbo].[OldEmployees] (
[EmployeeID] INT,
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50),
[Position] VARCHAR(50),
[LastEmploymentDate] DATETIME
)
To get a list of all employees of the company, previous and past, you would do a UNION ALL of both tables, as follows:
SELECT * FROM [dbo].[Employees]
UNION ALL
SELECT * FROM [dbo].[OldEmployees]
But since the [dbo].[OldEmployees] table has an extra column, [LastEmploymentDate], the following error is encountered:
Server: Msg 205, Level 16, State 1, Line 2
All queries in an SQL statement containing a UNION operator
must have an equal number of expressions in their target lists.
Solution / Work Around:
When performing a UNION or UNION ALL on two tables that do not have the same number of columns, you have to specify each column in the SELECT clause instead of doing a SELECT *. Aside from this, for the table that has a lesser number of columns, you have to provide a filler for the missing column or columns, such as NULL values or empty strings for VARCHAR columns and 0 for numeric columns.
To avoid the error in the example above, here’s how the SELECT statement will look like:
SELECT [EmployeeID], [FirstName], [LastName],
[Position], NULL AS [LastEmploymentDate]
FROM [dbo].[Employees]
UNION ALL
SELECT [EmployeeID], [FirstName], [LastName],
[Position], [LastEmploymentDate]
FROM [dbo].[OldEmployees]
Since the [dbo].[Employees] table contain current employees, it doesn’t have a column for the LastEmploymentDate. To overcome the error, a NULL value is returned for that column, as shown in the SQL statement.
|