Query processing order

1. Query processing order

Welcome to Chapter 4!

2. Why does processing order matter?

Knowing the order in which a SQL query is executed can help you a great deal in optimizing your queries. This is especially true with large and complex queries where knowing the order of execution can save you from unwanted results, and help you create queries that execute faster.

3. Example

Let’s look at the following query. You may think that the query is read in the way that we type it, but Oracle, and other RDBMSs too, does not read from top to bottom. It translates our SQL statements into something it can understand and execute.

4. Example

First, Oracle needs to know where it needs to get the data from. The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried. In this case we want to query the Invoice column.

5. Example

Now that we have the working data set in place, we can filter the data according to the conditions in the WHERE clause. In the example we filter on rows where the billing city is not Paris.

6. Example

Now that we filtered the data set using the WHERE clause, we can aggregate the data according to one or more columns by using the GROUP BY clause. You can now use aggregation functions to return a per-group values. Here we group the data by billing country.

7. Example

Now that we have grouped the data using the GROUP BY clause, we can use the HAVING clause to filter out some groups. Since Oracle already knows the groups, you can use aggregation functions in the HAVING clause. In this case, we only keep billing countries for which the average total invoice amount is higher than 100 dollars.

8. Example

Now that we are done with discarding rows from the dataset and grouping the data, we can select the columns we want to be returned. In the example, the billing country and the average invoice amount are selected.

9. Example

Sorting takes place once the database has the entire result set ready. Here we order by the average invoice amount, in descending order.

10. What could go wrong?

Now that you know the order in which Oracle processes your query, let's look at some things that could go wrong. First, aliases created in the SELECT list cannot be used by earlier steps. Since the WHERE, GROUP BY, and HAVING clauses are evaluated before SELECTing, Oracle doesn't know the Average alias yet. But, note that since ORDER BY is evaluated after SELECT, you can use aliases in the ORDER BY statement.

11. What could go wrong?

Another common pitfall would be to try and filter out aggregated values in the WHERE clause. For example, calculating the average of Total in the WHERE clause won't work since Oracle doesn't know the groups to aggregate over yet. To apply filtering conditions on aggregated data, you should use the HAVING clause. At that point, Oracle knows that the data is grouped by billing country and it can calculate the average invoice amount for each country.

12. What could go wrong?

You can no longer access the original rows in the HAVING clause since the data is already grouped. You can only apply conditions to filter entire groups, and not single rows in a group. Single rows can be filtered out in the WHERE clause. Here only rows where the billing country is not equal to Paris are kept in the data, before grouping by billing country.

13. Query order of execution

Here is an overview of the order in which SQL operators and clauses in a query are executed. You'll notice that DISTINCT and the set operators are evaluated before ordering.

14. Let's practice!

Are you getting the hang of it? It's time for you to try it out.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.