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 >
(SELECT AVG(p2.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.
Hernán.

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.

]]>

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |