拓冰网站定制 
我排查过一个线上报表一句“查不在销售部的员工”的 SQL只因子查询返回的列表里混进了一个 NULL本该有几十行的结果集直接变成空报表空了一上午。这篇按一条真实需求链把 WHERE、FROM、SELECT 三个位置的子查询连同标量、列、相关三类拆到底让你一次避开 NULL、多行、性能这三个坑。先把一个查询的结果当成一个值用需求很具体查出工资高于公司平均工资的员工。平均工资本身是一条聚合查询怎么把它塞进 WHERE 当比较值答案是把这条查询用括号包起来直接放在比较运算符右侧SELECTemployee_id,name,salaryFROMemployeesWHEREsalary(SELECTAVG(salary)FROMemployees);括号里的语句就是子查询Subquery外层语句叫主查询Outer Query。这条子查询返回一行一列也就是一个标量值称为标量子查询Scalar Subquery。它先独立执行一次再把结果交回主查询逐行比较。能接收标量的位置和运算符如下使用位置可用运算符WHERESELECT作为一个计算列这里有个我刚学时踩过的坑标量子查询只要返回多行数据库就直接报错。ERROR 1242 (21000): Subquery returns more than 1 row所以标量子查询的前提是结果唯一不确定时就用聚合函数把结果收敛成一个值。注意标量子查询必须一行一列多返回一行数据库直接报错。一个值装不下就换成一列值高于平均工资比较的是一个值。可换成“在销售部干过的员工”销售部可能对应多个部门 ID一个值装不下怎么办那就让子查询返回一列多行再用 IN 承接SELECTemployee_id,nameFROMemployeesWHEREdepartment_idIN(SELECTdepartment_idFROMdepartmentsWHEREdepartment_name销售部);这种返回一列多行的子查询叫列子查询Column Subquery配套的运算符有 IN、ANY、ALL对应关系如下写法含义等价聚合改写IN等于列表中任意一个 ANY ANY大于列表中任意一个 MIN(...) ALL大于列表中全部 MAX(...)-- 高于销售部任意一人等价于高于最低薪WHEREsalaryANY(SELECTsalaryFROMemployeesWHEREdepartment_id10);-- 高于销售部所有人等价于高于最高薪WHEREsalaryALL(SELECTsalaryFROMemployeesWHEREdepartment_id10);现在回到开头那个空掉的报表。把 IN 换成 NOT IN一旦子查询结果里混进 NULL结果就恒为空NOT IN 命中逻辑列表含 NULL x 1 AND x 2 AND ... AND x NULL │ ▼ x NULL 的结果是 UNKNOWN不是 TRUE │ ▼ 整条 AND 链被 UNKNOWN 污染结果集为空这是 SQL 的三值逻辑Three-valued Logic布尔值不是只有真、假还有 UNKNOWN。x NULL永远是 UNKNOWN而 NOT IN 要求每一项比较都为真一项 UNKNOWN 就整体落空。生产环境我不会用 NOT IN 兜这种风险直接改 NOT EXISTS它不关心 NULL命中即停SELECTemployee_id,nameFROMemployees eWHERENOTEXISTS(SELECT1FROMdepartments dWHEREd.department_ide.department_idANDd.department_name销售部);注意NOT IN 的列表里只要混进一个 NULL整个结果恒为空。子查询要读外层每一行怎么办前面两类子查询都能脱离主查询、独立跑一次。但需求换成“高于本部门平均工资”平均值每个部门都不一样子查询没法一次性算完怎么办那就让子查询引用主查询的列把两个查询按部门关联起来SELECTe1.employee_id,e1.name,e1.salaryFROMemployees e1WHEREe1.salary(SELECTAVG(e2.salary)FROMemployees e2WHEREe2.department_ide1.department_id);这条子查询无法单独执行因为它引用了外层的e1.department_id称为相关子查询Correlated Subquery。它的执行顺序是主查询取出一行 ↓ 绑定该行 department_id ↓ 子查询算出本部门平均工资 ↓ salary 该平均值 ↓ 取下一行回到顶部重复问题也在这张图里主查询有多少行子查询就执行多少次执行次数与外层行数成正比。我在十万行的表上跑过这种写法耗时随行数线性上涨。更稳的做法是用窗口函数Window Function让数据库扫描一次表就把每个部门的均值算好SELECTemployee_id,name,salaryFROM(SELECTemployee_id,name,salary,AVG(salary)OVER(PARTITIONBYdepartment_id)ASdept_avgFROMemployees)tWHEREsalarydept_avg;验证全表只扫描一次每个部门的均值在同一次扫描中算完耗时不再随行数上涨。相关子查询还有个高频形态 EXISTS它只关心子查询是否返回行不关心返回什么所以子查询里固定写 SELECT 1。查“有下属的员工”SELECTe.employee_id,e.nameFROMemployees eWHEREEXISTS(SELECT1FROMemployees subWHEREsub.manager_ide.employee_id);EXISTS 找到第一行就停止子查询结果集较大时通常比 IN 更省时。注意相关子查询外层每行执行一次大表先用窗口函数或 JOIN 改写再谈性能。把聚合结果当成临时表再查一次过滤条件写到这就到头了。可如果要“先按部门算出平均工资再筛出平均工资大于 10000 的部门”WHERE 里没法对一个聚合结果再过滤怎么办那就把聚合查询放进 FROM当成一张临时表主查询再对它过滤SELECTdepartment_id,avg_salaryFROM(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id)ASdept_avgWHEREavg_salary10000;FROM 里的子查询叫派生表Derived Table它把结果当作一张临时表供主查询使用。派生表必须起别名否则报错。它也能像普通表一样参与 JOIN把部门名称一并带出SELECTd.department_name,t.avg_salaryFROMdepartments dJOIN(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id)tONd.department_idt.department_id;派生表在底层有两种处理方式成本差异明显处理方式触发条件成本派生表合并仅过滤、无聚合 / LIMIT近乎零额外成本物化成临时表含 GROUP BY / DISTINCT / 聚合 / LIMIT先建临时表再回表MySQL 5.7 及之前普遍物化派生表8.0 起默认支持派生表合并。可读性更好的替代是公共表表达式CTECommon Table Expression用 WITH 把临时结果命名逻辑和派生表一致但主查询更清晰WITHdept_avgAS(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id)SELECTdepartment_id,avg_salaryFROMdept_avgWHEREavg_salary10000;如果确实需要在 FROM 里写“每行关联一次”的子查询PostgreSQL 和 MySQL 8.0.14 提供了 LATERAL 语法让派生表能引用外层列SELECTe.name,t.max_salaryFROMemployees eJOINLATERAL(SELECTMAX(salary)ASmax_salaryFROMemployees subWHEREsub.department_ide.department_id)tONTRUE;注意派生表必须起别名带聚合、LIMIT 的派生表往往先物化别假设它零成本。给每一行拼一个额外的计算值派生表解决了“对聚合结果再过滤”。但如果需求是“每行都显示公司平均工资这一列”既不是过滤也不是分组这一列放哪放进 SELECT用一个不依赖外层的标量子查询它只执行一次结果被复用到每一行SELECTemployee_id,name,salary,(SELECTAVG(salary)FROMemployees)AScompany_avgFROMemployees;如果子查询引用外层列就变成相关标量子查询每行执行一次同样建议换成窗口函数SELECTemployee_id,name,salary,AVG(salary)OVER(PARTITIONBYdepartment_id)ASdept_avgFROMemployees;SELECT 里的子查询有两条硬约束必须返回单值多行就报错用MAX()、MIN()或LIMIT 1收敛不能引用同一层 SELECT 里刚定义的别名别名在子查询之后才生效注意SELECT 里的子查询只能返回单值多行就用聚合或 LIMIT 1 收敛。三类子查询怎么选速查表收尾走完这条链三类子查询的边界已经清楚先用一张表对齐类型返回结果常见位置典型运算符执行特点标量子查询单行单列WHERE、SELECT独立执行通常一次列子查询多行单列WHEREINANYALL独立执行通常一次相关子查询任意WHERE、SELECTEXISTSIN依赖外层每行一次选型时顺着这条判断走子查询返回什么 │ ├─ 一个值 ───► 标量子查询 │ ├─ 一列值 ───► 列子查询IN / ANY / ALL │ └─ 取反用 NOT EXISTS别用 NOT IN │ └─ 依赖外层列 ─► 相关子查询EXISTS └─ 大表改用窗口函数 / JOIN 对聚合结果再过滤 ──► FROM 派生表起别名 每行带一个计算值 ──► SELECT 标量子查询落库前我会固定检查这几条关联列建索引相关子查询和 JOIN 的性能高度依赖索引大结果集优先 EXISTS找到即停取反用 NOT EXISTS 规避 NULL相关子查询能改窗口函数就改扫描次数从 N 降到 1派生表和 SELECT 子查询都遵守“返回单值、起别名”的约束术语速查表术语英文一句话说明子查询Subquery嵌套在另一条 SQL 中的 SELECT主查询Outer Query包裹子查询的外层语句标量子查询Scalar Subquery返回一行一列列子查询Column Subquery返回一列多行相关子查询Correlated Subquery引用外层列每行执行一次派生表Derived TableFROM 中的子查询须起别名窗口函数Window Function一次扫描完成分组聚合不折叠行公共表表达式CTEWITH 命名的临时结果集三值逻辑Three-valued Logic真、假、UNKNOWN 三种布尔结果总结子查询的本质是把一条查询的结果交给另一条查询继续使用。标量返回一个值列返回一列值相关子查询则让内层读取外层每一行WHERE、FROM、SELECT 三个位置对应过滤、临时表、计算列三种用途。判断子查询好不好别只看语法对不对要看它怎么执行能不能独立跑、执行几次、会不会被合并、会不会随行数线性变慢。把执行逻辑想清楚正确和高效才会同时成立。参考链接MySQL 8.0 子查询语法https://dev.mysql.com/doc/refman/8.0/en/subqueries.htmlMySQL 8.0 派生表https://dev.mysql.com/doc/refman/8.0/en/derived-tables.htmlPostgreSQL 子查询表达式https://www.postgresql.org/docs/current/functions-subquery.htmlPostgreSQL LATERALhttps://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL 子查询全解析：WHERE、FROM、SELECT 三大场景与标量、列、相关子查询实战
我排查过一个线上报表一句“查不在销售部的员工”的 SQL只因子查询返回的列表里混进了一个 NULL本该有几十行的结果集直接变成空报表空了一上午。这篇按一条真实需求链把 WHERE、FROM、SELECT 三个位置的子查询连同标量、列、相关三类拆到底让你一次避开 NULL、多行、性能这三个坑。先把一个查询的结果当成一个值用需求很具体查出工资高于公司平均工资的员工。平均工资本身是一条聚合查询怎么把它塞进 WHERE 当比较值答案是把这条查询用括号包起来直接放在比较运算符右侧SELECTemployee_id,name,salaryFROMemployeesWHEREsalary(SELECTAVG(salary)FROMemployees);括号里的语句就是子查询Subquery外层语句叫主查询Outer Query。这条子查询返回一行一列也就是一个标量值称为标量子查询Scalar Subquery。它先独立执行一次再把结果交回主查询逐行比较。能接收标量的位置和运算符如下使用位置可用运算符WHERESELECT作为一个计算列这里有个我刚学时踩过的坑标量子查询只要返回多行数据库就直接报错。ERROR 1242 (21000): Subquery returns more than 1 row所以标量子查询的前提是结果唯一不确定时就用聚合函数把结果收敛成一个值。注意标量子查询必须一行一列多返回一行数据库直接报错。一个值装不下就换成一列值高于平均工资比较的是一个值。可换成“在销售部干过的员工”销售部可能对应多个部门 ID一个值装不下怎么办那就让子查询返回一列多行再用 IN 承接SELECTemployee_id,nameFROMemployeesWHEREdepartment_idIN(SELECTdepartment_idFROMdepartmentsWHEREdepartment_name销售部);这种返回一列多行的子查询叫列子查询Column Subquery配套的运算符有 IN、ANY、ALL对应关系如下写法含义等价聚合改写IN等于列表中任意一个 ANY ANY大于列表中任意一个 MIN(...) ALL大于列表中全部 MAX(...)-- 高于销售部任意一人等价于高于最低薪WHEREsalaryANY(SELECTsalaryFROMemployeesWHEREdepartment_id10);-- 高于销售部所有人等价于高于最高薪WHEREsalaryALL(SELECTsalaryFROMemployeesWHEREdepartment_id10);现在回到开头那个空掉的报表。把 IN 换成 NOT IN一旦子查询结果里混进 NULL结果就恒为空NOT IN 命中逻辑列表含 NULL x 1 AND x 2 AND ... AND x NULL │ ▼ x NULL 的结果是 UNKNOWN不是 TRUE │ ▼ 整条 AND 链被 UNKNOWN 污染结果集为空这是 SQL 的三值逻辑Three-valued Logic布尔值不是只有真、假还有 UNKNOWN。x NULL永远是 UNKNOWN而 NOT IN 要求每一项比较都为真一项 UNKNOWN 就整体落空。生产环境我不会用 NOT IN 兜这种风险直接改 NOT EXISTS它不关心 NULL命中即停SELECTemployee_id,nameFROMemployees eWHERENOTEXISTS(SELECT1FROMdepartments dWHEREd.department_ide.department_idANDd.department_name销售部);注意NOT IN 的列表里只要混进一个 NULL整个结果恒为空。子查询要读外层每一行怎么办前面两类子查询都能脱离主查询、独立跑一次。但需求换成“高于本部门平均工资”平均值每个部门都不一样子查询没法一次性算完怎么办那就让子查询引用主查询的列把两个查询按部门关联起来SELECTe1.employee_id,e1.name,e1.salaryFROMemployees e1WHEREe1.salary(SELECTAVG(e2.salary)FROMemployees e2WHEREe2.department_ide1.department_id);这条子查询无法单独执行因为它引用了外层的e1.department_id称为相关子查询Correlated Subquery。它的执行顺序是主查询取出一行 ↓ 绑定该行 department_id ↓ 子查询算出本部门平均工资 ↓ salary 该平均值 ↓ 取下一行回到顶部重复问题也在这张图里主查询有多少行子查询就执行多少次执行次数与外层行数成正比。我在十万行的表上跑过这种写法耗时随行数线性上涨。更稳的做法是用窗口函数Window Function让数据库扫描一次表就把每个部门的均值算好SELECTemployee_id,name,salaryFROM(SELECTemployee_id,name,salary,AVG(salary)OVER(PARTITIONBYdepartment_id)ASdept_avgFROMemployees)tWHEREsalarydept_avg;验证全表只扫描一次每个部门的均值在同一次扫描中算完耗时不再随行数上涨。相关子查询还有个高频形态 EXISTS它只关心子查询是否返回行不关心返回什么所以子查询里固定写 SELECT 1。查“有下属的员工”SELECTe.employee_id,e.nameFROMemployees eWHEREEXISTS(SELECT1FROMemployees subWHEREsub.manager_ide.employee_id);EXISTS 找到第一行就停止子查询结果集较大时通常比 IN 更省时。注意相关子查询外层每行执行一次大表先用窗口函数或 JOIN 改写再谈性能。把聚合结果当成临时表再查一次过滤条件写到这就到头了。可如果要“先按部门算出平均工资再筛出平均工资大于 10000 的部门”WHERE 里没法对一个聚合结果再过滤怎么办那就把聚合查询放进 FROM当成一张临时表主查询再对它过滤SELECTdepartment_id,avg_salaryFROM(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id)ASdept_avgWHEREavg_salary10000;FROM 里的子查询叫派生表Derived Table它把结果当作一张临时表供主查询使用。派生表必须起别名否则报错。它也能像普通表一样参与 JOIN把部门名称一并带出SELECTd.department_name,t.avg_salaryFROMdepartments dJOIN(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id)tONd.department_idt.department_id;派生表在底层有两种处理方式成本差异明显处理方式触发条件成本派生表合并仅过滤、无聚合 / LIMIT近乎零额外成本物化成临时表含 GROUP BY / DISTINCT / 聚合 / LIMIT先建临时表再回表MySQL 5.7 及之前普遍物化派生表8.0 起默认支持派生表合并。可读性更好的替代是公共表表达式CTECommon Table Expression用 WITH 把临时结果命名逻辑和派生表一致但主查询更清晰WITHdept_avgAS(SELECTdepartment_id,AVG(salary)ASavg_salaryFROMemployeesGROUPBYdepartment_id)SELECTdepartment_id,avg_salaryFROMdept_avgWHEREavg_salary10000;如果确实需要在 FROM 里写“每行关联一次”的子查询PostgreSQL 和 MySQL 8.0.14 提供了 LATERAL 语法让派生表能引用外层列SELECTe.name,t.max_salaryFROMemployees eJOINLATERAL(SELECTMAX(salary)ASmax_salaryFROMemployees subWHEREsub.department_ide.department_id)tONTRUE;注意派生表必须起别名带聚合、LIMIT 的派生表往往先物化别假设它零成本。给每一行拼一个额外的计算值派生表解决了“对聚合结果再过滤”。但如果需求是“每行都显示公司平均工资这一列”既不是过滤也不是分组这一列放哪放进 SELECT用一个不依赖外层的标量子查询它只执行一次结果被复用到每一行SELECTemployee_id,name,salary,(SELECTAVG(salary)FROMemployees)AScompany_avgFROMemployees;如果子查询引用外层列就变成相关标量子查询每行执行一次同样建议换成窗口函数SELECTemployee_id,name,salary,AVG(salary)OVER(PARTITIONBYdepartment_id)ASdept_avgFROMemployees;SELECT 里的子查询有两条硬约束必须返回单值多行就报错用MAX()、MIN()或LIMIT 1收敛不能引用同一层 SELECT 里刚定义的别名别名在子查询之后才生效注意SELECT 里的子查询只能返回单值多行就用聚合或 LIMIT 1 收敛。三类子查询怎么选速查表收尾走完这条链三类子查询的边界已经清楚先用一张表对齐类型返回结果常见位置典型运算符执行特点标量子查询单行单列WHERE、SELECT独立执行通常一次列子查询多行单列WHEREINANYALL独立执行通常一次相关子查询任意WHERE、SELECTEXISTSIN依赖外层每行一次选型时顺着这条判断走子查询返回什么 │ ├─ 一个值 ───► 标量子查询 │ ├─ 一列值 ───► 列子查询IN / ANY / ALL │ └─ 取反用 NOT EXISTS别用 NOT IN │ └─ 依赖外层列 ─► 相关子查询EXISTS └─ 大表改用窗口函数 / JOIN 对聚合结果再过滤 ──► FROM 派生表起别名 每行带一个计算值 ──► SELECT 标量子查询落库前我会固定检查这几条关联列建索引相关子查询和 JOIN 的性能高度依赖索引大结果集优先 EXISTS找到即停取反用 NOT EXISTS 规避 NULL相关子查询能改窗口函数就改扫描次数从 N 降到 1派生表和 SELECT 子查询都遵守“返回单值、起别名”的约束术语速查表术语英文一句话说明子查询Subquery嵌套在另一条 SQL 中的 SELECT主查询Outer Query包裹子查询的外层语句标量子查询Scalar Subquery返回一行一列列子查询Column Subquery返回一列多行相关子查询Correlated Subquery引用外层列每行执行一次派生表Derived TableFROM 中的子查询须起别名窗口函数Window Function一次扫描完成分组聚合不折叠行公共表表达式CTEWITH 命名的临时结果集三值逻辑Three-valued Logic真、假、UNKNOWN 三种布尔结果总结子查询的本质是把一条查询的结果交给另一条查询继续使用。标量返回一个值列返回一列值相关子查询则让内层读取外层每一行WHERE、FROM、SELECT 三个位置对应过滤、临时表、计算列三种用途。判断子查询好不好别只看语法对不对要看它怎么执行能不能独立跑、执行几次、会不会被合并、会不会随行数线性变慢。把执行逻辑想清楚正确和高效才会同时成立。参考链接MySQL 8.0 子查询语法https://dev.mysql.com/doc/refman/8.0/en/subqueries.htmlMySQL 8.0 派生表https://dev.mysql.com/doc/refman/8.0/en/derived-tables.htmlPostgreSQL 子查询表达式https://www.postgresql.org/docs/current/functions-subquery.htmlPostgreSQL LATERALhttps://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-LATERAL