当前位置：首页 > java >正文

SQL 子查询全位置解析：可编写子查询的 7 大子句

java 2025/6/29 20:20:05

🔍 SQL 子查询全位置解析：可编写子查询的 7 大子句

子查询可以出现在 SQL 语句的多个关键位置，不同位置的子查询具有独特的行为和限制。以下是系统化总结：

📌 1. WHERE 子句（最常用）

SELECT 列 
FROM 表 
WHERE 列 操作符 (SELECT ...);

类型：

标量子查询（单值）
行子查询（单行多列）
集合子查询（多行单列）
示例：

-- 标量子查询
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);-- 集合子查询
SELECT * FROM customers
WHERE id IN (SELECT cust_id FROM orders);

📊 2. FROM 子句（派生表）

SELECT 列 
FROM (SELECT ...) AS 别名 
WHERE 条件;

特点：

必须指定别名
可包含任意复杂查询
实质是创建临时视图
示例：

SELECT dept, avg_sal
FROM (SELECT dept_id, AVG(salary) AS avg_salFROM employeesGROUP BY dept_id
) AS dept_avg;

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

SELECT 列,(SELECT ...) AS 别名 
FROM 表;

限制：

必须返回单行单列
通常为关联子查询（引用外部列）
每行都会执行一次
示例：

SELECT name,salary,(SELECT AVG(salary) FROM employees) AS avg_sal,salary - (SELECT AVG(salary) FROM employees) AS diff
FROM employees;

🔍 4. HAVING 子句

SELECT 聚合列 
FROM 表 
GROUP BY 列 
HAVING 聚合函数(列) 操作符 (SELECT ...);

特点：

在分组后执行
可访问聚合函数结果
示例：

SELECT dept_id, AVG(salary) 
FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

🔄 5. JOIN 子句

SELECT 列 
FROM 表1 
JOIN (SELECT ...) AS 别名 ON 连接条件;

优势：

预先过滤/聚合连接表
减少连接数据量
示例：

SELECT c.name, o.order_count
FROM customers c
JOIN (SELECT cust_id, COUNT(*) AS order_countFROM ordersGROUP BY cust_id
) o ON c.id = o.cust_id;

⚙️ 6. INSERT 语句

插入数据来源

INSERT INTO 目标表 (列)
SELECT ... FROM 源表;

示例：

INSERT INTO premium_users (id, name)
SELECT id, name 
FROM users
WHERE total_spend > 10000;

插入值计算

INSERT INTO 表 (列1, 列2) 
VALUES ((SELECT ...), (SELECT ...)
);

限制：

每个子查询必须返回单值
示例：

INSERT INTO stats (total_users, avg_salary)
VALUES ((SELECT COUNT(*) FROM users),(SELECT AVG(salary) FROM employees)
);

✏️ 7. UPDATE 语句

UPDATE 表 
SET 列 = (SELECT ...) 
WHERE 条件;

关键点：

SET 子句的子查询必须返回单值
WHERE 子句可嵌套子查询
示例：

-- 更新员工薪资为部门平均
UPDATE employees e
SET salary = (SELECT AVG(salary)FROM employees WHERE dept_id = e.dept_id
);

⚠️ 8. 特殊位置注意事项

ORDER BY 子句（少用）

SELECT 列 
FROM 表 
ORDER BY (SELECT ...);

限制：

子查询必须返回单值
每行执行一次，性能差
示例：

SELECT name, salary
FROM employees
ORDER BY (SELECT AVG(salary) FROM employees);

CREATE VIEW 语句

CREATE VIEW 视图名 AS
SELECT ... FROM (SELECT ...);

⚠️ 子查询通用注意事项

NULL 处理

NOT IN 遇 NULL 返回空集 → 用 NOT EXISTS 替代

-- 危险
WHERE id NOT IN (SELECT ...) -- 安全
WHERE NOT EXISTS (SELECT 1 FROM ...)

性能陷阱

关联子查询（Correlated Subquery）导致 O(n²) 复杂度

解决方案：

-- 低效
SELECT * FROM t1 
WHERE col = (SELECT ... FROM t2 WHERE t2.id = t1.id)-- 高效：转为 JOIN
SELECT t1.* 
FROM t1 
JOIN (SELECT ... FROM t2) sub ON t1.id = sub.id

返回结果限制

位置 允许的行/列 是否需别名
WHERE 单行或多行（取决操作符）否
FROM 任意是
SELECT 单行单列 可选
HAVING 单行或多行否
SET (UPDATE) 单行单列 否

位置	允许的行/列	是否需别名
WHERE	单行或多行（取决操作符）	否
FROM	任意	是
SELECT	单行单列	可选
HAVING	单行或多行	否
SET (UPDATE)	单行单列	否

可读性优化

超过 2 层嵌套时改用 CTE（公共表表达式）：

-- 嵌套子查询（难维护）
SELECT ...
FROM (SELECT ... FROM (SELECT ...)) -- CTE 优化版
WITH step1 AS (SELECT ...),step2 AS (SELECT ... FROM step1)
SELECT ... FROM step2;

💎 子查询位置决策指南

使用场景	首选位置	替代方案
行级条件过滤	WHERE	JOIN
创建临时数据集	FROM	CTE/临时表
动态计算列值	SELECT	应用层计算
分组后过滤	HAVING	子查询 + WHERE
批量插入数据	INSERT SELECT	ETL工具
基于查询结果更新	UPDATE SET	多语句事务
复杂数据集连接前预处理	JOIN	物化视图