当前位置 博文首页 > Tony.Dong的专栏：什么是 PostgreSQL 横向子查询？
大家好，我是只谈技术不剪发的 Tony 老师。
一般来说，SQL 子查询只能引用外查询中的字段，而不能使用同一层级中其他表中的字段。例如：
-- 错误示例
SELECT d.dept_name,
t.avg_salary
FROM department d
JOIN (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_name
FROM department;
简单来说，我们可以将以上查询看作一个循环处理语句。使用伪代码实现的以上 SQL 语句如下：
for dept_id, dept_name in department
loop
print dept_id, dept_name
end loop
对于 department 中的每一条记录，都执行 SELECT 语句指定的操作，以上示例简单的输出了每行记录。
SELECT 就像一个循环语句，而 LATERAL 就像是一个嵌套循环语句，对于左侧表中的每行记录执行一次子查询操作。例如，通过增加 LATERAL 关键字，我们可以修改第一个示例：
SELECT d.dept_name,
t.avg_salary
FROM department d
CROSS 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.salary
FROM department d
LEFT JOIN LATERAL
(SELECT emp_name, salary
FROM employee e
WHERE e.dept_id = d.dept_id
ORDER BY salary DESC
LIMIT 3) t
ON 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 department
loop
for e in employee order by salary desc
loop
cnt++
if cnt <= 3
then
return e
else
goto next d
end
end loop
end loop
通过 EXPLIAN 命令查看以上语句的执行计划：
EXPLAIN
SELECT d.dept_name, t.emp_name, t.salary
FROM department d
LEFT JOIN LATERAL
(SELECT emp_name, salary
FROM employee e
WHERE e.dept_id = d.dept_id
ORDER BY salary DESC
LIMIT 3) t
ON 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 使用的就是嵌套循环算法。
如果觉得文章有用，欢迎评论📝、点赞👍、推荐🎁
如果你想要学习更多 PostgreSQL 知识，可以参考这篇文章。
cs