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
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";
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.
SQL Use alias in Where statement
Not possible, but you can do the following:
SELECT SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) AS Col1
FROM MyTable
WHERE SUBSTRING(Column1, 1, 4) + SUBSTRING(Column1, 4, 3) = 'MySearch'
No subqueries or hacks required
How to use aliases in WHERE clause?
Logically, WHERE
clause is calculated before SELECT
. That's why you cannot use column sal
in WHERE
. It's not like in other languages where code is executed from top to bottom. A simplified order looks like this:
- FROM
- JOIN
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
You can find more information about it by typing logical query processing
in your browser.
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%';
How to use column alias in where clause
You cannot use table aliases in the where
clause.
Instead, just use the expressions:
WHERE _TABLE_SUFFIX BETWEEN '20191122' AND '20191202' AND
hits.transaction.transactionId IS NOT NULL AND
ARRAY_LENGTH(hits.product) > 2 AND
associated.productSKU <> ref.productSKU
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
Related Topics
Can SQL Server Express Localdb Be Connected to Remotely
Difference Between Right & Left Join VS Right & Left Outer Join in SQL
How to Add 'On Delete Cascade' in Alter Table Statement
How to Fill Date Gaps in MySQL
Can Parameterized Statement Stop All SQL Injection
SQL Query for 7 Day Rolling Average in SQL Server
When to Use Except as Opposed to Not Exists in Transact SQL
MySQL Full Text Search for Words with Three or Less Letters
How to Exclude Rows That Don't Join with Another Table
Selecting the Second Row of a Table Using Rownum
Postgresql Wildcard Like for Any of a List of Words
Finding the Next Available Id in MySQL
Ora - 00933 Confusion with Inner Join and "As"
Select a Random Sample of Results from a Query Result
How to Read the Last Row with SQL Server