Correlated Subqueries in a HAVING Clause

Using sub queries in the HAVING clause is little different than using a sub query in any other part of the clause. The one thing that may through you is the WHERE clause inside the sub query… it acts as a JOIN criteria, so you might not get the placement right, until you’ve done it a few times.

The following example finds the product models for which the maximum list price is more than twice the average for the model.

USE AdventureWorks
GO
SELECT
	p1.ProductModelID
FROM Production.Product p1
GROUP BY
	p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL
	(SELECT
		2 * AVG(p2.ListPrice)
	 FROM Production.Product p2
	 WHERE p1.ProductModelID = p2.ProductModelID
	 )
GO

In this case, the sub query is evaluated once for each group defined in the outer query, that is, once for each model of product.

Again, there’s little difference in using a sub query in the HAVING clause. If you have any questions, send them in, I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *