4 ms·

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 <t

by chias 7y ago

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?

faizshah 7y ago
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-alias-in-where-clause-of-mysql-query-produces-an-error 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-alias-in-a-where-clause-doesnt-work 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-alias-in-sqlite3-where-clause 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.
emmelaich 7y ago
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 :-)

by chias 7y ago

faizshah 7y ago

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-alias-in-where-clause-of-mysql-query-produces-an-error 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-alias-in-a-where-clause-doesnt-work 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-alias-in-sqlite3-where-clause 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.

emmelaich 7y ago

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 :-)