两次左联接与联盟的表现 [英] Performance of two left joins versus union

问题描述

我已经搜索过但没有找到明确的答案.以下哪个对SQL Server的性能更好:

I have searched but have not found a definitive answer. Which of these is better for performance in SQL Server:

SELECT T.*
FROM dbo.Table1 T
    LEFT JOIN Table2 T2 ON T.ID = T2.Table1ID
    LEFT JOIN Table3 T3 ON T.ID = T3.Table1ID
WHERE T2.Table1ID IS NOT NULL
    OR T3.Table1ID IS NOT NULL

或...

SELECT T.*
FROM dbo.Table1 T
    JOIN Table2 T2 ON T.ID = T2.Table1ID
UNION
SELECT T.*
FROM dbo.Table1 T
    JOIN Table3 T3 ON T.ID = T3.Table1ID

我尝试同时运行这两种方法，但是很难确定.我会很高兴地解释一个为什么比另一个更快，或者这取决于情况.

I have tried running both but it's hard to tell for sure. I'd appreciate an explanation of why one is faster than the other, or if it depends on the situation.

推荐答案

您的两个查询没有做同样的事情.特别是，如果两个表中的值重复，则第一个将返回重复的行.

Your two queries do not do the same things. In particular, the first will return duplicate rows if values are duplicated in either table.

如果要在Table1中查找其他两个表之一中的行，则建议使用exists:

If you are looking for rows in Table1 that are in either of the other two tables, I would suggest using exists:

select t1.*
from Table1 t1
where exists (select 1 from Table2 t2 where t2.Table1Id = t1.id) or
      exists (select 1 from Table3 t3 where t3.Table1Id = t1.id);

然后，在Table2和Table3中的Table1Id上创建索引.

And, create indexes on Table1Id in both Table2 and Table3.

您的原始查询哪个更快，很大程度上取决于数据.第二个步骤有一个额外的步骤来删除重复项(union与union all).另一方面，第一个可能最终会创建许多重复的行.

Which of your original queries is faster depends a lot on the data. The second has an extra step to remove duplicates (union verses union all). On the other hand, the first might end up creating many duplicate rows.

这篇关于两次左联接与联盟的表现的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！