How to use alias column name in where clause in SQL Server
You can't use aliased columns in a WHERE
clause. You can try using a derived table. Perhaps something like this (sorry, not tested):
SELECT * FROM
(SELECT SQRT(POWER(cast(Program_Latitude as float) - cast('41.5126237' as float), 2) +
POWER(cast(Program_Longitude as float) - cast('-81.6516411' as float), 2)) * 62.1371192
AS DistanceFromAddress from tblProgram) mytable
WHERE DistanceFromAddress < 2
SQL Server AS statement aliased column within WHERE statement
SQL doesn't typically allow you to reference column aliases in WHERE, GROUP BY or HAVING clauses. MySQL does support referencing column aliases in the GROUP BY and HAVING, but I stress that it will cause problems when porting such queries to other databases.
When in doubt, use the actual column name:
SELECT t.lat AS latitude
FROM poi_table t
WHERE t.lat < 500
I added a table alias to make it easier to see what is an actual column vs alias.
Update
A computed column, like the one you see here:
SELECT *,
( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) AS distance
FROM poi_table
WHERE distance < 500;
...doesn't change that you can not reference a column alias in the WHERE clause. For that query to work, you'd have to use:
SELECT *,
( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) AS distance
FROM poi_table
WHERE ( 6371*1000 * acos( cos( radians(42.3936868308) ) * cos( radians( lat ) ) * cos( radians( lon ) - radians(-72.5277256966) ) + sin( radians(42.3936868308) ) * sin( radians( lat ) ) ) ) < 500;
Be aware that using a function on a column (IE: RADIANS(lat)
) will render an index useless, if one exists on the column.
Referring to a Column Alias in a WHERE Clause
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
WHERE ( DATEDIFF(day, maxlogtm, GETDATE() > 120)
Normally you can't refer to field aliases in the WHERE
clause. (Think of it as the entire SELECT
including aliases, is applied after the WHERE
clause.)
But, as mentioned in other answers, you can force SQL to treat SELECT
to be handled before the WHERE
clause. This is usually done with parenthesis to force logical order of operation or with a Common Table Expression (CTE):
Parenthesis/Subselect:
SELECT
*
FROM
(
SELECT
logcount, logUserID, maxlogtm,
DATEDIFF(day, maxlogtm, GETDATE()) AS daysdiff
FROM statslogsummary
) as innerTable
WHERE daysdiff > 120
Or see Adam's answer for a CTE version of the same.
How do I use alias in where clause?
The SQL-Server docs says:
column_alias can be used in an ORDER BY clause, but it cannot be used in a WHERE, GROUP BY, or HAVING clause.
Similar in the MySQL doc it says:
Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined.
In MySQL you can at least reuse aliases in the SELECT clause
How to use alias in where clause in SQL Server
I would do instead :
SELECT *, STUFF(stManufacturerPartReference, 1, 1, '') AS stManufacturerPartReference
FROM . . . .
. . . . CROSS APPLY
( SELECT ', ' + PM.stManufacturerPartReference
FROM tblProductManufacturers PM JOIN
tblProducts Product
ON PM.inProductId = Product.inProductId JOIN
tblManufacturers M
ON M.inManufacturerId = PM.inManufacturerId
WHERE PM.inProductId=product.inProductId
FOR XML PATH('')
) tt(stManufacturerPartReference)
WHERE . . . AND
stManufacturerPartReference LIKE '%ABC DEF%';
Reference alias (calculated in SELECT) in WHERE clause
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:
SQL - Is it possible to use alias in where?
The WHERE
clause is evaluated before select. Hence the where clause is not aware of the aliases you used.
So you need to use the original column name:
SELECT CustomerID AS Id, CustomerName AS Customer
FROM Customers
WHERE CustomerName="Alfreds Futterkiste";
If you must use alias in the where clause, you may use subquery or CTE (an overkill and may result in slower query):
SELECT * from (
SELECT CustomerID AS Id, CustomerName AS Customer
FROM Customers
) t WHERE Customer = "Alfreds Futterkiste";
How to reuse column alias in another column within same select statement?
In SQL Server, you can use APPLY
to define column aliases in the FROM
clause -- a good place, in my opinion:
select pd.id v.value_1,
(Case when value_1 in ('test1', 'test2') Then '0'
else value_1
End) as value_2
from pd cross apply
(values ( case . . . )
) v(value_1);
Notes:
- Don't use identifier names that need to be escaped, hence
value_1
instead ofvalue 1
. value_1
appears to be string. Thecase
expression forvalue_2
should return a string, so'0'
instead of0
.- I define table aliases using
as
after the expression. This is the SQL standard, but there is nothing per se wrong with using=
, which is SQL Server specific syntax.
Related Topics
How to Group and Choose Lowest Value in SQL
SQL String Comparison Speed 'Like' VS 'Patindex'
Calculate Time Difference (Only Working Hours) in Minutes Between Two Dates
Query Combinations with Nested Array of Records in JSON Datatype
Run Stored Procedure and Return Values from Vba
How to Add Sequence Number for Groups in a SQL Query Without Temp Tables
Is SQL Order by Clause Guaranteed to Be Stable ( by Standards)
Does Inner Join Performance Depends on Order of Tables
"Missing Right Parenthesis": on Delete Set Null on Update Cascade (Sql/Oracle)
How to List Active Connections on Postgresql
How to Sort Values in Columns and Update Table
Ora-22905 - When Querying a Table Type with a Select Statement
Number of Fridays Between Two Dates
Order of Ands in Where Clause for Greatest Performance
How to Fix Ora-01427 Single-Row Subquery Returns More Than One Row in Select