横向子查询在 PostgreSQL 中是如何工作的?

发表时间: 2021-07-23 17:41

作者 | 不剪发的Tony老师 责编 | 晋兆雨

出品 | CSDN博客
一般来说,SQL 子查询只能引用外查询中的字段,而不能使用同一层级中其他表中的字段。例如:
-- 错误示例
SELECT d.dept_name, t.avg_salaryFROM department dJOIN (SELECT avg(e.salary) AS avg_salary FROM employee e WHERE e.dept_id = d.dept_id) t;SQL 错误 [42601]: ERROR: syntax error at end of input 位置:183
由于 JOIN 子句中的查询语句 t 引用了左侧 department 表中的字段,因此产生了语法错误。
了解决以上问题,我们可以使用 PostgreSQL 提供的横向子查询(LATERAL subquery)。不过在介绍 LATERAL 关键字之前,我们先来回顾一下 SELECT 和 FROM 子句的含义。例如:
SELECT dept_id, dept_nameFROM department;
简单来说,我们可以将以上查询看作一个循环处理语句。使用伪代码实现的以上 SQL 语句如下:
for dept_id, dept_name in departmentloop print dept_id, dept_nameend loop
对于 department 中的每一条记录,都执行 SELECT 语句指定的操作,以上示例简单的输出了每行记录。
SELECT 就像一个循环语句,而 LATERAL 就像是一个嵌套循环语句,对于左侧表中的每行记录执行一次子查询操作。例如,通过增加 LATERAL 关键字,我们可以修改第一个示例:
SELECT d.dept_name, t.avg_salaryFROM department dCROSS JOIN LATERAL (SELECT avg(e.salary) AS avg_salary FROM employee e WHERE e.dept_id = d.dept_id) t;

dept_name |avg_salary |-----------+----------------------+行政管理部 | 26666.666666666667|人力资源部 |13166.6666666666666667|财务部 | 9000.0000000000000000|研发部 | 7577.7777777777777778|销售部 | 5012.5000000000000000|保卫部 | |
CROSS JOIN LATERAL 右侧的查询可以引用左侧表中的字段,以上语句为 JOIN 左侧的每个部门返回了月薪总和。
LATERAL 可以帮助我们实现一些有用的分析功能,例如以下查询返回了每个部门月薪最高的 3 名员工:
SELECT d.dept_name, t.emp_name, t.salaryFROM department dLEFT JOIN LATERAL (SELECT emp_name, salary FROM employee e WHERE e.dept_id = d.dept_id ORDER BY salary DESC LIMIT 3) tON TRUE;

dept_name |emp_name|salary |-----------+--------+--------+行政管理部 |刘备 |30000.00|行政管理部 |关羽 |26000.00|行政管理部 |张飞 |24000.00|人力资源部 |诸葛亮 |24000.00|人力资源部 |黄忠 | 8000.00|人力资源部 |魏延 | 7500.00|财务部 |孙尚香 |12000.00|财务部 |孙丫鬟 | 6000.00|研发部 |赵云 |15000.00|研发部 |周仓 | 8000.00|研发部 |关兴 | 7000.00|销售部 |法正 |10000.00|销售部 |简雍 | 4800.00|销售部 |孙乾 | 4700.00|保卫部 | | |
对于 department 中的每个部门,子查询 t 最多返回 3 个员工信息。我们使用了 LEFT JOIN LATERAL,从而保证了“保卫部”也会返回一条数据。
同样使用伪代码表示以上查询语句:
for d in departmentloop for e in employee order by salary desc loop cnt++ if cnt <= 3 then return e else goto next d end end loopend loop
通过 EXPLIAN 命令查看以上语句的执行计划:
EXPLAINSELECT d.dept_name, t.emp_name, t.salaryFROM department dLEFT JOIN LATERAL (SELECT emp_name, salary FROM employee e WHERE e.dept_id = d.dept_id ORDER BY salary DESC LIMIT 3) tON TRUE;

QUERY PLAN |-------------------------------------------------------------------------------------------------+Nested Loop Left Join (cost=8.17..4439.35 rows=540 width=250) | -> Seq Scan on department d (cost=0.00..15.40 rows=540 width=122) | -> Limit (cost=8.17..8.17 rows=1 width=132) | -> Sort (cost=8.17..8.17 rows=1 width=132) | Sort Key: e.salary DESC | -> Index Scan using idx_emp_dept on employee e (cost=0.14..8.16 rows=1 width=132)| Index Cond: (dept_id = d.dept_id) |
Nested Loop Left Join 说明 PostgreSQL 使用的就是嵌套循环算法
版权声明:本文为CSDN博主「不剪发的Tony老师」的原创文章。
原文链接:https://blog.csdn.net/horses/article/details/118769805