SQL嵌套查询中内外“跨级引用表”的原理解释

个人学习中最难解释的就是这种代码的工作原理：

目的：返回有下属员工部门的部门信息，并返回其下属员工数

SELECT d.*,(
	SELECT COUNT(*)
	FROM `employees` e
	WHERE e.`department_id`=d.`department_id`
) 个数
FROM `departments` d;

 

结果：

很容易解释成先执行子查询，而子查询就是统计有所属部门的员工总数，得到一个常量（这里是106）。然后在主查询中就是select一个常量，按道理说应该无论是哪个部门，这个常量都不变。不能得到每个部门的所属员工数。

但其实际运行机制应该并非是先执行完子查询，再执行主查询。因为在这个代码中：

子查询引用了主查询中的表

所以其运行过程应该是这样的：

1. 先运行from语句，进入departments表
2. 运行select语句，获取第一个实例(数据)
3. 进入select语句中的子查询，
   1. 运行from语句，进入employees表
   2. 运行where条件句，获取雇员表中的部门id=主查询目前选取的实例的部门id这个条件
   3. 运行select语句，获取雇员表中的第一个实例(数据)。判断是否符合where条件，如果符合则计算count()函数，即次数+1
   4. 返回至(子查询的)第2步，获取下一个实例。直到没有实例满足where条件(即等于这个id的实例已经全部被取过了
   5. 输出select结果(即得到该部门的统计人数)
4. 将子查询的结果一起进行select输出(即部门信息和统计人数)，作为输出表的其中一条
5. 返回至(主查询的)第2步，获取下一个实例。

大概流程总结为：

主查询每查询一个实例，子查询查询完整的一轮

拓展例：

目的：查询有下属员工的部门名

SELECT `department_name`
FROM `departments` d
WHERE EXISTS(
	SELECT *
	FROM `employees` e
	WHERE d.`department_id`=e.`department_id`
);

同样的原理，

1. 主查询先获取一条数据。
2. 然后where中的子查询依次选取雇员表中所有数据，并将员工的部门ID与该主查询获取的数据的部门ID进行比较，如果相等则输出。
3. 当子查询的雇员表搜索完毕后全部select输出。
4. 再用exists判断，若子查询无输出则为0，反之为1。
5. 则说明主查询选取的这条数据的where条件为0，则不输出这条数据。反之，则输出。
6. 然后主查询继续选取下一条数据。