Chapter 2 Basics

2.1 Order of Execution

  1. FROM clause

SQL begins by identifying and combining all relevant data from the specified tables. This includes any joins or subqueries. The FROM clause defines the data source and sets the foundation for the rest of the query.

  1. JOIN clause

If there are any JOIN clauses, SQL processes them immediately after identifying the tables in the FROM clause. The joining of tables determines which rows from the combined tables are considered for further steps.

  1. WHERE clause

After combining tables, SQL applies the WHERE clause to filter out rows that do not meet the criteria. The result is a reduced dataset that satisfies the condition(s).

  1. GROUP BY clause

SQL groups the filtered rows based on the columns specified in the GROUP BY clause. This is necessary for aggregate functions like COUNT, SUM, AVG, etc.

  1. HAVING or QUALIFY clause

  • After the GROUP BY clause, SQL applies the HAVING clause to filter the grouped results. Unlike WHERE, which filters rows before grouping, HAVING filters groups after they have been formed.

  • The window functions like RANK() are executed before filtering by QUALIFY. The QUALIFY clause then filters based on the results of these window functions.

  • SELECT After QUALIFY: The SELECT clause is applied last, selecting the columns from the result set after the filtering is done by QUALIFY.

  1. SELECT clause

SQL evaluates the SELECT clause after all filtering, grouping, and having conditions have been applied. It determines which columns or expressions are included in the final output.

  1. DISTINCT clause

SQL applies the DISTINCT clause after the SELECT clause, ensuring that only unique rows are returned.

  1. ORDER BY clause

SQL orders the final result set according to the columns specified in the ORDER BY clause. This is the last step in query execution before the result set is returned.

  1. LIMIT - OFFSET - TOP

SQL applies these clauses after all other operations have been completed. LIMIT defines how many rows to return, while OFFSET skips a specified number of rows.