SQL 效率:WHERE IN 子查询 vs. JOIN 然后 GROUP

2021-09-10 00:00:00 performance tsql sql-server

例如，我想获取应用了特定标签的所有项目的列表.我可以执行以下任一操作:

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

相关文章