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?



Read more here: https://stackoverflow.com/questions/67375601/sql-using-having-with-exists-without-using-group-by

Content Attribution

This content was originally published by Isaac Many at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: