SQL
SQL query order of execution
Understand how SQL queries run internally
SQL statements are executed in a specific lexical execution order, which determines how SQL queries are processed internally. Although SQL is written in a declarative way (i.e., not in the execution order), the SQL engine follows a distinct sequence. Hereβs the lexical execution order for a typical SQL SELECT
statement:
1. FROM
- The source tables or joins between tables are identified.
- Any subqueries in the
FROM
clause are executed first. - Temporary tables may be created to handle derived tables or subqueries.
2. WHERE
- Filters rows from the
FROM
clause based on specified conditions. - This step excludes rows that do not meet the condition.
3. GROUP BY
- Groups rows into subsets based on column(s) specified.
- Aggregate functions (e.g.,
SUM
,MAX
,COUNT
) operate on these groups.
4. HAVING
- Filters groups formed in the
GROUP BY
clause based on specified conditions. - Similar to
WHERE
, but operates on grouped data.
5. SELECT
- Projects columns or expressions to return the desired data.
- Includes computed columns and uses aggregate functions.
6. DISTINCT
- Eliminates duplicate rows in the result set, if specified.
7. ORDER BY
- Sorts the result set based on specified column(s) or expressions.
- Can be ascending (
ASC
) or descending (DESC
).
8. LIMIT / FETCH / TOP
- Restricts the number of rows returned to the client.
- Syntax depends on the database system (e.g.,
LIMIT
in MySQL,TOP
in SQL Server).
Example Query Execution Breakdown
- FROM: Reads the
employees
table. - WHERE: Filters rows with
salary > 10000
. - GROUP BY: Groups rows by the
department
column. - HAVING: Retains groups where
COUNT(*) > 10
. - SELECT: Selects
department
and the computedemployee_count
. - ORDER BY: Sorts the result by
employee_count
in descending order. - LIMIT: Returns the top 5 rows.
Understanding this order helps in optimizing queries and ensuring proper execution.