Execution Order of SQL Statement

Posted on In

Understand SQL statements is very important for database developers. In this article, we will discuss the execution order of SQL statements.

Execution Order of SQL Statement

The execution order of an SQL statement is not the same as the order in which the clauses are written. Here’s the typical execution order:

  1. FROM clause including JOINs
  2. WHERE clause
  3. GROUP BY clause
  4. HAVING clause
  5. SELECT clause
  6. DISTINCT clause
  7. ORDER BY clause
  8. LIMIT / OFFSET clauses

This order is important to understand because it can affect the results of your queries, especially when using aliases or when using aggregate functions. For example, you can’t use an alias defined in your SELECT clause within your WHERE clause because the WHERE clause is executed before the SELECT clause.

FROM clause including JOINs

The FROM clause is the first clause to be executed in an SQL statement. It specifies the tables from which the data will be retrieved. If you are using JOINs, the JOINs are also executed at this stage.

The execution of a JOIN statement in a database involves combining rows from two or more tables based on a related column between them.

Example

1
2
3
4
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Orders.OrderDate > '2022-01-01';

In this example, the JOIN statement cause the database to combine rows from the Orders and Customers tables based on the CustomerID column. The result is a new temporary table that contains the columns OrderID and CustomerName.

Database engines use different algorithms to execute JOIN statements, such as nested loops, hash joins, and merge joins. The choice of algorithm depends on the size of the tables, the indexes available, and other factors. JOINs are very common in SQL queries, database engines are optimized to execute them efficiently.

WHERE clause

The WHERE clause is used to filter the rows returned by the FROM clause. It is executed after the FROM clause and JOINs.

GROUP BY clause

The GROUP BY clause is used to group the rows returned by the FROM and WHERE clauses. It is executed after the WHERE clause.

HAVING clause

The HAVING clause is used to filter the groups returned by the GROUP BY clause. It is executed after the GROUP BY clause.

SELECT clause

The SELECT clause is used to specify the columns that will be returned by the query. It is executed after the HAVING clause.

DISTINCT clause

The DISTINCT clause is used to remove duplicate rows from the result set. It is executed after the SELECT clause.

ORDER BY clause

The ORDER BY clause is used to sort the rows returned by the query. It is executed after the SELECT and DISTINCT clauses.

LIMIT / OFFSET clauses

The LIMIT and OFFSET clauses are used to limit the number of rows returned by the query and to skip a certain number of rows. They are executed after the ORDER BY clause.

Conclusion

Understanding the execution order of SQL statements is important for writing efficient queries and getting the results you expect. By knowing the order in which the clauses are executed, you can avoid common pitfalls and write more effective queries.