Reference alias (calculated in SELECT) in WHERE clause

by

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:

enter image description here

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to get a particular preceding value of a column in SQL [closed]
  3. SQL where clause odd behavior [closed]
  4. How do I obtain a Query Execution Plan in SQL Server?
  5. How do I escape a single quote in SQL Server?
  6. Simple way to transpose columns and rows in SQL?
  7. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  8. Only inserting a row if it’s not already there
  9. Unpivot with column name
  10. Selecting data from two different servers in SQL Server
  11. Most efficient way in SQL Server to get date from date+time?
  12. Generate SQL Create Scripts for existing tables with Query
  13. How to rewrite IS DISTINCT FROM and IS NOT DISTINCT FROM in SQL Server 20008R2?
  14. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  15. convert Excel Date Serial Number to Regular Date
  16. For Nvarchar(Max) I am only getting 4000 characters in TSQL?
  17. How do I create a foreign key in SQL Server?
  18. How do I group on continuous ranges
  19. join comma delimited data column
  20. Need to list all triggers in SQL Server database with table name and table’s schema
  21. Count based on condition in SQL Server
  22. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  23. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  24. Best equivalent for IsInteger in SQL Server
  25. Syntax of for-loop in SQL Server
  26. How to correctly insert newline in nvarchar [duplicate]
  27. How do you copy a record in a SQL table but swap out the unique id of the new row?
  28. Using IF ELSE statement based on Count to execute different Insert statements
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment