SQL Efficiency: WHERE IN Subquery vs. JOIN then GROUP

Question

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?

@Larsenal: you can replace a LEFT JOIN with an INNER JOIN in your second query, the results will be the same. A LEFT JOIN will return NULLs for the rows in ItemTag that do not have a corresponding Item.ID, and your WHERE condition will filter them out. — Quassnoi
– Quassnoi, Commented Jul 24, 2009 at 19:45

SIRHAMY · Accepted Answer · 2017-05-16 14:56:27Z

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

Otávio Décio · Accepted Answer · 2009-07-24 18:08:13Z

4

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.

answered Jul 24, 2009 at 18:08

Otávio Décio

74.6k18 gold badges167 silver badges230 bronze badges

Comments

Amy B · Accepted Answer · 2009-07-24 18:12:31Z

2

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.

answered Jul 24, 2009 at 18:12

Amy B

110k21 gold badges140 silver badges191 bronze badges

Comments

Spencer Ruport · Accepted Answer · 2009-07-24 18:10:29Z

1

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.

answered Jul 24, 2009 at 18:10

Spencer Ruport

35.2k12 gold badges89 silver badges150 bronze badges

2 Comments

Quassnoi Over a year ago

In fact, it's easy to say: the second one is way faster. It will just refuse to compile in a nanosecond or so.

Kasapo Over a year ago

@Quassnoi Wouldn't that make it slower? It takes an infinite amount of time to return the result...

KM. · Accepted Answer · 2009-07-24 18:16:00Z

0

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.

answered Jul 24, 2009 at 18:16

KM.

104k34 gold badges183 silver badges214 bronze badges

Comments

JeffO · Accepted Answer · 2009-07-24 19:53:18Z

0

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.

answered Jul 24, 2009 at 19:53

JeffO

8,1233 gold badges46 silver badges55 bronze badges

Comments

Viky Leaf · Accepted Answer · 2014-12-11 02:56:40Z

0

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

answered Dec 11, 2014 at 2:56

Viky Leaf

1955 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL Efficiency: WHERE IN Subquery vs. JOIN then GROUP

7 Answers 7

Comments

Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related