Tuesday, July 18, 2017

Using alias in WHERE clause in MySQL - understanding how queries are parsed

I had written a very long query where I was using an alias and needed to be able to filter the query on the alias. Turns out you cannot use an alias in a WHERE clause. BUT you can use a HAVING clause which can be made to mimic certain aspects of a WHERE clause.

https://stackoverflow.com/questions/200200/can-you-use-an-alias-in-the-where-clause-in-mysql

In relation to this, it can be important to understand how queries are parsed:

https://stackoverflow.com/questions/24127932/mysql-query-clause-execution-order

From the above post on stack overflow:

Order that queries are executed, although I think HAVING and GROUP BY could come after SELECT:

FROM clause
WHERE clause
SELECT clause
GROUP BY clause
HAVING clause
ORDER BY clause

This is important for understanding how queries are parsed. You cannot use a column alias defined in a SELECT in the WHERE clause, for instance, because the WHERE is parsed before the SELECT. On the other hand, such an alias can be in the ORDER BY clause.

And another blog post on SQL Query Order of Operations:

https://www.bennadel.com/blog/70-sql-query-order-of-operations.htm


No comments:

Post a Comment