随笔

FROM、CTE和APPLY

2026-06 简体中文

FROM 的主表一般就是左表，而 LEFT JOIN本质上就是取左表+两表交集的部分，剩下的右表未交集部分则直接为null；而INNER JOIN则是直接取交集，两表都有的才返回。

CTE 是个很有意思的操作，它跟子查询不一样的点是，其本身可以先定义再使用，更符合人类可读的形式——从外向内。而子查询就跟变成里面的嵌套if一样，你得从内向外的取梳理逻辑：看到一个子查询的时候得先看里面，再去看外面倒推这里在干嘛。子查询可以理解为现场使用的逻辑，而执行上来说确实如此，CTE则是预先声明的逻辑变量。在没被调用之前并不会执行，只有真的被调用时才会运作。而从执行器的角度来看，它跟子查询没什么太大差别，性能不会更好，但也不会更坏。

-- CTE 写法：逻辑清晰，先定义，后使用
WITH DeptAvg AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary 
    FROM Employees 
    GROUP BY DepartmentID
),
第二个 AS (SELECT ...)
SELECT * 
FROM DeptAvg    -- 像使用普通表一样使用这个 CTE
WHERE AvgSalary > 50000;

OUTER APPLY 也是一个有意思的操作。这是一个SQL Server特有的操作，其他数据库里可能叫JOIN LATERAL之类的。类比的话，就像是带了关联查询的JOIN。一般情况下，对于后者来说，它内部是无法引用外部其他表的字段来进行关联的——但是，OUTER APPLY就可以：

SELECT 
    A.列名, 
    B.列名
FROM 左表 AS A
OUTER APPLY (
    -- 这里是可以直接引用 A.列名 的子查询或表值函数
    SELECT TOP 1 * 
    FROM 右表 AS R 
    WHERE R.关联键 = A.关联键 
    ORDER BY R.排序键 DESC
) AS B;

JOIN就没办法这么玩儿。如果我需要在JOIN里面进行一次子查询，那么这个子查询就只能是完全独立的，不可能还引用左表的字段来进行匹配。OUTER APPLY 在这里对应的是 LEFT JOIN，左表里面没匹配到的会返回 NULL，而 CROSS APPLY 则是 INNER JOIN，只会返回左右两表的交集。

这个看上去其实蛮好用的，但它可能会存在性能损失。因为这种关联匹配本质上是对左表的每行进行遍历，也自然存在经典的遍历问题——如果左表数据量极其庞大，那右边的循环就会爆炸。解决此问题的方法就是建立索引，如果右表有非常良好的索引，那么循环爆炸就会消失。执行器不再需要每次遍历全表来进行匹配，只需要精确匹配到指定项即可。但是，这种情况下遍历问题仍然存在，即便右表的匹配可以压缩到精确的几行，但如果左表几百上千万行，那就算是从N对N优化到N对1的逐行匹配，性能损失仍然不小。

因此在性能优先的场合下，这个最好还是优化成别的性能写法，但在合适的情况下，甚至是大多数中小场合下，倒是没有这些担忧。毕竟比起硬写JOIN写到理解和维护困难，轻微的性能损失完全是可接受的。比如我需要获取子表的TOP N，那这种能关联外部的APPLY写法，就比直接写JOIN要好读的多。