title: Which is faster: JOIN with GROUP BY or a Subquery?
body: <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Or I could do this</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Or...</p>
  
  <pre><code>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))
  </code></pre>