术语表

• CTE：common table expression，公共表表达式。

简介

WITH 提供了一种方式来书写在一个大型查询中使用的辅助语句。
这些语句通常被称为公共表表达式 或 CTE，它们可以被看成是仅在单个 SQL语句（ SELECT、INSERT、UPDATE 或 DELETE）的执行范围内存在中存在的临时表。

在 WITH 子句中的每一个辅助语句可以是一个 SELECT、INSERT、UPDATE 或 DELETE，并且 WITH 子句本身也可以被附加到一个主语句，主语句也可以是 SELECT、INSERT、UPDATE 或 DELETE。

语法

WITH cte_name AS (
    query
)
SELECT * FROM cte_name;

案例

在合并两张表之前先进行筛选，尽可能删减掉不需要的行，只保留需要的行，这样编写出的 SQL 更高效。

以下的 SQL 是非常低效的，因为它先合并两个表，之后才对 “2017 年 1 月 9 日” 之后的时间进行筛选：

SELECT *
FROM table_a a
JOIN table_b b
ON a.username = b.username
WHERE a.day >= '2017-09-01' and b.day >= '2017-09-01'

正确的表达方式是在合并表之前使用CTEs进行筛选，如下：

WITH a AS (
     SELECT *
     FROM table_a
     WHERE day >= '2017-09-01'),

b AS (
     SELECT *
     FROM table_b
     WHERE day >= '2017-09-01');

SELECT *
FROM a
JOIN b
ON a.username=b.username;

比如 table_a 和 table_b 各有 1000 条数据，day >= '2017-09-01' 的数据为 10 条。

• 【先连表再过滤】要处理 1000 * 1000 = 100w 条数据
• 【先过滤再连表】只要处理 10 * 10 = 100 条数据，包含两次过滤的数据量 200，一共 300 条数据。

正常人都知道，100 条数据处理比 100w 条数据处理块，所以【先过滤再连表】比【先连表再过滤】效率更高。

参考

• 7.8. WITH查询（公共表表达式）
• SQL---公共表表达式（CTEs）
• MySQL CTE(公共表表达式)