SQL Script Comparison Question

Transact-SQL
1

Hello Community,

I have two SQL Scripts that both produce the exact same results. One script uses a subquery, while the other script uses straightforward SQL code.

The following is the sql script using subquery - it is taken from the book called 'Query Answers with SQL Server: Volume 2: In-Depth Querying.

SELECT      MK.MakeName
           ,COUNT(MK.MakeName) AS VehiclesSold
           ,SUM(SD.SalePrice) AS TotalSalesPerMake
FROM       Data.Make AS MK
INNER JOIN Data.Model AS MD ON MK.MakeID = MD.MakeID
INNER JOIN Data.Stock AS ST ON ST.ModelID = MD.ModelID
INNER JOIN Data.SalesDetails SD ON ST.StockCode = SD.StockID
WHERE      MakeName IN (
                        SELECT     TOP (3) MK.MakeName
                        FROM       Data.Make AS MK 
                        INNER JOIN Data.Model AS MD 
                                   ON MK.MakeID = MD.MakeID
                        INNER JOIN Data.Stock AS ST 
                                   ON ST.ModelID = MD.ModelID
                        INNER JOIN Data.SalesDetails SD 
                                   ON ST.StockCode = SD.StockID
                        INNER JOIN Data.Sales AS SA 
                                   ON SA.SalesID = SD.SalesID
                        GROUP BY    MK.MakeName
                        ORDER BY   COUNT(MK.MakeName) DESC
                       )
GROUP BY  MK.MakeName
ORDER BY  VehiclesSold DESC

The following is my script which produces the same results:

SELECT TOP 3
  Make.MakeName
 ,COUNT(Make.MakeName) AS VehiclesSold
 ,SUM(SalesDetails.SalePrice) AS TotalSalesPerMake
FROM Data.Make
INNER JOIN Data.Model
  ON Make.MakeID = Model.MakeID
INNER JOIN Data.Stock
  ON Model.ModelID = Stock.ModelID
INNER JOIN Data.SalesDetails
  ON Stock.StockCode = SalesDetails.StockID
INNER JOIN Data.Sales
  ON SalesDetails.SalesID = Sales.SalesID
GROUP BY Make.MakeName
ORDER BY VehiclesSold DESC

My question is why did the author use subquery to produce a result, which clearly doesn't need a subquery

The results appears as follows:
subquery

2

Hello Community,

Please let me know if you need further information to help me with this question?

Thanks

3

Maybe because of performance in the specific scenario (large data perhaps). Didn't the book explain why he/she chose this approach?
One does sometimes get a surprise (like I did here Need Help to Optimize Query)

4

Thanks reaching out bitsmed,

The author didn't give any specific reason why he chose his particular method, only to say the following:

Some queries are really two queries in one. In these cases, subqueries unlock the real power of SQL to allow you to carry out complex filtering that then becomes the basis of further analysis. This example extends the code from the previous example to show that the two queries that generate the final result are— and have to be— totally independent of one another. Even though the subquery counts the sales per make, you cannot pass this value back to the outer query. You have to count the number of vehicles sold twice: once in the subquery to act as a filter and once in the outer query to display the result. Otherwise this query is very similar to the previous one. The subquery finds the top three makes sold by quantity. Then these three makes are used as the filter by the outer query.

5

The example isn't the best - what it is trying to show is how a sub-query can be used to filter. What if we wanted to show the TOP 3 Makes - by Model? In other words - ORDER the sub-query by the count of ST.ModelID.

What if the sub-query comes from a different set of data? Or - you wanted to show the TOP 3 Makes by highest average sales?

Yes - you can change the query and get the same results in this case, but in other cases you would not be able to do that and arrive at the same results.