SQL中WITH与IN的用法及优化教程

原创于 2025-11-27 16:44:31 发布 · 424 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#sql

一、WITH语句基础用法

1.1 公用表表达式（CTE）

WITH语句通过定义临时结果集简化复杂查询，常用于分解多步骤查询逻辑：

-- 基础语法
WITH cte_name AS (
    SELECT column1, column2 FROM table WHERE condition
)
SELECT * FROM cte_name;

-- 示例：计算各区域销售额并筛选Top区域
WITH regional_sales AS (
    SELECT region, SUM(amount) AS total_sales
    FROM orders
    GROUP BY region
),
top_regions AS (
    SELECT region FROM regional_sales 
    WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT o.region, o.product, SUM(o.quantity) 
FROM orders o
WHERE o.region IN (SELECT region FROM top_regions)
GROUP BY o.region, o.product;

特点：

支持递归查询（RECURSIVE关键字）
可被多次引用，提升代码可读性

1.2 递归查询

通过UNION ALL实现层级遍历：

WITH RECURSIVE employee_hierarchy AS (
    SELECT id, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.manager_id 
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy;

应用场景：组织架构树形查询、图结构遍历

二、IN操作符深度解析

2.1 基本语法

SELECT * FROM table1 
WHERE column1 IN (value1, value2, ...);

2.2 性能对比

场景	IN表现	推荐替代方案
子查询结果集小	高效（先执行子查询）	JOIN更优
子查询结果集大	低效（全表扫描）	EXISTS更优
主表数据量大	全表扫描	EXISTS+索引

性能优化示例：

-- 低效写法
SELECT * FROM orders 
WHERE customer_id IN (SELECT id FROM customers WHERE country='CN');

-- 优化1：JOIN替代
SELECT o.* FROM orders o
JOIN customers c ON o.customer_id = c.id 
WHERE c.country='CN';

-- 优化2：EXISTS替代
SELECT * FROM orders o
WHERE EXISTS (
    SELECT 1 FROM customers c 
    WHERE c.id=o.customer_id AND c.country='CN'
);

三、WITH与IN的组合应用

3.1 递归+条件过滤

WITH RECURSIVE product_tree AS (
    SELECT product_id, parent_id FROM products WHERE parent_id IS NULL
    UNION ALL
    SELECT p.product_id, p.parent_id 
    FROM products p
    JOIN product_tree pt ON p.parent_id = pt.product_id
)
SELECT * FROM product_tree 
WHERE product_id IN (SELECT id FROM top_products);

3.2 临时结果集复用

WITH filtered_orders AS (
    SELECT order_id FROM orders WHERE amount > 1000
)
SELECT customer.* 
FROM customers customer
WHERE customer.id IN (SELECT order_id FROM filtered_orders);

四、性能优化指南

4.1 索引策略

为IN子查询的字段建立索引
递归查询中为关联字段添加索引

4.2 执行计划分析

使用EXPLAIN观察：

EXPLAIN 
SELECT * FROM employees 
WHERE department_id IN (SELECT id FROM departments WHERE location='BJ');

重点关注：

type字段是否为ref或range
rows扫描行数
Extra中的Using where提示

4.3 大数据量处理

数据量	优化方案
子查询>1万行	改用JOIN或临时表
频繁查询	创建物化视图
分布式场景	分片查询+结果聚合

五、数据库差异对比

特性	MySQL	PostgreSQL	SQL Server
递归查询支持	8.0+支持	全版本支持	2005+支持
临时表生命周期	会话级	事务级	批处理级
最佳实践	优先使用JOIN替代IN	推荐WITH递归处理树形结构	使用CTE优化复杂查询