SQL Server Error Messages - Msg 116¶

Error Message¶

Server: Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Causes¶

This error is usually encountered when using a sub-query together with the IN or NOT IN logical operator. The IN logical operator determines whether a specified value matches any value in a subquery or a list. The NOT IN logical operator negates the subquery or list.

To illustrate, given the following table structures:

CREATE TABLE [dbo].[OrderHeader] (
    [OrderHeaderID]    INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    [OrderDate]        DATETIME,
    [OrderDescription] VARCHAR(100),
    [TotalCount]       INT,
    [TotalCost]        MONEY
)

CREATE TABLE [dbo].[OrderDetail] (
    [OrderDetailID]    INT NOT NULL IDENTITY(1, 1),
    [OrderHeaderID]    INT REFERENCES [dbo].[OrderHeader] ( [OrderHeaderID] ),
    [ItemNumber]       VARCHAR(10),
    [ItemCount]        INT,
    [UnitPrice]        MONEY,
    [TotalCost]        MONEY
)

The following SELECT statement, which tries to determine which OrderHeader records have corresponding OrderDetail records, will generate the error:

SELECT [OrderHeaderID], [OrderDate], [OrderDescription], [TotalCount], [TotalCost]
FROM [dbo].[OrderHeader]
WHERE [OrderHeaderID] IN (SELECT [OrderHeaderID], [OrderDetailID], [ItemNumber]
                          FROM [dbo].[OrderDetail])

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Similarly, using the NOT IN operator, the following SELECT statement will generate the error, which tries to determine which OrderHeader records don’t have corresponding OrderDetail records:

SELECT [OrderHeaderID], [OrderDate], [OrderDescription], [TotalCount], [TotalCost]
FROM [dbo].[OrderHeader]
WHERE [OrderHeaderID] NOT IN (SELECT [OrderHeaderID], [OrderDetailID], [ItemNumber]
                              FROM [dbo].[OrderDetail])

Msg 116, Level 16, State 1, Line 4
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Solution / Work Around¶

One way to avoid this error is to follow the suggestion of the error message and provide only one expression, or one column, in the SELECT subquery statement.

SELECT [OrderHeaderID], [OrderDate], [OrderDescription], [TotalCount], [TotalCost]
FROM [dbo].[OrderHeader]
WHERE [OrderHeaderID] IN (SELECT [OrderHeaderID]
                          FROM [dbo].[OrderDetail])

Another way of avoiding this error is to rewrite the SELECT statement and use the EXISTS operator or the INNER JOIN in the case where the IN operator is used, and use the NOT EXISTS operator or the LEFT OUTER JOIN in the case where the NOT IN operator is used.

In the case of the IN operator, the following SELECT statement, which uses the INNER JOIN, can be used to return all OrderHeader records that have corresponding OrderDetail records:

SELECT DISTINCT [OH].[OrderHeaderID], [OH].[OrderDate], [OH].[OrderDescription],
                [OH].[TotalCount], [OH].[TotalCost]
FROM [dbo].[OrderHeader] [OH] INNER JOIN [dbo].[OrderDetail] [OD]
  ON [OH].[OrderHeaderID] = [OD].[OrderHeaderID]

The DISTINCT clause is included since only unique OrderHeader records are needed in the output. If there are multiple OrderDetail records exist for a particular OrderHeader record, the OrderHeader record will be returned in the output multiple times as well, thus the need for the DISTINCT clause.

Another way of rewriting the SELECT statement that returns OrderHeader records that have corresponding OrderDetail records is with the use of the EXISTS operator, as shown in the following SELECT statement:

SELECT [OH].[OrderHeaderID], [OH].[OrderDate], [OH].[OrderDescription],
       [OH].[TotalCount], [OH].[TotalCost]
FROM [dbo].[OrderHeader] [OH]
WHERE EXISTS (SELECT [OD].[OrderHeaderID], [OD].[OrderDetailID], [OD].[ItemNumber]
              FROM [dbo].[OrderDetail] [OD]
              WHERE [OH].[OrderHeaderID] = [OD].[OrderHeaderID])

In the case of the NOT IN operator, the following SELECT statement, which uses the LEFT OUTER JOIN, can be used to return all OrderHeader records that have no corresponding OrderDetail records:

SELECT [OH].[OrderHeaderID], [OH].[OrderDate], [OH].[OrderDescription],
       [OH].[TotalCount], [OH].[TotalCost]
FROM [dbo].[OrderHeader] [OH] LEFT OUTER JOIN [dbo].[OrderDetail] [OD]
  ON [OH].[OrderHeaderID] = [OD].[OrderHeaderID]
WHERE [OD].[OrderHeaderID] IS NULL

Alternatively, the NOT EXISTS operator can be used instead of the NOT IN operator, as shown in the following SELECT statement:

SELECT [OH].[OrderHeaderID], [OH].[OrderDate], [OH].[OrderDescription],
       [OH].[TotalCount], [OH].[TotalCost]
FROM [dbo].[OrderHeader] [OH]
WHERE NOT EXISTS (SELECT [OD].[OrderHeaderID], [OD].[OrderDetailID], [OD].[ItemNumber]
                  FROM [dbo].[OrderDetail] [OD]
                  WHERE [OH].[OrderHeaderID] = [OD].[OrderHeaderID])