SQL Order of Operations

Asian business woman working with SQL at the office.

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.

Six Operations to Order: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY

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 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:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

Sample of the DEPARTMENT table:

DEPT_NAMEMANAGERBUDGET
ACCOUNTING100300,000
IT101250,000
SALES104700,000

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.

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:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

Second, we execute WHERE DEPARTMENT = ‘IT’, which narrows it down to this:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
101MarySexton82,000IT
103AgnesMiller95,000IT

Finally, we apply SELECT FIRST_NAME, LAST_NAME, producing the final result of the query:

FIRST_NAMELAST_NAME
MarySexton
AgnesMiller

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

SELECT, FROM, and WHERE

Changes in the Order of Operations If We Add 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:

FIRST_NAMELAST_NAME
AgnesMiller
MarySexton

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

SELECT with FROM, WHERE, and ORDER BY

Adding GROUP BY and HAVING Clauses to the Query

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:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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:

DEPARTMENT
ACCOUNTING
IT
SALES

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

DEPARTMENTCOUNT(*)
ACCOUNTING1
IT2
SALES1

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

DEPARTMENTCOUNT(*)
IT2
ACCOUNTING1
SALES1

The order of execution in this example is:

1-FROM 	2-WHERE 	3-GROUP BY    4-SELECT      5-ORDER BY

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.

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:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT

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

DEPARTMENTAVG(SALARY)
ACCOUNTING79,250
IT88,500

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

DEPARTMENTAVG(SALARY)
IT88,500

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

DEPARTMENT
IT

The order of execution in this example is:

1-FROM 	2-WHERE 	3-GROUP BY    4-HAVING      5-SELECT

Adding a New Operation: The JOIN Clause

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:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100JamesSmith78,000ACCOUNTING
101MarySexton82,000IT
102ChunYen80,500ACCOUNTING
103AgnesMiller95,000IT
104DmitryKomer120,000SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENTDEPT_NAMEMANAGERBUDGET
100JamesSmith78,000ACCOUNTINGACCOUNTING100300,000
101MarySexton82,000ITIT101250,000
102ChunYen80,500ACCOUNTINGACCOUNTING100300,000
103AgnesMiller95,000ITIT101250,000
104DmitryKomer120,000SALESSALES104700,000

Third, WHERE BUDGET > 275000 is applied:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENTDEPT_NAMEMANAGERBUDGET
100JamesSmith78.000ACCOUNTINGACCOUNTING100300,000
102ChunYen80,500ACCOUNTINGACCOUNTING100300,000
104DmitryKomer120,000SALESSALES104700,000

Finally, SELECT EMPLOYEE_ID, LAST_NAME is executed, producing the final result of the query:

EMPLOYEE_IDLAST_NAME
100Smith
102Yen
104Komer

The order of execution in this example is:

1-FROM 	2-JOIN 	3-WHERE    4-SELECT

Closing Words

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:

1-FROM    2-JOIN    3-WHERE   4-GROUP BY   5-HAVING    6-SELECT   7-ORDER BY

However, if one of these clauses is not present, the order of execution will be different. SQL is an easy, entry-level language, but once you are inside, there are a lot of exciting concepts to explore. Check out this online course to enter the fascinating world of SQL, and see where it can take you!

Ignacio L. Bisso

Ignacio is a database consultant from Buenos Aires, Argentina. He’s worked for 15 years as a database consultant for IT companies like Informix and IBM. These days, he teaches databases at Sarmiento University and works as a PostgreSQL independent SQL consultant. A proud father of four kids with 54 years in his backpack, Ignacio plays soccer every Saturday afternoon, enjoying every match as if it’s his last one.

comments powered by Disqus