在实际的数据库开发和分析中，我们常常会遇到复杂的多层嵌套查询，这样的 SQL 语句不仅难以阅读，也容易出错。 这时候就需要使用一个非常实用又优雅的关键字 —— WITH！ 它可以帮助我们将复杂的子查询提取出来并命名，从而提升代码可读性、复用性和维护性。这个功能也被称为 CTE（Common Table Expressions，公用表表达式）。

一、WITH语句基础用法

WITH 是 SQL 中用于定义临时结果集的关键字。这些临时结果集可以在后续查询中像普通表一样被引用，并且只在当前查询执行期间存在。

你可以把它理解为：“先写好一个中间结果，后面可以直接拿来用”。

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;

特点：

1. 递归能力：支持递归查询（RECURSIVE关键字）
2. 可读性：可被多次引用，提升代码可读性
3. 临时结果集：CTE只在当前查询执行期间存在

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 性能对比

性能优化示例：

-- 低效写法
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 索引策略

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

4.2 执行计划分析

使用EXPLAIN观察：

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

重点关注：

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

4.3 大数据量处理

五、数据库差异对比

典型错误规避：

1. 避免在NOT IN中使用可能含NULL的子查询
2. 递归查询需设置终止条件（如depth < 10）
3. 分布式数据库慎用跨节点CTE

通过合理组合WITH的查询复用能力和IN的条件筛选，可显著提升SQL可读性和执行效率。建议在实际开发中结合EXPLAIN分析执行计划，针对具体场景选择最优方案。