Hacker News Reader

Comment by chias | Hacker News Reader

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.

View on HN

Isn’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;

I came here to ask the same question. I use having to filter select a lot. I use BigQuery, but it should hold up

I was thinking the same thing. HAVING must be after SELECT. It doesn't really make sense otherwise.

Long 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).

The diagram in the OP is a pretty good general/semantic understanding of SQL execution order for writing queries.

Reply on news.ycombinator.com