sql - Reference alias (calculated in SELECT) in WHERE clause

Question

SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE BalanceDue > 0 --error

The calculated value BalanceDue that is set as a variable in the list of selected columns cannot be used in the WHERE clause.

Is there a way that it can? In this related question (Using a variable in MySQL Select Statment in a Where Clause), it seems like the answer would be, actually, no, you would just write out the calculation (and perform that calculation in the query) twice, none of which is satisfactory.

Answer 1

You can't reference an alias except in ORDER BY because SELECT is the second last clause that's evaluated. Two workarounds:

SELECT BalanceDue FROM (
  SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
  FROM Invoices
) AS x
WHERE BalanceDue > 0;

Or just repeat the expression:

SELECT (InvoiceTotal - PaymentTotal - CreditTotal) AS BalanceDue
FROM Invoices
WHERE  (InvoiceTotal - PaymentTotal - CreditTotal)  > 0;

I prefer the latter. If the expression is extremely complex (or costly to calculate) you should probably consider a computed column (and perhaps persisted) instead, especially if a lot of queries refer to this same expression.

PS your fears seem unfounded. In this simple example at least, SQL Server is smart enough to only perform the calculation once, even though you've referenced it twice. Go ahead and compare the plans; you'll see they're identical. If you have a more complex case where you see the expression evaluated multiple times, please post the more complex query and the plans.

Here are 5 example queries that all yield the exact same execution plan:

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE LEN(name) + column_id > 30;

SELECT x FROM (
SELECT LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;

SELECT LEN(name) + column_id AS x
FROM sys.all_columns
WHERE column_id + LEN(name) > 30;

SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE x > 30;

SELECT name, column_id, x FROM (
SELECT name, column_id, LEN(name) + column_id AS x
FROM sys.all_columns
) AS x
WHERE LEN(name) + column_id > 30;

Resulting plan for all five queries: