SQLBolt：https://sqlbolt.com/lesson/introduction

• SQL Lesson 12: Order of execution of a Query

使用語法 & 概念

本章節介紹的是 SQL 語句的執行順序，這是我們去理解、解讀、使用 SQL 時相當重要的概念。

-- Complete SELECT query
SELECT DISTINCT column, AGG_FUNC(column_or_expression), …
FROM mytable
    JOIN another_table
      ON mytable.column = another_table.column
    WHERE constraint_expression
    GROUP BY column
    HAVING constraint_expression
    ORDER BY column ASC/DESC
    LIMIT count OFFSET COUNT;

大的方向是：先是資料的來源、分組，之後才是資料的選取與排序。

所以要理解 SQL 的語意，不應該照習慣寫法的順序去讀，而是要照它如何定義資料的順序去解讀。

章節定義的排序如下：

FROM and JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT / OFFSET

SQL Lesson 12: Order of execution of a Query

題目 1

-- 1. Find the number of movies each director has directed
SELECT director, count(director) -- 3
FROM movies  -- 1
GROUP BY director; -- 2

題目 2

-- 2. Find the total domestic and international sales that an be attributed to each director
SELECT director, SUM(domestic_sales + international_sales) -- 3
FROM movies JOIN boxoffice -- 1
ON movies.id = boxoffice.movie_id -- 1
GROUP BY director; --2 

排序說明

1. FROM and JOINs
   The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried. This includes subqueries in this clause, and can cause temporary tables to be created under the hood containing all the columns and rows of the tables being joined.

2. WHERE
   Once we have the total working set of data, the first-pass WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded. Each of the constraints can only access columns directly from the tables requested in the FROM clause. Aliases in the SELECT part of the query are not accessible in most databases since they may include expressions dependent on parts of the query that have not yet executed.

3. GROUP BY
   The remaining rows after the WHERE constraints are applied are then grouped based on common values in the column specified in the GROUP BY clause. As a result of the grouping, there will only be as many rows as there are unique values in that column. Implicitly, this means that you should only need to use this when you have aggregate functions in your query.

4. HAVING
   If the query has a GROUP BY clause, then the constraints in the HAVING clause are then applied to the grouped rows, discard the grouped rows that don't satisfy the constraint. Like the WHERE clause, aliases are also not accessible from this step in most databases.

5. SELECT
   Any expressions in the SELECT part of the query are finally computed.

6. DISTINCT
   Of the remaining rows, rows with duplicate values in the column marked as DISTINCT will be discarded.

7. ORDER BY
   If an order is specified by the ORDER BY clause, the rows are then sorted by the specified data in either ascending or descending order. Since all the expressions in the SELECT part of the query have been computed, you can reference aliases in this clause.

8. LIMIT / OFFSET
   Finally, the rows that fall outside the range specified by the LIMIT and OFFSET are discarded, leaving the final set of rows to be returned from the query.

小結

到今天為止，關於查詢語句（CRUD, READ）的部分就介紹完了，之後就是就是關於如何新增（Create）、修改（UPDATE）、刪除（DELETE）行（record）與表（table）的部分囉。

參考資料：

• SQL Lesson 12: Order of execution of a Query