Changing from subquery to groub by… | SQL Server Performance Forums
SQL Server Performance Forum – Threads Archive
Changing from subquery to groub by…Hello everybody:
The following query finds all products that have a price greater than the average for products of its subcategory:
SELECT p1.ProductSubcategory, p1.Name
FROM Production.Product p1
WHERE p1.ListPrice >
FROM Production.Product p2
WHERE p1.ProductSubcategoryID = p1.ProductSubcategoryID)
is it possible re-write this query?? for example using self join, group by, etc…
Thanks in advance.
Not really a self join, but a derived table.
Move the subquery to the FROM clause. Add an alias for the calculated value AVG(p2.ListPrice) AS AvgListPrice, and an alias for the derived table, like X.
Drop the subquery’s WHERE clause, and use it as the ON statement for the INNER JOIN in the main query. Change the main query’s WHERE clause to WHERE p1.ListPrice > X.AvgListPrice.
However, SQL Server will probably use the exact same execution plan.