問題描述

例如，我想獲取應用了特定標簽的所有項目的列表.我可以執行以下任一操作:

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

這篇關于SQL 效率:WHERE IN 子查詢 vs. JOIN 然后 GROUP的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！