Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

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.

The error you're encountering typically occurs in SQL Server when a subquery that returns multiple columns is used in a context where only a single column is allowed. 

Let's look at an example and how you can resolve this error.

Example Scenario

Consider you have the following tables:

-- Sample tables
CREATE TABLE Employees (
    EmployeeID INT,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    DepartmentID INT
);

CREATE TABLE Departments (
    DepartmentID INT,
    DepartmentName NVARCHAR(50)
);

And you run a query like this:

SELECT 
    EmployeeID,
    (SELECT DepartmentID, DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID)
FROM 
    Employees

Output:

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.

Explanation

In the above query, the subquery (SELECT DepartmentID, DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID) returns two columns (DepartmentID and DepartmentName). However, it's used in a context where only one column is expected, causing SQL Server to throw the error:

Solution

To resolve this issue, you need to ensure that the subquery returns only one column. Here are a few ways to adjust your query:

Option 1: Returning a Single Column

If you only need one of the columns from the subquery, modify the subquery to return just that column:

SELECT 
    EmployeeID,
    (SELECT DepartmentName FROM Departments WHERE Departments.DepartmentID = Employees.DepartmentID) AS DepartmentName
FROM 
    Employees

Option 2: Using a JOIN

If you need multiple columns from the subquery, use a JOIN instead of a subquery:

SELECT 
    Employees.EmployeeID,
    Departments.DepartmentID,
    Departments.DepartmentName
FROM 
    Employees
JOIN 
    Departments ON Employees.DepartmentID = Departments.DepartmentID

This way, you can select multiple columns from both tables without encountering the error.

Summary

The key to resolving this error is to ensure that subqueries used in a context expecting a single column only return one column. If you need multiple columns, consider using a JOIN to include the necessary columns in your result set.