Error 156 in SQL Server is a generic error that doesn’t tell us much, other than the fact that we’re using the wrong syntax.

But if you’re running a subquery, and you’re getting error 156, which reads “Incorrect syntax near the keyword ‘WHERE’.” or whatever keyword it’s near, it could be that you haven’t declared an alias for the subquery.

Example of Error

Here’s an example of code that produces the error:

SELECT * FROM
(
    SELECT 
        DepartmentID,
        SUM(Salary) AS SumSalary
    FROM Employees
    GROUP BY DepartmentID
)
WHERE SumSalary > 100000;

Output:

Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'WHERE'.

All it tells us is that there’s a syntax error near the WHERE clause.

Here’s a slightly more elaborate query that produces the same error:

SELECT 
    ds.sale_date,
    ds.total_sales,
    SUM(prev.total_sales) AS running_total
FROM 
    daily_sales AS ds
JOIN (
    SELECT sale_date, total_sales
    FROM daily_sales
) ON sale_date <= ds.sale_date
GROUP BY ds.sale_date, ds.total_sales
ORDER BY ds.sale_date;

Output:

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ON'.

While it’s still the same error (error 156), this time it tells us that the incorrect syntax is near the ON keyword.

Solution

In both cases, the solution is the same; declare an alias for the subquery.

Here’s how we can fix the first query:

SELECT * FROM
(
    SELECT 
        DepartmentID,
        SUM(Salary) AS SumSalary
    FROM Employees
    GROUP BY DepartmentID
) AS SalaryTotals
WHERE SalaryTotals.SumSalary > 100000;

The thing that fixed it was the AS SalaryTotals part. This provides an alias for the subquery, and we were able to reference that alias in the next line. We don’t always have to refer to the alias like I did here in the next line, but it could come in handy if there are multiple tables with the same column name (in which case we’d need to reference the alias so that SQL Server knows which column name we’re referring to). But as mentioned, the part that resolved the error was the declaration of the alias itself.

Let’s fix the second query:

SELECT 
    ds.sale_date,
    ds.total_sales,
    SUM(prev.total_sales) AS running_total
FROM 
    daily_sales AS ds
JOIN (
    SELECT sale_date, total_sales
    FROM daily_sales
) AS prev ON prev.sale_date <= ds.sale_date
GROUP BY ds.sale_date, ds.total_sales
ORDER BY ds.sale_date;

For this example, I provided AS prev to declare an alias of prev for the subquery.

In this case, we really do need this alias for when we reference the sale_date column in the ON clause, as there are two columns with that name (one in the subquery, and one in the outer query). If we didn’t prefix these column names, we’d get error 209 indicating an ambiguous column name.