Reference alias (calculated by SELECT) in the WHERE clause

Question

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

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

Is there any way this is possible? In this related question ( Using a variable in MySQL Select Statment in the Where> section), it seems that the answer will be, in fact, no, you just write the calculation (and perform this calculation in the query) twice, none of which is satisfactory.

Answer 1

You cannot reference an alias other than ORDER BY, because SELECT is the second last evaluated clause. 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 an expression is extremely complex (or expensive to calculate), you should probably consider a computed column (and possibly a persistent one), especially if many queries relate to the same expression.

PS Your concerns seem unfounded. In this simple example, at least SQL Server is smart enough to execute only once, even if you referenced it twice. Go ahead and compare plans; you will see that they are identical. If you have a more complicated case, when you see an expression evaluated several times, send a more complex request and plans.

Here are five sample queries that give 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; 

The final plan for all five queries:

Answer 2

You can do this using cross join

 SELECT c.BalanceDue AS BalanceDue FROM Invoices cross join (select (InvoiceTotal - PaymentTotal - CreditTotal) as BalanceDue) as c WHERE c.BalanceDue > 0;