横向子查询在 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 使用的就是嵌套循环算法。

原文链接：https://blog.csdn.net/horses/article/details/118769805

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

热门阅读

推荐阅读