Want to learn more? Take the full course at at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work. ---
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.
Here's a simple query that returns all earthquakes with a magnitude of 9 or more.
The syntax in this query, from start to the end, begins with SELECT, identifying the columns to be returned: Country, Place, and Magnitude.
We identify the name of the data source next with FROM, which is the Earthquakes table.
We apply a filtering condition on the Earthquakes table with WHERE in this case Magnitude greater than or equal to 9.
The query ends with ORDER BY which orders the final results. In the example, this is the largest magnitude descending.
In contrast, the processing order of the query in the database is different. The data source is processed first with FROM.
The filtering condition is applied next by processing WHERE.
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.
Once we have our required rows and columns, ORDER BY can be processed to sort the final output.
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.
FROM is processed first and because there is no table in the database called LargeEarthquakes it cannot be processed. It should be Earthquakes.
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.
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.
ORDER BY is processed last. Magnitud is misspelled.
Finally, once all the errors fixed, it runs as expected.
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.
Let's practice.
#SQL #DataCamp #SQLTutorial #Query #Performance #SQLServer
0 Comments