Hacker News Reader

Comment by faizshah | Hacker News Reader

In my experience the execution order isn’t obvious to most sql users. You’ll find a lot of people using sql who don't understand why something from SELECT can be used in ORDER BY but not WHERE or HAVING for example. It takes a slightly more advanced SQL understanding to be able to explain these kinds of errors in thinking. Thats why a good ORM can often be the best choice for users.

View on HN

Can't you though?

    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. How is this not using something from SELECT in a HAVING clause?

Like with many SQL questions this depends on what implementation you're using.

In MySQL you can use HAVING, ORDER BY, and GROUP BY with column aliases but not WHERE: https://stackoverflow.com/questions/942571/using-column-alia...

In PostgreSQL you can not use the column alias within WHERE or HAVING but you can use it in ORDER BY and GROUP BY: https://dba.stackexchange.com/questions/225874/using-column-...

Similarly in Microsoft SQL server you cannot use it in WHERE, HAVING, or GROUP BY but you can use it in ORDER BY.

In SQLite you can use column aliases within WHERE, HAVING, ORDER BY and GROUP BY: https://stackoverflow.com/questions/10923107/using-a-column-...

In Google Bigquery you can use them in GROUP BY and ORDER BY but in Hive you can only use them in ORDER BY.

As far as I know the standard only defines that you cannot use column aliases in the WHERE clause. I'm sure someone else can chime in with what the standard says about column aliases in ORDER BY, GROUP BY and HAVING.

HAVING applies to groups. Sqlite3 doesn't allow HAVING without GROUP BY.

If a sql dialect doesn't require the GROUP BY then I think the entire select result is one group. So you'd only get either 1 or 0 rows ever.

Or so I think :-)

Reply on news.ycombinator.com