What is the order of execution of various clauses or parts of SQL SELECT statement? In what order a SELECT query is executed in SQL? Why SELECT clause is executed at the end in most of the SELECT statements? Order of execution of SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY and FETCH clauses
Order of execution in SQL SELECT query
SELECT query in SQL is the most used query to access the database. It has the following clauses (or parts) which can be found in most of the DBMSs like Oracle, MySQL, SQL Server etc.
- SELECT clause
- FROM clause
- WHERE clause
- GROUP BY clause
- HAVING clause
- Order By clause
SELECT query, when executed, follows certain order to fetch the records as result to the user. The table given below discusses about the order of execution of various clauses of SELECT query.
|
Order of execution |
Clause |
Description |
Presence
|
|
1 |
FROM |
SELECT query identifies the records in one (or more tables) as per the conditions (if any) and displays the result to the user. Hence, the first task is to identify the base table from which the record to be fetched. |
Must |
|
1a |
JOIN |
JOIN is a binary operator to combine rows from two tables and it is a part of FROM clause if used in the query. Hence, it is in position 2 in the order of execution. JOIN can be used in the FROM clause of a query as INNER JOIN, NATURAL JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, CARTESIAN JOIN (‘,’ (comma) between table names is used as the symbol for CARTESIAN JOIN). |
Optional |
|
2 |
WHERE |
Once you have chosen the base table(s), now it is the time to mention the filtering constraints (conditions). If your query has any, it is mentioned in the WHERE clause. After choosing records from base tables, we need to filter them based on certain values as per your requirement. Hence, after FROM (or JOIN) clause, WHERE clause will be executed next. |
Optional |
|
3 |
GROUP BY |
If you like to group the result based on certain attributes and their values, GROUP BY clause has to be used. It is a way to specify ‘HOW’ I want to see my data in the result. It executes after FROM (or JOIN, or WHERE) clause. You typically use a GROUP BY clause in conjunction with an aggregate expression (eg. AVG(), SUM(), etc.). |
|
|
4 |
HAVING |
HAVING clause is used along with GROUP BY clause in case if you like to apply conditions (filters) on grouped data. For example, assume that you have counted number of employees in each department (using COUNT(*) and ‘GROUP BY did’) and now you want to include that departments that have more than 5 employees. Here, we use HAVING clause. |
Optional |
|
5 |
SELECT |
SELECT will take parameters (* OR list of columns OR functions OR arithmetic operations). As per the presence of parameters in the query, SELECT will pick and show necessary data from the results produced by the execution of previous clauses. For example, the SELECT clause in “SELECT sname, age FROM student WHERE gender = ‘F’;” takes two columns. SELECT will select and show only sname and age columns after the execution of FROM clause and WHERE clause are completed. |
Must |
|
6 |
ORDER BY |
ORDER BY clause sorts (ascending or descending) the result data produced by SELECT query. |
Optional |
|
7 |
TOP / LIMIT / FETCH |
They are used to limit the number of records to be displayed as a result. TOP is used by SQL Server, LIMIT is supported by MySQL, and Oracle (versions 12 or later) supports FETCH. |
Optional |
Examples - Order of execution of SQL clauses in SELECT query:
Example 1:
