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?