Can You Use an Alias in the Where Clause in MySQL

Using column alias in WHERE clause of MySQL query produces an error

You can only use column aliases in GROUP BY, ORDER BY, or HAVING clauses.

Standard SQL doesn't allow you to
refer to a column alias in a WHERE
clause. This restriction is imposed
because when the WHERE code is
executed, the column value may not yet
be determined.

Copied from MySQL documentation

As pointed in the comments, using HAVING instead may do the work. Make sure to give a read at this question too: WHERE vs HAVING.

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

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

MYSQL using alias in the WHERE clause

MySQL extends the use of the HAVING clause, which can be used for this purpose. If the query is not an aggregation query, then HAVING still does filtering -- but it allows aliases.

So, you can write:

SELECT n.nid AS nid, l.name AS l, (6371.0 * ACOS(SIN((l.latitude * RADIANS(1))) * SIN((28.755925 * RADIANS(1))) + COS((l.latitude * RADIANS(1))) * COS((28.755925 * RADIANS(1))) * COS((l.longitude * RADIANS(1)) - (-81.346395 * RADIANS(1))))) AS distance
FROM node n LEFT JOIn
     location_instance li
     ON n.vid = li.vid LEFT JOIN
     location l
     ON li.lid = l.lid
WHERE n.status = 1 AND n.type IN ('locations') 
HAVING distance <= 100
ORDER BY distance
LIMIT 10;

Notes:

• If status is a number, which seems likely, then you should compare to a number, not a string.
• Table aliases make the query easier to write and to read.
• Although status and type could go in the HAVING clause, I suspect that they are better in the WHERE (I suspect that HAVING could affect optimization choices).
• You could use a subquery, but that would store all the data in the table.

How can I use alias name in where clause in MySQL?

Wrap a part of your current query up in a derived table:

select * from
(
SELECT 
    *,
    CASE roles.rol_active
        WHEN '1' THEN 'yes'
        WHEN '0' THEN 'no'
    END AS roles_active
FROM
    roles
) as dt
WHERE
    rol_is_deleted = 
 AND (rol_name LIKE '%ac%' 
  OR rol_display_name LIKE '%ac%'
  OR rol_description LIKE '%ac%'
  OR rol_active LIKE '%ac%'
  OR rol_updated_by LIKE '%ac%'
  OR rol_updated_at LIKE '%ac%')
ORDER BY rol_name asc
LIMIT 10 OFFSET 0;

You can even have two separate WHERE clauses. One for column conditions in the sub-query, and another one for column alias conditions in the outer query. I.e. something like:

...
WHERE column-conditions
) as dt
WHERE column-alias-conditions
...

Using alias in the WHERE and HAVING statements?

Only MySQL permits alises in HAVING, it is not standard SQL (see here: https://dba.stackexchange.com/questions/50391/why-does-mysql-allow-having-to-use-select-aliases ) please note that no other major RDBMS allows the use of aliases in WHERE or HAVING.

The reason you can't use aliases in WHERE (and HAVING) is because SELECT is actually evaluated after most other sub-clauses: https://stackoverflow.com/a/21693272/159145

A SELECT query is evaluated, conceptually, in the following order:

1. The FROM clause
2. The WHERE clause
3. The GROUP BY clause
4. The HAVING clause
5. The SELECT clause
6. The ORDER BY clause

So your query:

SELECT
    customer_id,
    address_id AS addressID 
FROM
    customer 
WHERE
    addressID = 5 

Is evaluated in this order:

1: FROM
    customer
2: WHERE
    address_id = 5
3: SELECT
    customer_id,
    address_id AS addressID

As you cans see, if the WHERE part referenced addressID instead of address_id the query execution engine would complain because addressID is not defined at that point.

MySQL does permit the referencing of (normal) aliases in HAVING by doing a (non-standard) neat trick where it partially evaluates the SELECT before it evaluates HAVING - and because MySQL has a handling of aliases that means the evaluation engine can be sure that the alias is valid (which is why most other RDBMS engines don't allow the use of aliases in HAVING when they otherwise should be able to). But you can't use an alias in WHERE because if there's a GROUP BY then it might render an alias meaningless, consider:

SELECT
    SUM( foo ) AS baz,
    created
FROM
    foo
WHERE
    baz > 5 -- Meaningless: the GROUP BY hasn't been evaluated yet, so `baz` is unavailable
GROUP BY
    created

MySQL explains this in their manual: https://dev.mysql.com/doc/refman/5.7/en/problems-with-alias.html

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.

The WHERE clause determines which rows should be included in the GROUP BY clause, but it refers to the alias of a column value that is not known until after the rows have been selected, and grouped by the GROUP BY.

Referencing a column alias in the WHERE clause

I would recommend writing the WHERE as:

WHERE fila_mailing = 2638 AND
      F1STA ='ANSWER' AND
      CLASSE1 IN ('VC2', 'VC3') AND
      FONE1 LIKE '31%' AND
      LASTCALL >= '2020-10-02' AND
      LASTCALL < '2020-10-31'

Note the changes to the logic:

• FONE1 appears to be a string, so the comparison uses string operations.
• The DATETIME comparisons uses >= and < rather than BETWEEN so the last second on the last day is not missed.
• The date format is simplified.

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.

---

Related Topics