有没有客观的理由更喜欢一种特定形式的左反半联合

Question

我见过很多不同形式的左反半联接。请允许我列出并说出我想到的每一个。以下查询旨在返回每个未被任何员工使用的ROSTER_ID以及该花名册的所有者。

--1) NOT EXISTS, with a particular column selected in the subquery
SELECT ROSTER_ID, ROSTER_OWNER
FROM ROSTERS
WHERE NOT EXISTS (SELECT EMP_ID FROM EMPLOYEES WHERE EMP_ID = ROSTERS.EMP_ID)

--2) NOT EXISTS, with a particular column selected in the subquery and TOP (1) used
SELECT ROSTER_ID, ROSTER_OWNER
FROM ROSTERS
WHERE NOT EXISTS (SELECT TOP (1) EMP_ID FROM EMPLOYEES WHERE EMP_ID = ROSTERS.EMP_ID)

--3) NOT EXISTS, with all data selected in the subquery
SELECT ROSTER_ID, ROSTER_OWNER
FROM ROSTERS
WHERE NOT EXISTS (SELECT * FROM EMPLOYEES WHERE EMP_ID = ROSTERS.EMP_ID)

--4) NOT EXISTS, with all columns selected in the subquery and TOP (1) used
SELECT ROSTER_ID, ROSTER_OWNER
FROM ROSTERS
WHERE NOT EXISTS (SELECT TOP (1) * FROM EMPLOYEES WHERE EMP_ID = ROSTERS.EMP_ID)

--5) NOT EXISTS, but just use SELECT 1
SELECT ROSTER_ID, ROSTER_OWNER
FROM ROSTERS
WHERE NOT EXISTS (SELECT 1 FROM EMPLOYEES WHERE EMP_ID = ROSTERS.EMP_ID)

--6) NOT IN
SELECT ROSTER_ID, ROSTER_OWNER
FROM ROSTERS
WHERE EMP_ID NOT IN (SELECT EMP_ID FROM EMPLOYEES)

--7) LEFT JOIN
SELECT ROSTER_ID, ROSTER_OWNER
FROM ROSTERS
LEFT OUTER JOIN EMPLOYEES
ON EMPLOYEES.EMP_ID = ROSTERS.EMP_ID
WHERE EMPLOYEES.EMP_ID IS NULL

我的问题是：是否有任何客观原因——例如性能、向后兼容性、可移植性、NULL处理、易于测试、可扩展性等——更喜欢任何特定的左反半联接方式我也很想听听主观原因，例如风格问题或清晰度，但只包括它们是不可能的。

我自己的研究只指出了以下几点，但都很薄弱，可能是主观的：

• Microsoft的U-SQL文档暗示他们更喜欢在T-SQL中使用NOT IN版本(我的#6)
• NULL与IN的处理总是引起关注，这是更喜欢EXISTS而不是它的温和原因
• 如果您非常关心向后兼容性，那么我认为我的LEFT JOIN语法在20世纪80年代不起作用
• 有些人喜欢使用EXCEPT，但我不认为它适用于列只出现在最外面的SELECT中的情况

Answer 1

否，除了NOT IN sucking

Aaron的文章已经充满了有趣的信息，您已经发现了将NOT IN与NULLS结合使用的危险。

我唯一可以补充的一点是，在使用(not) exists时，FROM之前的东西，如select column或select top 1 *，本质上是垃圾。我甚至向sql server发布了一条建议，建议使用(anti) semi join table2 on <join condition>的替代语法。

Answer 2

EXISTS、NOT EXISTS根据情况返回boooleanTRUE/FALSE。EXISTS子查询中提到的Select列无关紧要，它只是根据where cluase检查它是否存在

例如，

SELECT ROSTER_ID, ROSTER_OWNER
FROM ROSTERS
WHERE NOT EXISTS (SELECT 1/0 FROM EMPLOYEES WHERE EMP_ID = ROSTERS.EMP_ID)

在上面的通知中1/0不会抛出错误，这意味着什么？

因此，您的1)、2)、3)、4)、5)在性能和结果上都是相等的。

LEFT JOIN：当您需要结果集中LEFT JOIN表中的列时使用。

Answer 3

我不认为任何答案涵盖了所有内容，所以我尝试将所有答案和评论放在一起：

• 实施例1至5，即EXISTS实施例，都是相同的。这并不容易证明(你可能会发现KumarHarsh的例子令人信服)，并且依赖于你对优化器的信任
• 不要使用示例#6，即IN。你就是不能相信NULL
• 示例#7，即LEFT JOIN，如果您将其用于普通(即非anti)半联接，则可能会重复结果。也有人怀疑优化器将如何处理它(见下面的链接)。主观上，它需要更多的打字，并且不太善于传达你的意图。总的来说，最好避免

总之，使用EXISTS。示例#2和#4使用TOP，但这样做毫无意义，因此您最好避免使用它们。至于您是否应该使用示例#1、#3或#5，这只是一个品味问题。我认为第五条最清楚地传达了意图，但这完全是一个意见问题。

这篇非常有用的文章也建议使用OUTER APPLY，但我认为没有人认真对待这一点。