FROM, CTE and APPLY

The main table in FROM is usually the left table, and LEFT JOIN is really just the left table plus the intersection of the two, with the part of the right table that didn’t intersect coming back as null; INNER JOIN takes the intersection directly, only returning what both tables have.

CTE is a pretty interesting thing to do. What makes it different from a subquery is that it can be defined first and used later, which fits the form a human can read, from the outside in. A subquery is like turning into a nested if inside there, and you have to work through the logic from the inside out: when you see a subquery you read the inside first, then look at the outside and work backwards to figure out what it’s doing there. You can think of a subquery as logic used on the spot, and that’s exactly what it is when it runs, while a CTE is a logic variable declared up front. It doesn’t run before it gets called, it only works when something actually calls it. From the executor’s point of view there isn’t much difference from a subquery. Performance won’t be better, but it won’t be worse either.

-- CTE form: the logic is clear, define first, use after
WITH DeptAvg AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary 
    FROM Employees 
    GROUP BY DepartmentID
),
第二个 AS (SELECT ...)
SELECT * 
FROM DeptAvg    -- use this CTE the way you'd use an ordinary table
WHERE AvgSalary > 50000;

OUTER APPLY is another interesting one. This one is specific to SQL Server; in other databases it might be called something like JOIN LATERAL. If you want an analogy, it’s like a JOIN that comes with a correlated query. Normally, with the latter, the inside can’t reference columns of other tables outside it to correlate on, but OUTER APPLY can:

SELECT 
    A.列名, 
    B.列名
FROM 左表 AS A
OUTER APPLY (
    -- a subquery or table-valued function here can reference A.列名 directly
    SELECT TOP 1 * 
    FROM 右表 AS R 
    WHERE R.关联键 = A.关联键 
    ORDER BY R.排序键 DESC
) AS B;

JOIN can’t play like that. If I need to run a subquery inside a JOIN, that subquery can only be fully independent, there’s no way for it to also reference a column of the left table to match on. OUTER APPLY here corresponds to LEFT JOIN, where whatever didn’t match in the left table comes back NULL, and CROSS APPLY is INNER JOIN, only returning the intersection of the two tables.

This looks pretty usable, but there can be a performance cost. This kind of correlated matching is fundamentally a walk over every row of the left table, so it naturally comes with the classic iteration problem. If the left table has an enormous amount of data, the loop on the right side blows up. The way to solve that is to build indexes. If the right table has really good indexes, the loop stops exploding. The executor no longer needs to walk the whole table every time to match, it only needs to match exactly the item it wants. But even so the iteration problem is still there. Even if the match on the right can be squeezed down to a few exact rows, if the left table has millions of rows, then going from N-to-N down to N-to-1 row-by-row matching is still no small performance cost.

So where performance comes first this is better rewritten into some other faster form, but in the right situation, even in most small and medium cases, none of those worries apply. Compared with forcing a JOIN until it’s hard to understand and hard to maintain, a slight performance cost is completely acceptable. Say I need the TOP N of a child table: this APPLY form that can reference the outside is much easier to read than writing the JOIN directly.

---

Translation note. I wrote this in Chinese. This English version is an LLM translation, so the wording is not mine even though the thinking is. Original: FROM、CTE和APPLY.