MySQL query / clause execution order

by

The actual execution of MySQL statements is a bit tricky. However, the standard does specify the order of interpretation of elements in the query. This is basically in the order that you specify, 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.

As for actual execution, that is really left up to the optimizer. For instance:

. . .
GROUP BY a, b, c
ORDER BY NULL

and

. . .
GROUP BY a, b, c
ORDER BY a, b, c

both have the effect of the ORDER BY not being executed at all — and so not executed after the GROUP BY (in the first case, the effect is to remove sorting from the GROUP BY and in the second the effect is to do nothing more than the GROUP BY already does).

More Related Contents:

  1. Mysql or/and precedence?
  2. Why the order of evaluation for expressions involving user variables is undefined?
  3. Can we use CONCAT and JOIN together in MySQL? [closed]
  4. MySQL pivot table query with dynamic columns
  5. Finding duplicate values in MySQL
  6. Error installing mysql2: Failed to build gem native extension
  7. Create a temporary table in a SELECT statement without a separate CREATE TABLE
  8. MySQL : isn’t in GROUP BY
  9. How do you extract a numerical value from a string in a MySQL query?
  10. SQL: Creating a Relational table with 2 different auto_increment
  11. CURRENT_DATE/CURDATE() not working as default DATE value
  12. How do I add a custom CHECK constraint on a MySQL table?
  13. MySql Count cannot show 0 values
  14. How to store uuid as number?
  15. Cannot change column used in a foreign key constraint
  16. Update row with data from another row in the same table
  17. using (-) dash in mysql table name
  18. MySQL password function
  19. How can I enable MySQL’s slow query log without restarting MySQL?
  20. How to select the most recent set of dated records from a mysql table
  21. MySQL database with unique fields ignored ending spaces
  22. Does MySQL allows to create database with dot?
  23. Selecting multiple columns/fields in MySQL subquery
  24. How to rearrange MySQL columns?
  25. MySql Query Replace NULL with Empty String in Select
  26. how to trim leading zeros from alphanumeric text in mysql function
  27. What are the max number of allowable parameters per database provider type?
  28. How to handle a query with special characters / (forward slash) and \ (backslash)
  29. Does MySQL support historical date (like 1200)?
  30. mySQL query to search all tables within a database for a string?

Leave a Comment