高级 JOIN 与子查询
中等 约 35 分钟
你将构建什么
一组针对员工/部门模式的高级 SQL 查询,包括用于层级的自连接、用于查找缺失记录的反连接,以及用于逐行比较的相关子查询。
学习目标
- 为层级数据编写自连接
- 使用反连接(LEFT JOIN + IS NULL)查找未匹配的行
- 编写相关和非相关子查询
- 高效使用 EXISTS 和 NOT EXISTS
前置要求
- INNER 和 LEFT JOIN 基础
- WHERE 子句过滤
- 子查询基础
步骤
- 1
建立模式
创建一个带有自引用 manager_id 列的 employees 表,用于建模组织层级。
代码CREATE TABLE employees ( id SERIAL PRIMARY KEY, name TEXT NOT NULL, manager_id INTEGER REFERENCES employees(id), salary INTEGER NOT NULL, dept TEXT NOT NULL ); INSERT INTO employees (name, manager_id, salary, dept) VALUES ('CEO', NULL, 500000, 'Exec'), ('VP Eng', 1, 300000, 'Eng'), ('VP Sales', 1, 280000, 'Sales'), ('Dev Lead', 2, 200000, 'Eng'), ('Senior Dev', 4, 150000, 'Eng'), ('Junior Dev', 4, 90000, 'Eng'), ('Sales Rep', 3, 100000, 'Sales');预期结果
插入七名员工,形成管理层级结构。
- 2
自连接查找经理姓名
将 employees 表与自身连接,查找每位员工的经理姓名。自连接通过使用不同别名将一张表视为两张表。
代码SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id ORDER BY e.id;预期结果
每行员工显示其经理姓名;CEO 的经理为 NULL。
- 3
反连接:没有直接下属的员工
反连接查找在一张表中没有匹配的行。使用 LEFT JOIN + IS NULL 查找不是任何人经理的员工。
代码SELECT e.name FROM employees e LEFT JOIN employees r ON r.manager_id = e.id WHERE r.id IS NULL ORDER BY e.name;预期结果
返回 'Senior Dev'、'Junior Dev' 和 'Sales Rep' —— 叶子员工。
- 4
相关子查询:高于平均薪资
相关子查询引用外层查询。查找薪资高于其所在部门平均薪资的员工。
代码SELECT name, salary, dept FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept = e.dept ) ORDER BY dept, salary DESC;预期结果
返回薪资高于部门平均的员工 —— 例如 Eng 部门的 Dev Lead。
- 5
用 EXISTS 查找依赖记录
使用 EXISTS 检查相关行的存在。查找至少有一名员工薪资超过 150000 的部门。
代码SELECT DISTINCT dept FROM departments d WHERE EXISTS ( SELECT 1 FROM employees e WHERE e.dept = d.name AND e.salary > 150000 );预期结果
返回 'Eng' 和 'Exec' —— 有高薪员工的部门。
自己动手试试
起始代码
-- Self-join: find each employee's manager
SELECT e.name, ??? AS manager
FROM employees e
??? JOIN employees m ON ???;解决方案代码
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;你学到了什么
你掌握了高级 JOIN 模式 —— 用于层级的自连接、用于查找空缺的反连接,以及用于行级比较的相关子查询。这些是复杂报表查询的必备技能。
相关资源
相关术语
新手友好的术语表用大白话解释编程术语——变量、函数、DOM、Promise 等等。
解读错误信息
用大白话解释常见错误——什么意思、为什么发生、如何修复。