Search code examples
sqlsubquerycorrelated

SQL correlated subquery returning no results

I'm trying to better understand how correlated subqueries work. Using https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_all

the query below is my attempt to use a correlated subquery to list the products with price greater than the average price of all products. It returns no records.

SELECT p1.ProductID, p1.Price
FROM PRODUCTS p1
WHERE p1.Price > 
    (SELECT AVG(p2.Price)
    FROM PRODUCTS p2
        WHERE p2.ProductID = p1.ProductID);

Number of Records: 0

ProductID Price

When I modify the query to run as a non-correlated subquery (below), it runs fine and returns results.

SELECT p1.ProductID, p1.Price
FROM PRODUCTS p1
WHERE p1.Price > 
    (SELECT AVG(p2.Price)
    FROM PRODUCTS p2);

Number of Records: 25

ProductID Price
7 30
8 40
9 97
10 31
etc.

What am I missing? Why is the correlated subquery failing to return any records? I would expect that when "WHERE p2.ProductID = p1.ProductID" evaluates to true, the nested query would return AVG(p2.Price) and compare it to p1.Price. When p1.Price is greater than AVG(p2.Price), the record should be added to the result set. Any ideas why this doesn't work?

Solution

  • this would work, as it select the avg that have a bigger ProductId

    SELECT * FROM Products p2
WHERE Price > (SELECT AVG(Price) FROM Products WHERE ProductID > p2.ProductID);

    result: enter image description here

    your problem, is that the correlated subquery will only get you the Avaerage of the one product that is in the row, so the price and AVG(Price= is identical, as only one value is present to be calculated, so it never can be bigger.

    If you change the WHERE to something that gets more than one row, you can get results