SQL Order of Operations

SQL is not a traditional programming language in which you write a sequence of instructions in a given order of execution. Instead, SQL is a "declarative" language, which means that by writing a SQL query, you declare what data you expect as a result of the query, but you don't indicate how to obtain it.

What is the Order of Operations in SQL?

By using examples, we will explain the execution order of the six most common operations or pieces in an SQL query. Because the database executes query components in a specific order, it's helpful for the developer to know this order. It's similar to following a recipe: you need to know the ingredients and what to do with ingredients, but you also need to know in which order do the tasks. If the database follows a different order of operations, the performance of the query can decrease dramatically.

The best way to learn SQL order of operations is through practice. I recommend LearnSQL.com's SQL Practice track. It contains over 1000 hands-on exercises to practice your SQL skills. Or select the best course for you from over 70 interactive SQL courses we offer at various levels of proficiency.

The Employee Database

In this article, we will work with a database for a typical company that has employees distributed in different departments. Each employee has an ID, a name, and a salary and belongs to a department, as we can see in the following tables.

Sample of the employee table:

Sample of the department table:

In this article, we will also use frequent SQL queries used in a company: "Obtain the names of employees working for the IT department" or "Obtain the number of employees in each department with a salary higher than 80.000." For each of these queries, we will analyze the order of execution of its components.

FROM, WHERE, SELECT

Let's start with a simple query to obtain the names of the employees in the IT department:

SELECT last_name, first_name
FROM employee
WHERE department = 'IT'

First, we execute FROM employee, which retrieves this data:

Second, we execute WHERE department = 'IT', which narrows it down to this:

Finally, we apply SELECT first_name, last_name, producing the final result of the query:

Great! After completing our first query dissection, we can conclude that the order of execution for simple queries with SELECT, FROM, and WHERE is:

ORDER BY

Suppose your boss receives a report based on the query in the previous example and rejects it, because the employee names are not in alphabetical order. To fix it, you need to add an ORDER BY clause to the previous query:

SELECT last_name, first_name
FROM employee
WHERE department = 'IT'
ORDER BY first_name

The execution process of this query is almost the same as in the previous example. The only change is at the end, when the ORDER BY clause is processed. The final result of this query orders the entries by FIRST_NAME, as shown below:

So, if we have SELECT with FROM, WHERE, and ORDER BY, the order of execution is:

GROUP BY

In this example, we will use a query with GROUP BY. Suppose we want to obtain how many employees in each department have a salary higher than 80,000, and we want the result in descending order by the number of people in each department. The query for this situation is:

SELECT department, COUNT(*)
FROM employees
WHERE salary > 80000
GROUP BY department
ORDER BY COUNT(*) DESC

Again, we first execute FROM employee, which retrieves this data:

Second, we execute WHERE salary > 80000, which narrows it down to this:

Third, GROUP BY is applied, generating one record for each distinct value in the GROUP BY columns. In our example, we create one record for each distinct value in DEPARTMENT:

Fourth, we apply SELECT with COUNT(*), producing this intermediate result:

Finally, we apply the ORDER BY clause, producing the final result of the query:

The order of execution in this example is:

In this next example, we will add the HAVING clause. HAVING is not as commonly used in SQL as the other clauses we've looked at so far. The best way to describe HAVING is that it's like the WHERE clause for GROUP BY. In other words, it is a way to filter or discard some of the groups of records created by GROUP BY.

HAVING

Suppose we now want to obtain all the departments, except the SALES department, with an average salary higher than 80,000. The query for this situation is:

SELECT department
FROM employees
WHERE department <> 'SALES'
GROUP BY department
HAVING AVG(salary) > 80000

Again, we first execute FROM employee, which retrieves this data:

Second, the WHERE clause, excluding the SALES records, is processed:

Third, GROUP BY is applied, generating the following records:

Fourth, HAVING AVG(SALARY) > 80000 is applied to filter the group of records generated by GROUP BY:

Finally, the SELECT clause is applied, producing the final result of the query:

The order of execution in this example is:

JOIN

The previous examples dealt with one table. Let's add a second table using the JOIN clause. Suppose we want to obtain the last names and employee IDs of employees working for departments with a budget higher than 275,000. The query for this situation is:

SELECT employee_id, last_name
FROM employees
JOIN department
ON department = dept_name
WHERE budget > 275000

Again, we first execute FROM employee, which retrieves this data:

Second, we apply the JOIN clause generating a new intermediate result combining both tables:

Third, WHERE budget > 275000 is applied:

Finally, SELECT employee_id, last_name is executed, producing the final result of the query:

The order of execution in this example is:

The Order of Operations in SQL

In this article, we covered the execution order in SQL queries through examples. From these examples, we can see that there is an order of execution, but this order may vary depending on which clauses are present in the query. As a general guideline, the order of execution is:

However, if one of these clauses is not present, the order of execution will be different.

To master SQL in practice, check out our SQL Practice track that offer 10 different SQL practice courses and over 1000 exercises.