如何筛选子查询生成的[ProductId]为NULL的采购订单数据？

方法一：使用CTE（公共表表达式） #

先通过CTE生成包含子查询结果的临时数据集，再在外层过滤目标列的NULL值，同时可以给生成的列起个不重复的名字避免冲突：

WITH PurchaseOrderResults AS (
    SELECT 
        [Id], [Number] AS [Purchase Order], [OldProduct] AS [Old Product], 
        (SELECT [Id]
         FROM  
             (SELECT [Id], [Name]
              FROM [Products]
              UNION ALL
              SELECT [ProductId] AS [Id], [Alias] AS [Name]
              FROM [ProductAliases]) AS [ProductNames]
         WHERE [ProductNames].[Name] = po.[OldProduct]) AS [DerivedProductId]
    FROM 
        [PurchaseOrders] po
)
SELECT [Id], [Purchase Order], [Old Product], [DerivedProductId] AS [ProductId]
FROM PurchaseOrderResults
WHERE [DerivedProductId] IS NULL;

方法二：嵌套子查询 #

直接把原查询作为内层子查询，在外层直接过滤生成的ProductId列，此时外层引用的是子查询生成的列，不会和原表的ProductId混淆：

SELECT [Id], [Purchase Order], [Old Product], [ProductId]
FROM (
    SELECT 
        [Id], [Number] AS [Purchase Order], [OldProduct] AS [Old Product], 
        (SELECT [Id]
         FROM  
             (SELECT [Id], [Name]
              FROM [Products]
              UNION ALL
              SELECT [ProductId] AS [Id], [Alias] AS [Name]
              FROM [ProductAliases]) AS [ProductNames]
         WHERE [ProductNames].[Name] = po.[OldProduct]) AS [ProductId]
    FROM 
        [PurchaseOrders] po
) AS SubQueryResults
WHERE [ProductId] IS NULL;

方法三：使用OUTER APPLY替换子查询 #

用OUTER APPLY将子查询转换为关联查询，这样可以直接在主查询的WHERE子句中引用子查询返回的列，明确指定引用的是子查询的结果：

SELECT 
    po.[Id], po.[Number] AS [Purchase Order], po.[OldProduct] AS [Old Product], 
    pa.[Id] AS [ProductId]
FROM 
    [PurchaseOrders] po
OUTER APPLY (
    SELECT [Id]
    FROM  
        (SELECT [Id], [Name]
         FROM [Products]
         UNION ALL
         SELECT [ProductId] AS [Id], [Alias] AS [Name]
         FROM [ProductAliases]) AS [ProductNames]
    WHERE [ProductNames].[Name] = po.[OldProduct]
) AS pa
WHERE pa.[Id] IS NULL;

内容的提问来源于stack exchange，提问作者Jonathan Wood