Performance gain with Subquery instead of group

May 12, 2008

I've often used SubQueries in SQL for filtering data, but I've not tried using one in a select statement. I recently had a query that was running slowly due to the number of fields referenced in the group by clause. By changing it to use a SubQuery in the select it has actually reduced the execution time.

Below are two queries that return the same set of data. The first uses an outer join and group by clause, the second uses a subquery instead.

Using a group clause:

SELECT s.supplier_name
  , Count(w.worker_id) as workerscount
FROM [Suppliers] AS s
  LEFT OUTER JOIN [Workers] As w ON ( 
    s.supplier_id = w.worker_supplier_id
  )
GROUP BY supplier_name

Using a subquery:

SELECT s.supplier_name
  , ( 
    SELECT Count(w.worker_id) as workerscount 
    FROM [Workers] as w
    WHERE s.supplier_id = w.worker_supplier_id 
  )
FROM suppliers as s

A slight oddity is that for some reason MS SQL server returns the resulting data in a different order. Not a problem as it is easily fixed with a order by clause, it just surprised me!

2 comments

  1. Perhaps a better route yet might be:

    SELECT s.supplier_name, wc.workerscount
    FROM Suppliers s
    LEFT JOIN (
    SELECT w.worker_supplier_id, count(*) as workerscount
    FROM workers w
    GROUP BY w.worker_supplier_id
    ) wc ON wc.worker_supplier_id = s.supplier_id

    Comment by Adam Ness – May 14, 2008
  2. Hi Adam,

    Thanks - that's neat. I've been writing SQL statements for a decade and never seen it done like that before! There is a minor difference in the returned results, in that your query returns NULL instead of 0 for workerscount if no matches are found. But easy enough to handle this in code or with a case statement like this:

    SELECT s.supplier_name, s.supplier_id,
    CASE WHEN wc.workerscount IS NULL Then 0
    ELSE wc.workerscount
    END as workerscount
    FROM Suppliers s LEFT JOIN (
    SELECT w.worker_supplier_id, Count(*) as workerscount
    FROM workers w
    GROUP BY w.worker_supplier_id
    ) wc ON wc.worker_supplier_id = s.supplier_id

    Comment by John Whish – May 14, 2008

Leave a comment

If you found this post useful, interesting or just plain wrong, let me know - I like feedback :)

Please note: If you haven't commented before, then your comments will be moderated before they are displayed.