SQL中的CTE全称是公用表表达式，通过WITH关键字定义，很多开发者初次接触时会认为它只是把子查询提前声明的语法糖，作用仅仅是让SQL语句更整洁易读。但实际上CTE的能力远不止于此，它在功能特性和执行逻辑上都有普通子查询不具备的优势，适用于更多复杂查询场景。
CTE的基本用法
CTE的语法结构非常清晰，先通过WITH定义临时结果集，再在主查询中引用这个结果集。下面是一个简单的非递归CTE示例，查询员工表中薪资高于部门平均薪资的员工信息：
-- 定义CTE计算部门平均薪资
WITH dept_avg_salary AS (
SELECT
dept_id,
AVG(salary) AS avg_salary
FROM employee
GROUP BY dept_id
)
-- 主查询关联CTE筛选数据
SELECT
e.emp_id,
e.emp_name,
e.dept_id,
e.salary,
d.avg_salary
FROM employee e
JOIN dept_avg_salary d
ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;
CTE与普通子查询的差异
虽然CTE和子查询都能实现临时结果集的复用，但两者存在明显的区别：
- 可读性差异：CTE将临时逻辑提前声明，主查询逻辑更清晰，尤其是多层嵌套的子查询，改用CTE后结构会更直观。
- 多次引用特性：同一个CTE可以在主查询中多次引用，而普通子查询如果多次使用需要重复编写，或者依赖数据库优化器自动合并，CTE的多次引用逻辑更明确。
- 执行计划差异：部分数据库对CTE和子查询的执行计划处理不同，比如PostgreSQL中CTE默认是物化的，会把CTE的结果集先存储起来再使用，而普通子查询可能会被优化器合并到主查询中执行。
CTE独有的递归能力
递归CTE是普通子查询完全无法实现的功能，它用于处理层级结构数据，比如组织架构、商品分类树、评论嵌套等场景。递归CTE由两部分组成：锚定成员和递归成员，通过UNION ALL连接。
以下是一个查询员工上下级层级关系的递归CTE示例，假设员工表employee有emp_id、emp_name、manager_id字段，manager_id是上级的emp_id：
-- 递归CTE查询所有员工的层级关系
WITH RECURSIVE emp_hierarchy AS (
-- 锚定成员：查询最高级领导（没有上级的员工）
SELECT
emp_id,
emp_name,
manager_id,
1 AS level,
CAST(emp_name AS CHAR(1000)) AS hierarchy_path
FROM employee
WHERE manager_id IS NULL
UNION ALL
-- 递归成员：关联下级员工，层级加1，拼接路径
SELECT
e.emp_id,
e.emp_name,
e.manager_id,
eh.level + 1,
CONCAT(eh.hierarchy_path, ' -> ', e.emp_name)
FROM employee e
JOIN emp_hierarchy eh
ON e.manager_id = eh.emp_id
)
-- 查询所有员工的层级和路径
SELECT
emp_id,
emp_name,
level,
hierarchy_path
FROM emp_hierarchy
ORDER BY level, emp_id;
这个查询如果不使用递归CTE，用普通子查询几乎无法实现，只能依赖应用程序端的多次查询拼接，效率和使用体验都会差很多。
CTE的性能表现
关于CTE的性能，不能一概而论说它比子查询好或者差，需要结合具体数据库的实现来看。比如SQL Server中CTE和子查询的执行计划通常是一致的，优化器会做相同的处理；而MySQL 8.0之后对CTE的支持中，递归CTE是必须要使用CTE才能实现的，普通子查询不支持递归语法。
在需要多次引用同一个临时结果集的场景下，CTE可以避免重复编写子查询，也能让优化器更清晰地识别复用逻辑，有时候能获得更好的执行效率。但如果只是简单的单次使用的临时结果集，CTE和普通子查询的性能差异通常可以忽略。
总结
CTE绝对不是单纯的语法糖，它在提升查询可读性、支持递归查询、明确结果集复用逻辑等方面都有普通子查询不具备的优势。尤其是递归CTE的功能，是SQL处理层级数据的核心方案之一。开发者在使用SQL时，可以根据场景选择CTE：如果是复杂的多层级查询、需要多次复用的临时结果集，优先使用CTE；如果是简单的单次临时逻辑，CTE和子查询都可以选择，根据代码可读性需求决定即可。