SQL – using ‘HAVING’ with ‘EXISTS’ without using ‘GROUP BY’

Using ‘HAVING’ without ‘GROUP BY’ is not allowed:

SELECT *
FROM products
HAVING unitprice > avg(unitprice)

Column ‘products.UnitPrice’ is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

But when placing the same code under ‘EXISTS’ – no problems:

SELECT *
FROM products p
WHERE EXISTS (SELECT 1
              FROM products
              HAVING p.unitprice > avg(unitprice))

Can you please explain why?

Answer

well the error is clear in first query UnitPrice is not part of aggregation nor group by whereas in your second query you are comparing p.unitprice from table “products p” which doesn’t need to be part of aggregation or group by , your second query is equivalent to :

select * from products p
where p.unitprice > (select avg(unitprice) FROM products)

which maybe this is more clear , that sql caculate avg(unitprice) then compares it with unitprice column from product.