Using an Alias in a 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.

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

Can you use an alias in the WHERE clause in mysql?

You could use a HAVING clause, which can see the aliases, e.g.

 HAVING avg_rating>5

but in a where clause you'll need to repeat your expression, e.g.

 WHERE (sum(reviews.rev_rating)/count(reviews.rev_id))>5

BUT! Not all expressions will be allowed - using an aggregating function like SUM will not work, in which case you'll need to use a HAVING clause.

From the MySQL Manual:

It is not allowable to refer to a
column alias in a WHERE clause,
because the column value might not yet
be determined when the WHERE clause
is executed. See Section B.1.5.4,
“Problems with Column Aliases”.

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

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

Using an Alias in a WHERE clause

This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain.

You can do a sub-select and filter on it:

SELECT * FROM
(
  SELECT A.identifier
    , A.name
    , TO_NUMBER(DECODE( A.month_no
      , 1, 200803 
      , 2, 200804 
      , 3, 200805 
      , 4, 200806 
      , 5, 200807 
      , 6, 200808 
      , 7, 200809 
      , 8, 200810 
      , 9, 200811 
      , 10, 200812 
      , 11, 200701 
      , 12, 200702
      , NULL)) as MONTH_NO
    , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
  FROM table_a A
    , table_b B
  WHERE A.identifier = B.identifier
) AS inner_table
WHERE 
  MONTH_NO > UPD_DATE

Interesting bit of info moved up from the comments:

There should be no performance hit.
Oracle does not need to materialize
inner queries before applying outer
conditions -- Oracle will consider
transforming this query internally and
push the predicate down into the inner
query and will do so if it is cost
effective. – Justin Cave

Alias in where clause and the group by

You cannot use an alias in the WHERE clause; you would need to use the full un-aliased expression instead. However, that will not work here as the expression you have aliased is an aggregation and the WHERE filters are evaluated before the GROUP BY and you need to filter on the result of the aggregation after the GROUP BY; that is what the HAVING clause is for, filtering after GROUPing.

So, either use HAVING and repeat the aggregation expression:

SELECT product_status,
       SUM(list_price) as sum_prices
FROM   OE.PRODUCT_INFORMATION
WHERE  LOWER(product_status) NOT LIKE '%orderable%'
GROUP BY product_status
HAVING SUM(list_price) >= 4000;

Or wrap it in a sub-query then you can use the alias in the outer query.

SELECT *
FROM   (
  SELECT product_status,
         SUM(list_price) as sum_prices
  FROM   OE.PRODUCT_INFORMATION
  WHERE  LOWER(product_status) NOT LIKE '%orderable%'
  GROUP BY product_status
)
WHERE  sum_prices >= 4000;