SQL Efficiency: WHERE IN Subquery vs. JOIN then GR

问题:

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)

Or

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

Or something entirely different.

In general (assuming there is a general rule), what's a more efficient approach?

回答1:

SELECT Item.ID, Item.Name
FROM Item
WHERE Item.ID IN (
    SELECT ItemTag.ItemID
    FROM ItemTag
    WHERE ItemTag.TagID = 57 OR ItemTag.TagID = 55)

or

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

Your second query won't compile, since it references Item.Name without either grouping or aggregating on it.

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

these are still different queries, unless ItemTag.ItemId is a UNIQUE key and marked as such.

SQL Server is able to detect an IN condition on a UNIQUE column, and will just transform the IN condition into a JOIN.

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.

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

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

回答2:

I think it would depend on how the optimizer handles them, it may even be the case that you end up with the same performance. Display execution plan is your friend here.

回答3:

SELECT Item.ID, Item.Name
...
GROUP BY Item.ID

This is not valid T-SQL. Item.Name must appear in the group by clause or within an aggregate function, such as SUM or MAX.

回答4:

It's pretty much impossible (unless you're one of those crazy guru DBAs) to tell what will be fast and what won't without looking at the execution plan and/or running some stress tests.

回答5:

run this:

SET SHOWPLAN_ALL ON

then run each version of the query

you can see if they return the same plan, and if not look at the TotalSubtreeCost on the first row of each and see how different they are.

回答6:

Performance always seems to get the vote, but you also hear "it is cheaper to buy hardware than programmers"

The second wins on performance.

Sometimes it is nice to look at SQL and know the purpose, but that's what comments are for. The first query is using the other table for a filter - pretty straight forward.

The second one would make more sense (from an understanding purpose and not performance) using distinct instead of group by. I would expect some aggregates to be in the select, but there aren't any. Speed kills.

回答7:

The second one is more efficient in MySQL. MySQL will re-execute the query within the IN statement for every WHERE condition test.