4 ms·
I'm confused, this doesn't seem right. This article, and several of the comments here, suggest that you cannot reference things calculated during SELECT in the
by chias 7y ago
I'm confused, this doesn't seem right. This article, and several of the comments here, suggest that you cannot reference things calculated during SELECT in the HAVING field. But you can.
As a quick demo, pick your favorite table and run:
SELECT 2+2 as num FROM <table> HAVING num > 3;
this will return as many 4s as there are rows in the table
SELECT 2+2 as num FROM <table> HAVING num > 4;
this will return 0 rows.
- emehrkay 7y agoI came here to ask the same question. I use having to filter select a lot. I use BigQuery, but it should hold up
- okonomiyaki3000 7y agoI was thinking the same thing. HAVING must be after SELECT. It doesn't really make sense otherwise.
- faizshah 7y agoLong story short, relying on HAVING to run after SELECT is not a standard SQL behavior it is dependent on the implementation and what the optimizer thinks is best (more discussion below: https://news.ycombinator.com/item?id=21153870 https://news.ycombinator.com/item?id=21153870). The diagram in the OP is a pretty good general/semantic understanding of SQL execution order for writing queries.
- thawaway1837 7y agoIsn’t this covered by the author in her example where she shows how an alias can still work because the DB engine can rewrite the alias as: SELECT 2 + 2 FROM <table> HAVING 2 + 2 > 3; SELECT 2 + 2 FROM <table> HAVING 2 + 2 > 4;