Why the query is successful even if we use alias in HAVING clause

Q&A DQ Courses
#1

Screen Link:

My Code: 

SELECT 
    Major_category,
    ROUND(AVG(College_jobs)/AVG(Total), 3) as Share_degree_jobs
FROM new_grads
GROUP BY Major_category
HAVING Share_degree_jobs < 0.3

What I expected to happen:
The SQL query is being executed in the following plan:

  • FROM
  • WHERE
  • GROUP BY
  • HAVING
  • SELECT
  • ORDER BY
  • LIMIT

What actually happened:

We can use alias in the HAVING clause showed in the query

As the Having clause is executed before Select, it means the alias defined in the SELECT part should not be handed over to the Having clause. However the query is successful.
What is the reason behind this?

Also, why cant we use alias in the ORDER BY clause?

#2

Some SQL variants like SQLite and MySQL allow for this.

I couldn’t really find the SQLite documentation specifically stating this (maybe I didn’t look properly) but as per - SQLite Forum: Aliased function result with the same name issue

Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause.

In other variants, it should not be possible, apparently, but these 2 seem to allow for it.

#3

I collected some column alias allowed/disallowed patterns here: From Zero to Hero in SQL

#4

Thanks a lot :slight_smile:
Appreciate your help.