Search code examples
mysqlsqlwhere-clausehaving-clause

Using alias in the WHERE and HAVING statements?

Example:

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

But, using the HAVING clause works perfectly fine. So why aliases don't work in the where clause?

Solution

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