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

SELECT TOP 5 department, COUNT(*) AS employee_count
FROM employees
WHERE salary > 10000
GROUP BY department
HAVING COUNT(*) > 10
ORDER BY employee_count DESC;
  1. FROM: Reads the employees table.
  2. WHERE: Filters rows with salary > 10000.
  3. GROUP BY: Groups rows by the department column.
  4. HAVING: Retains groups where COUNT(*) > 10.
  5. SELECT: Selects department and the computed employee_count.
  6. ORDER BY: Sorts the result by employee_count in descending order.
  7. LIMIT: Returns the top 5 rows.

Understanding this order helps in optimizing queries and ensuring proper execution.