Fixing Multiple SELECT Expressions Errors in SQL Server

If you’ve worked with SQL Server long enough, you’ve probably run into the dreaded “Only one expression can be specified in the select list when the subquery is not introduced with EXISTS” error. This error usually pops up when you try to use a subquery in a place where SQL Server expects a single value, but your query is returning multiple columns or multiple rows.

It’s a simple mistake, but it can be frustrating until you understand why it happens and how to fix it.

Why the Error Happens

SQL Server is strict about subqueries. A subquery used in a WHERE or SELECT clause must return exactly one column and (depending on context) either exactly one row or a set of rows that can be handled by operators like IN or EXISTS.

If your subquery returns multiple columns in places where SQL Server expects just one, the engine throws the multiple select expressions error.

Example

Suppose we create and populate two tables like this:

-- Create Departments table
CREATE TABLE Departments (
    DepartmentID INT PRIMARY KEY,
    DepartmentName NVARCHAR(100),
    ManagerID INT NULL
);

-- Create Employees table
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    JobTitle NVARCHAR(100),
    DepartmentID INT,
    HireDate DATE,
    Salary DECIMAL(10,2),
    FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- Step 1: Insert managers first (they will be linked as department heads)
INSERT INTO Employees (EmployeeID, FirstName, LastName, JobTitle, DepartmentID, HireDate, Salary)
VALUES
(109, 'Giulia', 'Conti', 'HR Specialist', 1, '2020-02-19', 61000.00),
(102, 'Arun', 'Khatri', 'Software Engineer', 2, '2021-01-25', 88000.00),
(101, 'Marisol', 'Vega', 'Data Analyst', 3, '2019-04-12', 72000.00),
(106, 'Omar', 'Benali', 'Business Analyst', 4, '2022-03-14', 68000.00),
(104, 'Mateo', 'Serrano', 'Project Manager', 5, '2018-11-03', 95000.00),
(107, 'Elena', 'Novak', 'Technical Writer', 6, '2021-07-29', 62000.00);

-- Step 2: Insert departments with managers already defined
INSERT INTO Departments (DepartmentID, DepartmentName, ManagerID)
VALUES
(1, 'Human Resources', 109),
(2, 'Engineering', 102),
(3, 'Data & Analytics', 101),
(4, 'Finance', 106),
(5, 'Project Management', 104),
(6, 'Documentation', 107);

-- Step 3: Insert the rest of the employees
INSERT INTO Employees (EmployeeID, FirstName, LastName, JobTitle, DepartmentID, HireDate, Salary)
VALUES
(103, 'Lina', 'Graves', 'UX Designer', 2, '2020-06-10', 79500.00),
(105, 'Hyejin', 'Cho', 'Database Administrator', 3, '2017-09-18', 91000.00),
(108, 'Tariq', 'Mahmood', 'DevOps Engineer', 2, '2019-12-05', 87000.00),
(110, 'David', 'Okafor', 'QA Engineer', 2, '2022-05-23', 70000.00);

Example of Error

Here’s a basic example of a query that results in the error:

SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID = (
    SELECT DepartmentID, ManagerID
    FROM Departments
    WHERE DepartmentName = 'Finance'
);

Output:

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

At first glance, it might look fine. But the subquery is returning two columns (DepartmentID and ManagerID). SQL Server doesn’t know which one we meant to compare to EmployeeID.

Fixing the Problem

The fix is straightforward. You need to make sure your subquery only returns the single column you actually need.

SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID = (
    SELECT ManagerID
    FROM Departments
    WHERE DepartmentName = 'Finance'
);

Result:

FirstName  LastName
---------  --------
Omar       Benali  

Now, the subquery returns only one column (ManagerID). SQL Server can properly compare it to EmployeeID.

You’ll notice that the outer query can return more than one column. The outer query is not the problem. It’s just the subquery that we’re concerned with here.

Another Case: Multiple Rows

Sometimes the issue isn’t multiple columns but multiple rows. Let’s say you try this:

SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID = (
    SELECT ManagerID
    FROM Departments
);

If there are several departments, this subquery will return multiple ManagerID values, which can’t be compared with =. That triggers a different error:

Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

To fix it, you have a couple of options.

If you really only want one value, use an aggregate (like MAX() or MIN()):

SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID = (
    SELECT MAX(ManagerID)
    FROM Departments
);

Output:

FirstName  LastName
---------  --------
Giulia     Conti   

If you want to allow multiple matches, use IN instead of =:

SELECT FirstName, LastName
FROM Employees
WHERE EmployeeID IN (
    SELECT ManagerID
    FROM Departments
);

Output:

FirstName  LastName
---------  --------
Marisol    Vega    
Arun       Khatri  
Mateo      Serrano 
Omar       Benali  
Elena      Novak   
Giulia     Conti   

This way, the query works even if the subquery returns several rows.

Lessons Learned

The main takeaway is that subqueries must fit the context:

• In a WHERE = condition the subquery must return a single scalar value.
• In a WHERE IN condition the subquery can return one column with multiple rows.
• In a SELECT list the subquery must return exactly one value.
• If you really need multiple columns, that’s a sign you should be using a JOIN, not a subquery.

Summary

The “multiple select expressions” error in SQL Server isn’t a bug – it’s more of a guardrail. It’s SQL Server’s way of telling you that your subquery doesn’t match the context where you’re using it.

Once you know the rules, the fixes are quick and easy. You can either narrow your subquery to one column, decide whether you need IN vs =, or rewrite the logic with a JOIN.