Can I Use Aliases in MySQL's WHERE Clause?

MySQL WHERE Clause: Alias Limitations

MySQL allows aliasing columns for improved readability and to create new expressions within queries. However, directly using aliases within the WHERE clause often results in an "unknown column" error. This is because the WHERE clause filters rows before aliases are assigned.

Workarounds:

One solution involves using the HAVING clause. Unlike WHERE, HAVING operates after grouping and aggregation, allowing reference to aliases created within the query:

SELECT *, AVG(rev_rating) AS avg_rating
FROM reviews
GROUP BY rev_id
HAVING avg_rating > 5;

Here, avg_rating is correctly referenced in HAVING.

To achieve similar filtering in the WHERE clause, you must replace the alias with its complete expression:

SELECT *
FROM reviews
WHERE (SUM(reviews.rev_rating) / COUNT(reviews.rev_id)) > 5;

Important Considerations:

The WHERE clause doesn't support all expressions, particularly aggregate functions like SUM() or AVG() used within aliases. In such cases, HAVING is essential.

As the MySQL documentation explains, referencing column aliases in WHERE is disallowed because their values may not be computed at the time of WHERE clause execution.

The above is the detailed content of Can I Use Aliases in MySQL's WHERE Clause?. For more information, please follow other related articles on the PHP Chinese website!