SQL Server Error Messages - Msg 8157¶

Error Message¶

Server: Msg 8157, Level 16, State 1, 
Procedure View Name, Line 1
All the queries in a query expression containing a UNION operator must have the same number of expressions in their select lists.

Causes:¶

This error occurs when creating a view wherein the view uses the UNION or UNION ALL operator and the number of columns specified in the SELECT clause on the tables are not the same.

To illustrate, let’s say you have the following table definitions for the company employees, past and present:

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
)

Then you decided to create a view on these two tables that will contain all employees of the company:

CREATE VIEW [dbo].[AllEmployees]
AS
SELECT * FROM [dbo].[Employees]
UNION ALL
SELECT * FROM [dbo].[OldEmployees]

Since the [dbo].[OldEmployees] table has an extra column for the last employment date of the employee, [LastEmploymentDate], the following error is encountered:

Server: Msg 8157, Level 16, State 1, Procedure AllEmployees, Line 3
All the queries in a query expression containing a UNION operator must have the same number of expressions in their select lists.

Solution / Work Around:¶

When creating a view that merges or joins the results of two tables that do not have the same number of columns, always specify the columns in the SELECT clause instead of using the SELECT *. Also, use fillers for the missing columns from each table. If the missing column is of VARCHAR data type, you can use NULL or an empty string as filler for the missing column. If the missing column is a numeric column, you can use either NULL or 0 for the missing column.

To avoid the error generated from the above example, the script for the view will look like the following:

CREATE VIEW [dbo].[AllEmployees]
AS
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 does not have the [LastEmploymentDate] column, a NULL value is returned for that column so that the number of columns in the first table matches the number of columns in the second column.