如何优化子查询？避免在WHERE子句中使用SELECT语句

原代码中WHERE子句的相关子查询会对table1的每一行单独执行一次子查询，当table1数据量较大时，性能会明显下降。以下是几种更高效的写法：

方案1：用CTE预聚合后关联 #

先一次性计算出table2中每个员工的最小DateLoad，再和table1关联匹配，避免重复计算：

WITH EmpMinDate AS (
    SELECT Employee_Number, MIN(DateLoad) AS MinDateLoad
    FROM dbo.table2
    GROUP BY Employee_Number
)
SELECT 
    a.Employee_Number,
    a.DateLoad
FROM dbo.table1 AS a
JOIN EmpMinDate AS b 
    ON a.Employee_Number = b.Employee_Number
WHERE a.DateLoad = b.MinDateLoad

方案2：用临时表（适合超大数据量场景） #

如果table2数据量极大，可以用临时表存储聚合结果，还能给临时表加索引进一步提速：

-- 预聚合到临时表
SELECT Employee_Number, MIN(DateLoad) AS MinDateLoad
INTO #EmpMinDate
FROM dbo.table2
GROUP BY Employee_Number

-- 添加聚簇索引加速关联
CREATE CLUSTERED INDEX IX_EmpMinDate_Employee ON #EmpMinDate(Employee_Number)

-- 关联查询
SELECT 
    a.Employee_Number,
    a.DateLoad
FROM dbo.table1 AS a
JOIN #EmpMinDate AS b 
    ON a.Employee_Number = b.Employee_Number
WHERE a.DateLoad = b.MinDateLoad

-- 清理临时表
DROP TABLE #EmpMinDate

方案3：用CROSS APPLY（优化器适配更灵活） #

和原逻辑结构接近，但APPLY的执行计划通常比相关子查询更优，尤其是当table2有合适索引时：

SELECT 
    a.Employee_Number,
    a.DateLoad
FROM dbo.table1 AS a
CROSS APPLY (
    SELECT MIN(DateLoad) AS MinDateLoad
    FROM dbo.table2 AS b
    WHERE b.Employee_Number = a.Employee_Number
) AS b
WHERE a.DateLoad = b.MinDateLoad

额外性能建议 #

• 给table1和table2的Employee_Number字段创建包含DateLoad的覆盖索引，避免回表查询：

  CREATE NONCLUSTERED INDEX IX_table1_Emp_Date ON dbo.table1(Employee_Number) INCLUDE(DateLoad)
  CREATE NONCLUSTERED INDEX IX_table2_Emp_Date ON dbo.table2(Employee_Number) INCLUDE(DateLoad)
  

• 尽量避免使用相关子查询（即依赖外部查询字段的子查询），这类子查询会逐行执行，而预聚合的方式只执行一次聚合操作，效率提升明显。

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