SQL queries don't start with SELECT (jvns.ca)

Article summary

The article discusses the order in which SQL queries are executed, highlighting that the typical syntax starting with SELECT is not the actual order of execution. The author presents a diagram showing the correct order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, and LIMIT. This order is important for understanding how to write valid SQL queries and how database engines optimize query execution. The article also notes that while this order is useful for understanding SQL semantics, database engines may execute queries in a different order due to optimizations.

Main themes

• SQL query execution order
• Database engine optimizations
• Query syntax and semantics
• LINQ and other query languages
• Database performance and optimization

What commenters say

• The SQL syntax order is counterintuitive and should be changed to match the logical order of query execution.
• Database engines' optimization strategies can sometimes lead to unexpected performance issues, and query hints or other workarounds may be necessary.
• The order of query execution is important for understanding how to write valid and efficient SQL queries, but it is not always the same as the order in which the database engine executes the query.
• Some query languages, such as LINQ, have a syntax order that corresponds to the logical order of query execution, making them more composable and easier to use.
• The use of temporary tables or common table expressions (CTEs) can be a useful optimization strategy in certain cases, but may not always be the best approach.
• Database engines' ability to optimize queries can be both a blessing and a curse, as it can lead to unexpected performance issues if not properly understood and managed.