How to Use Alias in Where Clause

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

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 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:

1. FROM
2. JOIN
3. WHERE
4. GROUP BY
5. HAVING
6. SELECT
7. DISTINCT
8. ORDER BY

You can find more information about it by typing logical query processing in your browser.

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";

BigQuery, how to use alias in where clause?

You are trying to get the value of maxdate from your table maxdate. Add alias to your column and use this.

WITH
  maxdate AS (
  SELECT
    MAX(DATE(SETTLEMENTDATE)) as mx
  FROM
    `biengine-252003.aemo2.daily`)
SELECT
  *
FROM
  `biengine-252003.aemo2.daily`
WHERE
  DATE(SETTLEMENTDATE) = (SELECT mx FROM maxdate LIMIT 1)

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

MySQL column ALIAS in WHERE clause

Your query doesn't work, because WHERE statement executes before SELECT statement.
You should use HAVING statement to filtering of calculating fields. For example:

SELECT fullname,
  (SELECT MAX(login_time)
   FROM login_details
   WHERE reg_id=registration) AS login_time
FROM search_result
WHERE hire_work=0
GROUP BY fullname
HAVING login_time < '2018-05-22 18:09:00'

Also, subqueries aren't good practice. You can rewrite this query with JOIN

SELECT search_result.fullname,
       MAX(login_details.login_time) AS login_time
FROM search_result
JOIN login_details ON login_details.reg_id=search_result.registration
WHERE search_result.hire_work=0
GROUP BY fullname
HAVING login_time < '2018-05-22 18:09:00'

And.. Instead of a constant date, you can use the following expression: NOW() - INTERVAL 180 DAY. For example:

SELECT search_result.fullname,
       MAX(login_details.login_time) AS login_time
FROM search_result
JOIN login_details ON login_details.reg_id=search_result.registration
WHERE search_result.hire_work=0
GROUP BY fullname
HAVING login_time < NOW() - INTERVAL 180 DAY

---

Related Topics