Get startedGet started for free

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.