哪种类型的查询速度更快

Question

我将获取超过50k条记录，并将这些记录与另一个表进行匹配。查询如下：

SELECT * FROM
(SELECT *,
(SELECT TOP 1 t2.depart FROM table2 t2 WHERE t1.tripId = t2.tripID ORDER BY t2.tripID DESC) AS Depart,
(SELECT TOP 1 t2.arrival FROM table2 t2 WHERE t1.tripId = t2.tripID ORDER BY t2.tripID DESC) AS arrival
FROM table1 t1) x
WHERE x.Depart IS NOT NULL AND x.arrival IS NULL;

在上面的查询中，我正在对原始查询进行子查询，并应用where条件。我将获取超过5万条记录，所以这种方法是更快还是下面的方法：

SELECT * FROM table1 t1
WHERE 
(SELECT TOP 1 t2.depart FROM table2 t2 WHERE t1.tripId = t2.tripID ORDER BY t2.tripID DESC) IS NOT NULL 
AND
(SELECT TOP 1 t2.arrival FROM table2 t2 WHERE t1.tripId = t2.tripID ORDER BY t2.tripID DESC) 
IS NULL;

如有任何建议或改进，我们将不胜感激。非常感谢。

Answer 1

如果没有看到执行计划，很难说是肯定的，但这两个选项都不会有性能，因为您不必要地查询同一个表两次。

您可以通过使用APPLY并且只进行一次子查询来加快速度

SELECT *
FROM table1 t1
CROSS APPLY
(
SELECT TOP 1
t2.depart,
t2.arrival
FROM table2 t2
WHERE t1.tripId = t2.tripID
ORDER BY t2.tripID DESC
) t2
WHERE t2.depart IS NOT NULL
AND t2.arrival IS NULL;