问题描述
例如,我想获取应用了特定标签的所有项目的列表.我可以执行以下任一操作:
As an example, I want to get the list of all items with certain tags applied to them. I could do either of the following:
SELECT Item.ID, Item.Name FROM Item WHERE Item.ID IN ( SELECT ItemTag.ItemID FROM ItemTag WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)
或
SELECT Item.ID, Item.Name FROM Item LEFT JOIN ItemTag ON ItemTag.ItemID = Item.ID WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55 GROUP BY Item.ID, Item.Name
或者完全不同的东西.
总的来说(假设有一个通用规则),什么是更有效的方法?
In general (assuming there is a general rule), what's a more efficient approach?
推荐答案
SELECT Item.ID, Item.Name FROM Item WHERE Item.ID IN ( SELECT ItemTag.ItemID FROM ItemTag WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)
或
SELECT Item.ID, Item.Name FROM Item LEFT JOIN ItemTag ON ItemTag.ItemID = Item.ID WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55 GROUP BY Item.ID
您的第二个查询将无法编译,因为它引用了 Item.Name 而没有对其进行分组或聚合.
Your second query won't compile, since it references Item.Name without either grouping or aggregating on it.
如果我们从查询中删除 GROUP BY:
If we remove GROUP BY from the query:
SELECT Item.ID, Item.Name FROM Item JOIN ItemTag ON ItemTag.ItemID = Item.ID WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55
这些仍然是不同的查询,除非 ItemTag.ItemId 是一个 UNIQUE 键并被标记为这样.
these are still different queries, unless ItemTag.ItemId is a UNIQUE key and marked as such.
SQL Server 能够检测 UNIQUE 列上的 IN 条件,并且只会转换 IN> 条件转换为 JOIN.
SQL Server is able to detect an IN condition on a UNIQUE column, and will just transform the IN condition into a JOIN.
如果ItemTag.ItemID不是UNIQUE,第一个查询会使用一种SEMI JOIN算法,这种算法在SQL Server.
If ItemTag.ItemID is not UNIQUE, the first query will use a kind of a SEMI JOIN algorithm, which are quite efficient in SQL Server.
您可以将第二个查询转换为 JOIN:
You can trasform the second query into a JOIN:
SELECT Item.ID, Item.Name FROM Item JOIN ( SELECT DISTINCT ItemID FROMT ItemTag WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55 ) tags ON tags.ItemID = Item.ID
但是这个比 IN 或 EXISTS 效率低一点.
but this one is a trifle less efficient than IN or EXISTS.
有关更详细的性能比较,请参阅我博客中的这篇文章:
See this article in my blog for a more detailed performance comparison:
- IN vs. JOIN vs. EXISTS
- IN vs. JOIN vs. EXISTS