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, & 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 lớn following a recipe: you need lớn know the ingredients and what to bởi vì with ingredients, but you also need lớn know in which order bởi vì the tasks. If the database follows a different order of operations, the performance of the query can decrease dramatically.

Bạn đang xem: Sql order of operations

The best way to lớn learn SQL order of operations is through practice. I recommend myalbum.vn"s SQL Practice track. It contains over 600 hands-on exercises lớn practice your SQL skills. Or select the best course for you from over 30 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 và belongs to a department, as we can see in the following tables.

Xem thêm: Cách Tải Và Chơi Công Thành Xưng Đế: Trang Chủ, Công Thành Xưng Đế

Sample of the EMPLOYEE table:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100James Smith 78,000 ACCOUNTING
101Mary Sexton82,000 IT
102Chun Yen 80,500 ACCOUNTING
103Agnes Miller95,000 IT
104DmitryKomer 120,000SALES

Sample of the DEPARTMENT table:

DEPT_NAMEMANAGERBUDGET
ACCOUNTING100300,000
IT 101250,000
SALES 104700,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 khổng lồ 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
100James Smith 78,000 ACCOUNTING
101Mary Sexton82,000 IT
102Chun Yen 80,500 ACCOUNTING
103Agnes Miller95,000 IT
104DmitryKomer 120,000SALES

Second, we execute WHERE DEPARTMENT = "IT", which narrows it down khổng lồ this:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
101Mary Sexton82,000IT
103AgnesMiller95,000IT

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

FIRST_NAMELAST_NAME
Mary Sexton
AgnesMiller

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

*

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 lớn fix it, you need to địa chỉ an ORDER BY clause khổng lồ the previous query:

SELECT LAST_NAME, FIRST_NAME FROM EMPLOYEE WHERE DEPARTMENT = "IT"ORDER BY FIRST_NAMEThe 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
Mary Sexton

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

*

Adding GROUP BY & HAVING Clauses to lớn the Query

In this example, we will use a query with GROUP BY. Suppose we want lớn obtain how many employees in each department have a salary higher than 80,000, và 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 > 80000GROUP BY DEPARTMENTORDER BY COUNT(*) DESCAgain, we first execute FROM EMPLOYEE, which retrieves this data:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100James Smith 78,000 ACCOUNTING
101Mary Sexton82,000 IT
102Chun Yen 80,500 ACCOUNTING
103Agnes Miller95,000 IT
104DmitryKomer 120,000SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
101Mary Sexton82,000 IT
102Chun Yen 80,500 ACCOUNTING
103Agnes Miller95,000 IT
104DmitryKomer 120,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
IT 2
SALES 1

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

DEPARTMENTCOUNT(*)
IT 2
ACCOUNTING1
SALES 1

The order of execution in this example is:

*

In this next example, we will địa chỉ cửa hàng the HAVING clause. HAVING is not as commonly used in SQL as the other clauses we"ve looked at so far. The best way lớn describe HAVING is that it"s lượt thích the WHERE clause for GROUP BY. In other words, it is a way khổng lồ filter or discard some of the groups of records created by GROUP BY.

Suppose we now want lớn 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) > 80000Again, we first execute FROM EMPLOYEE, which retrieves this data:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100 James Smith 78,000 ACCOUNTING
101 Mary Sexton 82,000 IT
102 Chun Yen 80,500 ACCOUNTING
103 Agnes Miller 95,000 IT
104 Dmitry Komer 120,000 SALES

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

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100 James Smith 78,000 ACCOUNTING
101 Mary Sexton 82,000 IT
102 Chun Yen 80,500 ACCOUNTING
103 Agnes Miller 95,000 IT

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

DEPARTMENTAVG(SALARY)
ACCOUNTING 79,250
IT 88,500

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

DEPARTMENTAVG(SALARY)
IT 88,500

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

DEPARTMENT
IT

The order of execution in this example is:

*

Adding a New Operation: The JOIN Clause

The previous examples dealt with one table. Let"s showroom a second table using the JOIN clause. Suppose we want lớn 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 > 275000Again, we first execute FROM EMPLOYEE, which retrieves this data:

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENT
100James Smith 78,000 ACCOUNTING
101Mary Sexton82,000 IT
102Chun Yen 80,500 ACCOUNTING
103Agnes Miller95,000 IT
104DmitryKomer 120,000SALES

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENTDEPT_NAMEMANAGERBUDGET
100James Smith 78,000 ACCOUNTINGACCOUNTING100300,000
101Mary Sexton82,000 IT IT 101250,000
102Chun Yen 80,500 ACCOUNTINGACCOUNTING100300,000
103Agnes Miller95,000 IT IT 101250,000
104DmitryKomer 120,000SALES SALES 104700,000

EMPLOYEE_IDFIRST_NAMELAST_NAMESALARYDEPARTMENTDEPT_NAMEMANAGERBUDGET
100James Smith78,000 ACCOUNTINGACCOUNTING100300,000
102Chun Yen 80,500 ACCOUNTINGACCOUNTING100300,000
104DmitryKomer120,000SALES SALES 104700,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:

*

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:

*

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 lớn explore. Kiểm tra out this online course lớn enter the fascinating world of SQL, và see where it can take you!