Which is faster: JOIN with GROUP BY or a Subquery?

Question

Let's say we have two tables: 'Car' and 'Part', with a joining table in 'Car_Part'. Say I want to see all cars that have a part 123 in them. I could do this:

SELECT Car.Col1, Car.Col2, Car.Col3 
FROM Car
INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id
WHERE Car_Part.Part_Id = @part_to_look_for
GROUP BY Car.Col1, Car.Col2, Car.Col3

Or I could do this

SELECT Car.Col1, Car.Col2, Car.Col3 
FROM Car
WHERE Car.Car_Id IN (SELECT Car_Id FROM Car_Part WHERE Part_Id = @part_to_look_for)

Now, everything in me wants to use the first method because I've been brought up by good parents who instilled in me a puritanical hatred of sub-queries and a love of set theory, but it has been suggested to me that doing that big GROUP BY is worse than a sub-query.

I should point out that we're on SQL Server 2008. I should also say that in reality I want to select based the Part Id, Part Type and possibly other things too. So, the query I want to do actually looks like this:

SELECT Car.Col1, Car.Col2, Car.Col3 
FROM Car
INNER JOIN Car_Part ON Car_Part.Car_Id = Car.Car_Id
INNER JOIN Part ON Part.Part_Id = Car_Part.Part_Id
WHERE (@part_Id IS NULL OR Car_Part.Part_Id = @part_Id)
AND (@part_type IS NULL OR Part.Part_Type = @part_type)
GROUP BY Car.Col1, Car.Col2, Car.Col3

Or...

SELECT Car.Col1, Car.Col2, Car.Col3 
FROM Car
WHERE (@part_Id IS NULL OR Car.Car_Id IN (
    SELECT Car_Id 
    FROM Car_Part 
    WHERE Part_Id = @part_Id))
AND (@part_type IS NULL OR Car.Car_Id IN (
    SELECT Car_Id
    FROM Car_Part
    INNER JOIN Part ON Part.Part_Id = Car_Part.Part_Id
    WHERE Part.Part_Type = @part_type))

Answer 1

The best thing you can do is test them yourself, on realistic data volumes. That would not only benefit for this query, but for all future queries when you are not sure which is the best way.

Important things to do include:
- test on production level data volumes
- test fairly & consistently (clear cache: http://www.adathedev.co.uk/2010/02/would-you-like-sql-cache-with-that.html)
- check the execution plan

You could either monitor using SQL Profiler and check the duration/reads/writes/CPU there, or SET STATISTICS IO ON; SET STATISTICS TIME ON; to output stats in SSMS. Then compare the stats for each query.

If you can't do this type of testing, you'll be potentially exposing yourself to performance problems down the line that you'll have to then tune/rectify. There are tools out there you can use that will generate data for you.