子查询 | 恬静荒芜的时光

基础表结构

-- 部门表
CREATE TABLE departments (
  id INT PRIMARY KEY,
  name VARCHAR(20)
);

-- 员工表
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(20),
  salary DECIMAL(10,2),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES departments(id)
);

-- 示例数据
INSERT INTO departments VALUES 
(1, '研发部'), (2, '销售部'), (3, '人事部');

INSERT INTO employees VALUES
(101, '张三', 15000, 1),
(102, '李四', 12000, 1),
(103, '王五', 8000, 2),
(104, '赵六', 9000, 3),
(105, '钱七', 11000, 2);

一、按位置分类示例

1. WHERE 子查询（标量子查询）

-- 查询工资高于平均工资的员工
SELECT name, salary 
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

name | salary
-----|-------
张三 | 15000
李四 | 12000
钱七 | 11000

2. FROM 子查询（派生表）

-- 查询各部门平均工资
SELECT dept_id, ROUND(avg_sal, 0) AS avg_salary
FROM (
  SELECT dept_id, AVG(salary) AS avg_sal 
  FROM employees 
  GROUP BY dept_id
) AS dept_avg;

dept_id | avg_salary
--------|-----------
1       | 13500
2       | 9500
3       | 9000

3. SELECT 子查询（标量子查询）

-- 显示员工工资与公司平均工资的差值
SELECT 
  name,
  salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff 
FROM employees;

name | salary | diff
-----|--------|---------
张三 | 15000 | +3500
李四 | 12000 | +500
王五 | 8000  | -3500
...

二、按返回结果分类示例

1. 行子查询（单行多列）

-- 查找与张三部门和工资都相同的人
SELECT name 
FROM employees 
WHERE (dept_id, salary) = (
  SELECT dept_id, salary 
  FROM employees 
  WHERE name = '张三'
);

1
2
3

name
----
张三

2. 列子查询（单列多行）

-- 查找研发部和销售部的员工
SELECT name 
FROM employees
WHERE dept_id IN (
  SELECT id 
  FROM departments 
  WHERE name IN ('研发部', '销售部')
);

name
----
张三
李四
王五
钱七

3. 表子查询（多行多列）

-- 查询高工资员工（>10000）及其部门
SELECT e.name, d.name AS dept_name
FROM (
  SELECT * FROM employees WHERE salary > 10000
) AS e
JOIN departments d ON e.dept_id = d.id;

name | dept_name
-----|----------
张三 | 研发部
李四 | 研发部
钱七 | 销售部

三、按相关性分类示例

1. 非关联子查询

-- 查询比最高工资少的员工
SELECT name 
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

name
----
李四
王五
赵六
钱七

2. 关联子查询

-- 查询工资高于部门平均工资的员工
SELECT name, dept_id, salary
FROM employees e1
WHERE salary > (
  SELECT AVG(salary) 
  FROM employees e2 
  WHERE e2.dept_id = e1.dept_id
);

name | dept_id | salary
-----|---------|-------
张三 | 1       | 15000
钱七 | 2       | 11000

四、特殊类型示例

1. EXISTS 子查询

-- 查询有员工的部门
SELECT name 
FROM departments d
WHERE EXISTS (
  SELECT 1 
  FROM employees e 
  WHERE e.dept_id = d.id
);

name
----
研发部
销售部
人事部

2. CTE (WITH 子句) - MySQL 8.0+

-- 使用CTE查询高工资员工
WITH high_salary_emp AS (
  SELECT * FROM employees WHERE salary > 10000
)
SELECT name, salary FROM high_salary_emp;

name | salary
-----|-------
张三 | 15000
李四 | 12000
钱七 | 11000

五、优化示例（JOIN替代IN）

-- 原始IN子查询
SELECT name 
FROM employees
WHERE dept_id IN (SELECT id FROM departments WHERE name LIKE '%部');

-- 优化为JOIN
SELECT e.name 
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE d.name LIKE '%部';

关键提示：

标量子查询必须返回单值

派生表必须使用别名

EXISTS 更高效检查存在性

MySQL 8.0+ 优先使用CTE提高可读性

大数据量时用JOIN替代IN子查询

总结：MySQL 子查询形式速查表

分类维度	类型	常用操作符/写法	典型场景
位置	WHERE 子查询	`>`, `IN`, `EXISTS`	条件过滤
	FROM 子查询	派生表 + 别名	多步骤数据处理
	SELECT 子查询	标量子查询	添加计算列
返回结果	标量子查询	比较运算符	单值比较（如平均工资）
	行子查询	`(col1, col2) = (...)`	多列组合匹配
	列子查询	`IN`, `ANY/SOME`, `ALL`	多值匹配（如部门ID列表）
	表子查询	派生表	作为临时数据源
相关性	非关联子查询	独立执行	静态条件过滤
	关联子查询	外层列引用 (`e1.col=e2.col`)	分组内比较（如部门内工资）
特殊类型	`EXISTS/NOT EXISTS`	布尔判断	存在性检查（如是否有订单）
	CTE (`WITH` 子句)	MySQL 8.0+	复杂查询模块化