CTE（Common Table Expression，公用表表达式）与子查询（Subquery）平时都会使用，但其区别和运行效率区别还是比较大的，本文总结了CTE与子查询的区别，比较了运行效率，归纳应用场景。
1. CTE
CTE是一个临时结果集，在执行 SELECT、INSERT、UPDATE 或 DELETE 查询时，能够在查询中多次引用。CTE 通过 WITH 语句定义，可以使查询更易于阅读和理解，尤其是在处理复杂的查询时。
CTE 语法：
WITH CTE_Name AS
( SELECT column1, column2 FROM table WHERE condition
)
SELECT * FROM CTE_Name; CTE 特点：
- 临时性：CTE 只在当前查询的上下文中有效，不会保存到数据库中。
- 可重用：在一个查询中，CTE 可以被多次引用，这对于复杂查询非常有帮助。
- 可递归：CTE 支持递归查询，这使得它能够用于处理层次结构数据，如组织结构树或父子关系。
- 可读性：使用 CTE 可以让查询结构更清晰，尤其是当查询包含多个嵌套查询时，CTE 提供了更好的可读性。
2. 子查询
子查询是嵌套在其他查询（如 SELECT、INSERT、UPDATE 或 DELETE）中的查询，通常用于返回单个值或结果集。子查询可以在 SELECT 语句、WHERE 子句、FROM 子句等中使用。
子查询示例：
SELECT column1
FROM table
WHERE column2 IN (SELECT column2 FROM another_table WHERE condition); 子查询特点：
- 内联：子查询是嵌套在主查询中的，通常一次执行后立即使用。
- 可重用性差：子查询通常在查询中只能使用一次，不能像 CTE 那样在查询的多个地方重复使用。
- 没有递归能力：子查询不能直接处理递归关系，而 CTE 支持递归查询。
- 可能影响性能：某些类型的子查询（特别是非相关子查询）可能会导致 SQL Server 生成临时表，从而增加额外的资源开销。
3. CTE 与子查询的比较
| 特性 | CTE | 子查询 |
|---|---|---|
| 可读性 | 更清晰，尤其对于复杂查询。 | 可能导致查询嵌套过深，影响可读性。 |
| 可重用性 | 允许在查询的多个地方引用同一个 CTE。 | 每个子查询只能在查询中使用一次。 |
| 递归查询 | 支持递归查询，适合处理层次结构数据。 | 不支持递归查询。 |
| 执行顺序 | 在查询执行时与主查询一起执行。 | 在父查询执行前执行。 |
| 性能 | 性能通常较好，因为 CTE 可以被优化并且重用。 | 性能可能较差，尤其是在复杂的查询中。 |
4. 运行效率比较
在运行效率方面，CTE 和子查询在大多数情况下表现类似，但在某些特定场景下，它们的性能有所不同。
a. 子查询的性能：
- 相关子查询：子查询中的查询依赖于外部查询的结果，因此每执行一次外部查询，就会执行一次内部查询。这可能导致大量的计算，尤其是在数据量较大的时候。
- 非相关子查询：如果子查询不依赖于外部查询，则可以在主查询中使用一次计算结果，通常效率较高，执行计划通常会选择一种优化方式（例如转换为连接）。
b. CTE的性能：
- 与子查询相似：CTE 本质上是将查询的结果暂时存储在内存中，并在主查询中引用，因此它的性能表现通常与子查询相似。在大多数情况下，CTE 可能会比子查询更高效，尤其是在查询结构复杂的情况下。
- 递归查询：CTE 对递归查询有优势，能够有效处理层次结构数据，例如查询组织结构树、路径、父子关系等。在这种情况下，CTE 通常比多个嵌套子查询更高效。
c. 使用场景：
- 单次查询：如果查询只是一个简单的子查询，性能差异通常不会很明显。在这种情况下，选择 CTE 或子查询更多取决于查询的可读性和结构。
- 复杂查询或需要重用的查询：当查询结构复杂，需要在多个地方使用查询结果时，CTE 通常提供更好的性能和可读性。因为 CTE 允许在查询中多次引用，避免了重复计算。
- 递归查询：如果需要处理递归查询（例如，层次结构数据），CTE 是更合适的选择，性能也会更好。
d. 执行计划：
- 在 SQL Server 中，可以通过
SET STATISTICS IO和SET STATISTICS TIME查看子查询和 CTE 的执行计划。通常情况下，SQL Server 会尽可能优化 CTE 和子查询的执行，使它们尽可能高效。 - 对于大多数查询，CTE 和子查询的性能差异可能很小，除非查询中涉及到复杂的嵌套结构、重复计算或递归关系。
总结：
- CTE 更适用于复杂查询、递归查询以及需要重用查询结果的情况，通常在大多数场景下比子查询更具可读性和性能优势。
- 子查询 更适用于简单的嵌套查询或当需要执行一次性的计算时，尤其是非相关子查询性能较好。
- 在实际应用中，选择使用 CTE 还是子查询需要根据具体查询的复杂性和性能需求来决定。在处理递归查询或复杂查询时，CTE 是更好的选择。