What's the Exact Execution Order of SQL Clauses: FROM, WHERE, ORDER BY, and TOP?

Order of Execution for an SQL Statement

When executing an SQL statement, the order of execution for its clauses is crucial. Let's examine the execution order of a statement that combines a TOP clause, a WHERE clause, and an ORDER BY clause:

SELECT TOP 5 C.CustomerID,C.CustomerName,C.CustomerSalary
FROM Customer C
WHERE C.CustomerSalary > 10000
ORDER BY C.CustomerSalary DESC

According to the SELECT statement documentation, the logical processing order is:

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. TOP

However, it's important to note that the actual physical execution order may vary depending on the query processor.

Explanation:

• FROM (Customer C): First, the database engine identifies and accesses the Customer table.
• WHERE (C.CustomerSalary > 10000): Next, the engine applies the filter condition, selecting only rows where the CustomerSalary exceeds 10000.
• ORDER BY (C.CustomerSalary DESC): After retrieving the filtered data, the results are sorted in descending order of CustomerSalary.
• TOP 5: Finally, the TOP clause limits the number of rows returned to the top 5, as specified in the ORDER BY clause.

Therefore, the execution order follows a logical sequence, filtering the data based on the WHERE clause, sorting it as specified in the ORDER BY clause, and finally retrieving the top results limited by the TOP clause.

The above is the detailed content of What's the Exact Execution Order of SQL Clauses: FROM, WHERE, ORDER BY, and TOP?. For more information, please follow other related articles on the PHP Chinese website!