Query order
1. Query order
If SQL syntax is not in a specific order in the query, running the query will raise an error. The order of the syntax, as written in the query, is different from the processing order in the database. In this lesson, we'll examine the effects of query processing order in the database versus the order of SQL syntax in a query.2. Big earthquakes query
Here's a simple query that returns all earthquakes with a magnitude of 9 or more.3. Syntax order
The syntax in this query, from start to the end, begins with SELECT, identifying the columns to be returned: Country, Place, and Magnitude.4. Syntax order
We identify the name of the data source next with FROM, which is the Earthquakes table.5. Syntax order
We apply a filtering condition on the Earthquakes table with WHERE in this case Magnitude greater than or equal to 9.6. Syntax order
The query ends with ORDER BY which orders the final results. In the example, this is the largest magnitude descending.7. Processing order
In contrast, the processing order of the query in the database is different. The data source is processed first with FROM.8. Processing order
The filtering condition is applied next by processing WHERE.9. Processing order
Only when the data has been extracted and filtered with FROM and WHERE is SELECT processed, telling the database what columns to return from the data that was extracted and filtered.10. Processing order
Once we have our required rows and columns, ORDER BY can be processed to sort the final output.11. Processing errors
This query has several errors, and we'll use to demonstrate how a query is processed. When a query is run it returns a single error message only. The error messages reflect the processing order. Only after correcting an error in a preceding syntax can the following SQL syntax be processed. Take note of each error message.12. Processing FROM
FROM is processed first and because there is no table in the database called LargeEarthquakes it cannot be processed. It should be Earthquakes.13. Processing WHERE
WHERE is processed next. There is no column in the Earthquakes table called Strength for WHERE to apply a filter condition. It Should be Magnitude.14. Processing SELECT
SELECT is only processed after FROM and WHERE. The column PlaceName cannot be selected for the output because it does not exist. It's is a mistake, it should be Place, not PlaceName.15. Processing ORDER BY
ORDER BY is processed last. Magnitud is misspelled.16. Error free
Finally, once all the errors fixed, it runs as expected.17. Logical processing order
Here is the logical processing order for the most commonly used SQL Syntax in a query. This is the order the database processes the syntax in a query. Note, how far down the order SELECT occurs. We can imagine that all processing before SELECT is concerned with finding, merging, aggregating and filtering the data. Processing after SELECT is more concerned with actions on the final data extracted. The syntax after SELECT tends to be quite expensive on the database processing resources and we should only use where necessary. We'll look at this in a later chapter. As data scientists, working on real-world, large data sets, having an understanding of the processing order can help determine why a query will not execute and also help to look at ways to optimize the query for performance.18. Let's practice
Let's practice.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.