通用表表达式（Common Table Expression）能够简化复杂的子查询和连接查询，实现树状结构数据的遍历，提高 SQL 语句的可读性和性能。



## 表即变量

```sql
WITH cte_name(col1, col2, ...) AS (
	subquery
)
SELECT * FROM cte_name;
```

- cte_name: CTE 的名称，后面是可选的字段名。
- AS 后的 subquery 用于定义 CTE。
- SELECT 是主查询，可以引用所定义的 CTE。还可以使用 INSERT、UPDATE、DELETE 等。

```sql
WITH t(n) AS (
	SELECT 1 -- oracle 需要使用 SELECT 1 FROM dual
)
SELECT n
FROM t;
```

> WITH 语句定义了一个变量，这个变量的值是一个表，所以称为通用表表达式。CTE 和临时表或子查询类似，可以用于 SELECT、INSERT、UPDATE 以及 DELETE 等语句。

```sql
-- 定义多个 CTE
WITH t1(n) AS (
	SELECT 1 -- oracle 需要使用 SELECT 1 FROM dual
),
t2(m) AS (
	SELECT n+1
	FROM t1 -- 可以引用前面的 CTE
)
SELECT t1.n, t2.m
FROM t1
CROSS JOIN t2;
```

## 强大的递归

```sql
WITH RECURSIVE cte_name AS (
	cte_query_initial -- 初始化部分
	UNION [ALL] -- 合并
	cte_query_iterative -- 递归部分，可以对当前 CTE 进行自我引用
) SELECT * FROM cte_name;
```

- 每一次递归查询语句执行的结果都会再次作为输入，传递给下一次查询。
- 如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归。
- oracle 和 mss 不支持 RECURSIVE，而直接使用 WITH 定义递归形式的CTE，同时它们必须使用 UNION ALL 运算符。
- sqlite 可以省略 RECURSIVE。

一些使用示例。

```sql
-- 递归生成1～10
-- mysql pg sqlite
WITH RECURSIVE t(n) AS
(
	SELECT 1
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;

-- oracle
WITH t(n) AS
(
	SELECT 1 FROM dual
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;

-- mss
WITH t(n) AS
(
	SELECT 1
	UNION ALL
	SELECT n + 1 FROM t WHERE n < 10
)
SELECT n FROM t;
```

1. 首先运行初始化语句，生成数字1。
2. 第1次运行递归部分，此时n等于1，生成数字2（n+1）​。
3. 第2次运行递归部分，此时n等于2，生成数字3。
4. 继续运行递归部分，直到n等于9，生成数字10。
5. 第10次运行递归部分，此时n等于10。由于不满足查询条件（WHERE n < 10）​，不返回任何结果，同时终止递归。
6. 最后，主查询语句返回t中的全部数据，也就是一个1～10的数字序列。

```sql
-- 遍历层次/树结构，自顶向下
-- mysql
WITH RECURSIVE employee_path (emp_id, emp_name, path) AS
(
	-- 初始部分
	SELECT emp_id, emp_name, emp_name AS path
	FROM employee
	WHERE manager IS NULL -- 老板，是树的根
	UNION ALL
	-- 递归部分
	SELECT e.emp_id, e.emp_name, CONCAT(ep.path, '->', e.emp_name)
	FROM employee_path ep
	JOIN employee e ON ep.emp_id = e.manager -- 当前层关联下一层
)
SELECT emp_name AS "员工姓名", path AS "管理路径"
FROM employee_path
ORDER BY emp_id;
```

- mysql 递归次数限制由 cte_max_recursion_depth 控制。
- pg sqlite 没有递归次数限制。
- oracle 能够检测出递归死循环。
- mss 默认递归100次，可以在查询中使用 MAXRECURSION 选项设置。
- 递归终止的 WHERE 条件要在 CTE 的定义中，不能通过主查询实现。

## 参考

- [理解 MySQL with recursive 的执行过程](https://blog.chives.life/post/mysql-with-recursive)

通用表表达式

包括并集（Union）、交集（Intersect）和差集（Except）。执行SQL集合运算时，集合操作中的两个查询结果需要满足以下条件：

- 两个查询结果集中字段的数量必须相同。
- 两个查询结果集中对应字段的类型必须匹配或兼容。

注意

- SQLite 使用动态数据类型，不要求字段类型匹配或兼容。
- 某些数据库（例如 MySQL）可能会尝试执行隐式的类型转换。



## 交集求同

![图8.1 交集求同](https://s2.loli.net/2024/12/12/ojKUMBx1W2sy8Ti.png)

```sql
SELECT column1, column2, ...
FROM table1
INTERSECT [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;
```

- DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
- ALL 表示保留合并结果中的重复记录。
- 默认值为 DISTINCT。
- mysql 不支持 INTERSECT。
- pg 完整支持 DISTINCT 和 ALL。
- oracle 21c 开始支持 ALL 选项。
- 其他数据库支持简写的 INTERSECT。

```sql
-- oracle mss pg sqlite
SELECT id, name
FROM t_set1
INTERSECT
SELECT id, name
FROM t_set2;
```

交集运算通常可以改写为[内连接](https://blog.chives.life/post/sql-join-types#%E5%86%85%E8%BF%9E%E6%8E%A5%20Inner%20Join)。

- mysql 可以用这种方法实现交集。

```sql
SELECT DISTINCT t1.id, t1.name
FROM t_set1 t1
JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name)
```

## 并集存异

![图8.2 并集存异](https://s2.loli.net/2024/12/12/d8mCZMon1VzilAy.png)

```sql
SELECT column1, column2, ...
FROM table1
UNION [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;
```

- DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
- ALL 表示保留合并结果中的重复记录。
- 默认值为 DISTINCT。

```sql
SELECT id, name
FROM t_set1
UNION
SELECT id, name
FROM table2;
```

并集运算可以改写为[全外连接](https://blog.chives.life/post/sql-join-types#%E5%A4%96%E8%BF%9E%E6%8E%A5%20Outer%20Join)。

```sql
-- oracle mss pg
-- COALESCE 返回第一个非空值
SELECT COALESCE(t1.id, t2.id), COALESCE(t1.name, t2.name)
FROM t_set1 t1
FULL JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name);
```

- mysql sqlite 不支持全外连接。
- mysql sqlite 会进行隐式数据类型转换。

```sql
-- mysql sqlite
-- mysql 将第一个查询返回的字段转换为字符串类型
-- sqlite 将第二个查询返回的字段转换为整数类型
SELECT 1 AS id
UNION ALL
SELECT 'sql' AS name;
```

## 差集排他

![图8.3 差集排他](https://s2.loli.net/2024/12/12/iBpFxO9QJbLcUDR.png)

```sql
SELECT column1, column2, ...
FROM table1
EXCEPT [DISTINCT | ALL]
SELECT col1, col2, ...
FROM table2;
```

- DISTINCT 表示对合并后的结果集进行去重操作，只保留不重复的记录。
- ALL 表示保留合并结果中的重复记录。
- 默认值为 DISTINCT。
- mysql 不支持 EXCEPT 运算符。
- oracle 21c 开始支持 EXCEPT 关键字，其以前的版本使用等价的 MINUS 运算符。
- oracle 21c 开始支持 ALL 选项。
- 只有 pg 支持完整的 DISTINCT 和 ALL 选项。
- 其他数据库支持简写的EXCEPT。

```sql
-- oracle 19c以及更早
SELECT id, name
FROM t_set1
MINUS
SELECT id, name
FROM t_set2;
```

差集运算可以改写为[左外连接或右外连接](https://blog.chives.life/post/sql-join-types#%E5%A4%96%E8%BF%9E%E6%8E%A5%20Outer%20Join)。

- mysql 可以用这种方式实现差集。

```sql
SELECT t1.id, t1.name
FROM t_set1 t1
LEFT JOIN t_set2 t2
ON (t2.id = t1.id AND t2.name = t1.name)
WHERE t2.id IS NULL;
```

## 集合运算与排序

想要对集合运算的结果进行排序操作，必须将 ORDER BY 子句写在整个查询语句的最后，集合运算符之前的 SELECT 语句中不能出现排序子句。

```sql
SELECT id, name
FROM t_set1
UNION ALL
SELECT id, name
FROM t_set2
ORDER BY id;
```

## 运算符的优先级

- 按照SQL标准，交集运算符（INTERSECT）的优先级高于并集运算符（UNION）和差集运算符（EXCEPT）​。但是 Oracle 和 SQLite 中所有集合运算符的优先级相同。
- 相同的集合运算符按照从左至右的顺序执行。
- 某些数据库支持使用括号调整多个集合运算符的执行顺序。

```sql
-- mss pg sqlite
SELECT 1 AS n
UNION ALL
SELECT 1
INTERSECT
SELECT 1;
-- mss pg
/*
n
-
1
1
*/

-- sqlite
/*
n
-
1
*/

--oracle
SELECT 1 AS n FROM dual
UNION ALL
SELECT 1 FROM dual
INTERSECT
SELECT 1 FROM dual;
-- oracle
/*
n
-
1
*/

-- oracle 需要加上 dual 表
SELECT 1 AS N
UNION ALL
SELECT 1
UNION
SELECT 1;
/*
n
-
1
*/
SELECT 1 AS N
UNION
SELECT 1
UNION ALL
SELECT 1;
/*
n
-
1
1
*/

-- 使用括号修改顺序
-- mss pg，oracle 需要加上 dual 表
SELECT 1 AS N
UNION ALL
(SELECT 1
UNION
SELECT 1);
/*
n
-
1
1
*/
```

- MySQL 和 SQLite 目前不支持修改集合运算符优先级。

## 参考

- [Set operations (SQL)](https://en.wikipedia.org/wiki/Set_operations_(SQL))

表的集合运算

```sql
SELECT emp_name, salary -- Outer Query
FROM employee
WHERE salary > (
	SELECT AVG(salary) -- Inner Query
	FROM employee
);
```



## 标量子查询 Scalar Subquery

返回单个值（一行一列），可以像常量一样被用于SELECT、WHERE、GROUP BY、HAVING以及ORDER BY等子句中。

```sql
-- 员工月薪与平均月薪之间的差值
SELECT emp_name AS "员工姓名", salary AS "月薪",
	salary - (SELECT AVG(salary) FROM employee) AS "差值"
FROM employee;
```

## 行子查询 Row Subquery

返回单个记录（一行多列），不常用。

```sql
-- 查找所有与“孙乾”在同一个部门并且职位相同的员工
-- oracle mysql pg sqlite
SELECT emp_name, dept_id, job_id
FROM employee
WHERE (dept_id, job_id) = (
	SELECT dept_id, job_id
	FROM employee
	WHERE emp_name = '孙乾'
)
AND emp_name != '孙乾'；
```

- mss 不支持行子查询。

## 表子查询 Table Subquery

返回一个临时表（多行多列）。

### where 中的子查询

- 因为子查询是表，所以不能使用比较运算符（`=`、`!=`、`<`、`<=`、`>`、`>=`等）。
- 可以使用 IN、NOT IN。

```sql
-- 某个部门的全体员工
SELECT emp_name
FROM employee
WHERE job_id IN (
	SELECT job_id
	FROM employee
	WHERE dept_id = 1
);
```

### ALL、ANY 子查询

> ALL 运算符相当于多个 AND 运算符的组合，IN/ANY 运算符相当于多个 OR 运算符的组合。

- ALL 运算符与比较运算符（`=`、`!=`、`<`、`<=`、`>`、`>=`）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的全部数据。

```sql
-- 查找比“研发部”全体员工更晚入职的员工信息
-- oracle mysql mss pg
SELECT emp_name AS "员工姓名", hire_date AS "入职日期"
FROM employee
WHERE hire_date >ALL (
	SELECT e.hire_date
	FROM employee e
	JOIN department d ON (d.dept_id = e.dept_id)
	WHERE d.dept_name = '研发部'
);
```

- sqlite 不支持 ALL。

```sql
-- oracle
SELECT emp_name AS "员工姓名", hire_date AS "入职日期"
FROM employee
WHERE salary >ALL (10000, 15000, 20000);
```

- ANY 运算符与比较运算符（`=`、`!=`、`<`、`<=`、`>`、`>=`）的组合分别表示等于、不等于、小于、小于或等于、大于、大于或等于子查询结果中的任何数据。

```sql
-- 某个部门的全体员工
SELECT emp_name
FROM employee
WHERE job_id =ANY (
	SELECT job_id
	FROM employee
	WHERE dept_id = 1
);
```

- sqlite 不支持 any。
- SOME 关键字可以替代 ANY。

```sql
-- oracle
SELECT emp_name
FROM employee
WHERE job_id !=ANY (1, 2, 2); -- 等价于 job_id != 1 OR job_id != 2
```

### FROM 中的子查询

FROM子句中的子查询相当于一个临时表。

```sql
-- 部门平均工资
SELECT d.dept_name AS "部门名称", ds.avg_salary AS "平均月薪"
FROM department d
LEFT JOIN (
	SELECT dept_id, AVG(salary) AS avg_salary
	FROM employee
	GROUP BY dept_id) ds
ON (d.dept_id = ds.dept_id);
```

子查询中使用排序通常没有意义。

```sql
-- mysql pg sqlite
SELECT emp_name, salary
FROM employee
WHERE job_id IN (
	SELECT job_id
	FROM job
	WHERE max_salary >= 20000
	ORDER BY job_id
)
ORDER BY emp_name;
```

- oracle mss 在子查询中使用排序会报错。

## 关联子查询 Correlated Subquery

子查询使用外部查询中的字段。

> 对于外部查询返回的每条记录，关联子查询都会执行一次（数据库可能会对此进行优化）​，非关联子查询只会独立执行一次。

```sql
-- 每个部门的平均月薪
SELECT d.dept_name AS "部门名称",
	(SELECT AVG(salary) AS avg_aslary
	FROM employee
	WHERE dept_id = d.dept_id) AS "平均月薪"
FROM department d
ORDER BY d.dept_id;

-- 每个部门中最早入职的员工
SELECT d.dept_name AS "部门名称", o.emp_name AS "员工姓名", o.hire_date AS "入职日期"
FROM employee o
JOIN department d ON (d.dept_id = o.dept_id)
WHERE o.hire_date = (SELECT MIN(i.hire_date)
					FROM employee i
					WHERE i.dept_id = o.dept_id);
```

## 横向子查询 Lateral Subquery

关联子查询可以使用外部查询中的字段，但不能使用同一级别的其他查询或者表中的字段。以下是一个错误的示例：

```sql
-- 每个部门的最大月薪
SELECT d.dept_name, t.max_salary
FROM department d
JOIN (SELECT MAX(e.salary) AS max_salary
	 FROM employee e
	 WHERE e.dept_id = d.dept_id) t;
```

横向子查询，允许派生表使用它所在的FROM子句中左侧的其他查询或者表：

```sql
-- oracle mysql pg
SELECT d.dept_name "部门名称", t.max_salary AS "最高月薪"
FROM department d
CROSS JOIN
LATERAL (SELECT MAX(e.salary) AS max_salary
		FROM employee e
		WHERE e.dept_id = d.dept_id) t;

-- mss oracle
SELECT d.dept_name "部门名称", t.max_salary AS "最高月薪"
FROM department d
CROSS APPLY (SELECT MAX(e.salary) AS max_salary
		FROM employee e
		WHERE e.dept_id = d.dept_id) t;
```

- 除 CROSS APPLY 外，还有一个 OUTER APPLY，它是左外连接的一种变体，同样允许右侧的子查询使用 FROM 子句中左侧查询或者表中的字段。Microsoft SQL Server和Oracle提供了这两种查询语法。
- sqlite 不支持横向子查询。

## EXISTS 运算符

用于判断子查询结果的存在性。只要子查询返回了任何结果，就表示满足查询条件；如果子查询没有返回任何结果，就表示不满足查询条件。

```sql
-- 拥有女性员工的部门
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE EXISTS (SELECT 1
			 FROM employee e
			 WHERE e.sex = '女'
			 AND e.dept_id = d.dept_id)
ORDER BY dept_name;
```

> `​[NOT] EXISTS`运算符只检查结果的存在性，`​[NOT] IN`运算符需要比较实际的值是否相等。

当子查询的结果中只有 NULL 值时，EXISTS 运算符仍然可以返回结果，NOT EXISTS 运算符则不返回结果。但是，此时 IN 和 NOT IN 运算符都不会返回结果，因为任何数据和空值进行比较的结果均未知。

```sql
-- 不拥有女性员工的部门
SELECT d.dept_name
FROM department d
WHERE NOT EXISTS (SELECT NULL
			 FROM employee e
			 WHERE e.dept_id = d.dept_id
			 AND sex = '女');
```

NOT IN 的等价写法

```sql
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE d.dept_id NOT INT (3, 4, NULL);

-- 等价于
SELECT d.dept_name AS "部门名称"
FROM department d
WHERE d.dept_id != 3
AND d.dept_id != 4
AND d.dept_id != NULL;
```

## 参考

- [什么是 PostgreSQL 横向子查询？](https://blog.csdn.net/horses/article/details/118769805)

嵌套子查询

[图片来源](https://x.com/ezekiel_aleke/status/1857801057721590244)
![](https://s2.loli.net/2024/12/11/sEAYh2yaXVzuCLt.png)





## 内连接 Inner Join

INNER JOIN 返回两个表中满足连接条件的数据，可以简写为 JOIN。

**等值连接**

```sql
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
JOIN job j ON (e.job_id = j.job_id)
WHERE e.emp_id = 1;
```

**非等值连接**

除了等号运算符外的条件，比如`>=`、`!=`、BETWEEN、AND 等。

```sql
-- 返回当前月薪属于开发经理级别但不是开发经理的员工
SELECT e.emp_name AS "员工姓名", e.salary "月薪"
FROM employee e
JOIN job j ON (e.job_id != j.job_id
			  AND e.salary BETWEEN j.min_salary AND j.max_salary)
WHERE j.job_title = '开发经理';
```

## 外连接 Outer Join

**左外连接**

Left Outer Join 查询首先返回左表中的全部数据。之后，如果右表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 LEFT JOIN。

```sql
-- 每个部门的员工人数
SELECT d.dept_name AS "部门名称", COUNT(e.emp_id) AS "员工人数"
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;

-- 没有员工的部门
SELECT d.dept_id AS "部门编号", d.dept_name AS "部门名称"
FROM department d
LEFT JOIN employee e ON (e.dept_id = d.dept_id)
WHERE e.emp_id IS NULL;
```

**右外连接**

Right Outer Join 查询首先返回右表中的全部数据。如果左表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 RIGHT JOIN。

- 右外连接和左外连接可以相互转换。

```sql
-- 每个部门员工人数
-- oracle mysql mss pg
SELECT d.dept_name AS "部门名称", COUNT(e.emp_id) AS "员工人数"
FROM employee e
RIGHT JOIN department d ON (e.dept_id = d.dept_id)
GROUP BY d.dept_name;
```

- sqlite 不支持右外连接（[应该支持?](https://www.sqlite.org/lang_select.html)）

**全外连接**

Full Outer Join 查询同时返回左表和右表中所有的数据。如果右表或者左表中存在满足连接条件的数据，就返回该数据；如果没有相应的数据，就返回空值。可以简写为 FULL JOIN。

```sql
-- 获取哪些组没有员工和哪些员工没有分配到组
-- oracle mss pg
SELECT g.group_name, eg.emp_id
FROM t_group g
FULL JOIN t_emp_group eg ON (eg.group_id = g.group_id)
WHERE g.group_id IS NULL OR eg.emp_id IS NULL;
```

- mysql sqlite 不支持全外连接。（[应该支持?](https://www.sqlite.org/lang_select.html)）

## 交叉连接/笛卡尔积 Cartesian Product

CROSS JOIN 将一个表的所有数据行和另一个表的所有数据行进行两两组合，返回结果的数量为两个表中的行数相乘。

```sql
SELECT COUNT(*)
FROM employee e
CROSS JOIN department d;
```

## 自然连接 Natural Join

如果连接查询同时满足以下条件，我们可以使用 USING 替代 ON 来简化连接条件的输入：

- 连接条件是等值连接。
- 两个表中的连接字段名称相同，类型也相同。

```sql
-- oracle mysql pg sqlite
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
JOIN job j USING (job_id)
WHERE e.emp_id = 1;
```

- mss 不支持 USING。

如果等值连接条件中包含了两个表中所有同名同类型的字段，查询语句可以继续进行简化。

```sql
-- oracle mysql pg sqlite
SELECT e.emp_name AS "员工姓名", j.job_title "职位名称"
FROM employee e
NATURAL JOIN job j
WHERE e.emp_id = 1;
```

- mss 不支持自然连接。

## 自连接 Self Join

Self Join 查询指的是一个表和它自己进行连接查询。自连接本质上并没有什么特殊之处，主要用于处理那些对自身进行了外键引用的表。

```sql
-- 查看员工以及他的经理
SELECT e.emp_name AS "员工姓名", m.emp_name AS "经理姓名"
FROM employee e
LEFT JOIN employee m ON (m.emp_id = e.manager)
WHERE e.emp_id = 9;
```

SQL 连接类型

自己在查看网络时总是记不住 TCP 状态，所以整理了一下关于 TCP 的图，都来源于 TCP/IP GUID 这本书。这里并没有整理所有的图，有需要再加。



## TCP Segment Format

[位置](http://www.tcpipguide.com/free/t_TCPMessageSegmentFormat-3.htm)
![Figure 216: TCP Segment Format](https://s2.loli.net/2024/11/20/nuwI3cUbD8RhSdq.png)

## The TCP Finite State Machine (FSM)

[位置](http://www.tcpipguide.com/free/t_TCPOperationalOverviewandtheTCPFiniteStateMachineF-2.htm)
![Figure 210: The TCP Finite State Machine (FSM)](https://s2.loli.net/2024/11/20/FIiKhEAl4TemrSL.png)

## TCP “Three-Way Handshake” Connection Establishment Procedure

[位置](http://www.tcpipguide.com/free/t_TCPConnectionEstablishmentProcessTheThreeWayHandsh-3.htm)
![Figure 211: TCP “Three-Way Handshake” Connection Establishment Procedure](https://s2.loli.net/2024/11/20/REODpuWB3Gwl1v4.png)

## TCP Simultaneous Open Connection Establishment Procedure

[位置](http://www.tcpipguide.com/free/t_TCPConnectionEstablishmentProcessTheThreeWayHandsh-4.htm)
![Figure 212: TCP Simultaneous Open Connection Establishment Procedure](https://s2.loli.net/2024/11/20/Yuec7DPRQWEXk5L.png)

## TCP Connection Termination Procedure

[位置](http://www.tcpipguide.com/free/t_TCPConnectionTermination-2.htm)
![Figure 214: TCP Connection Termination Procedure](https://s2.loli.net/2024/11/20/LHTwPhm1a8AV2E4.png)

## TCP Simultaneous Connection Termination Procedure

[位置](http://www.tcpipguide.com/free/t_TCPConnectionTermination-4.htm)
![Figure 215: TCP Simultaneous Connection Termination Procedure](https://s2.loli.net/2024/11/20/RJm5gMr7zdQlhF8.png)

## 参考

- [The TCP/IP Guide](http://www.tcpipguide.com/free/index.htm)