5 ms·

> Per the SQL standard, you can't use column aliases in WHERE clauses, because the selection (again, relational algebra) occurs before the projection. Except t

> Per the SQL standard, you can't use column aliases in WHERE clauses, because the selection (again, relational algebra) occurs before the projection. Except this works in most major vendor SQL implementations. And they all support relation aliases in SELECT... Seems the standards have long fell behind actual implementations.

sgarland 1y ago
To clarify, I mean you can’t do this: SELECT id AS foo FROM MyTable WHERE foo = 1; Similarly, you can’t do this: SELECT id FROM MyTable WHERE id = MAX(id); Because in both cases, when the WHERE predicate is being executed, the engine doesn’t yet know what you’re asking it to find - for the former, SELECT hasn’t yet been called, so the alias isn’t applied; for the latter, the aggregation happens after filtering, so it can’t know what that maximum value is. You can of course alias columns for SELECT however you’d like, and can also use those aliases in ORDER BY. You can also trivially refactor the above examples to use subqueries or CTEs to accomplish the goal.
- jodrellblank 1y ago
  You apparently can in SQLIte though: https://sqlfiddle.com/sqlite/online-compiler https://sqlfiddle.com/sqlite/online-compiler and query: SELECT LOWER(Name) as LName FROM Product where LName like '%frame%';
- marzell 1y ago
  You absolutely can in many engines, for instance in Snowflake... with the small exception that in all supporting engines you actually need to use HAVING instead of WHERE in your second example (because it compares an aggregation, otherwise WHERE is fine). You can also use "correlated column aliases" (I can't recall the proper name) i.e. SELECT id AS foo, foo || '_1' as foo_n, right(foo_n, 1) as foo_ordinal FROM MyTable WHERE foo = 1; Again, if this isn't all part of SQL standards, the reality is that a lot of engines have semi-standard (sometimes very proprietary too) ways of handling these now common patterns. For real-world use cases, the standards are unfortunately becoming increasingly irrelevant. I think it would be better in the long term to use standards, but if they can't keep up with actual usage then they will just get ignored.

sgarland 1y ago

To clarify, I mean you can’t do this: SELECT id AS foo FROM MyTable WHERE foo = 1; Similarly, you can’t do this: SELECT id FROM MyTable WHERE id = MAX(id); Because in both cases, when the WHERE predicate is being executed, the engine doesn’t yet know what you’re asking it to find - for the former, SELECT hasn’t yet been called, so the alias isn’t applied; for the latter, the aggregation happens after filtering, so it can’t know what that maximum value is. You can of course alias columns for SELECT however you’d like, and can also use those aliases in ORDER BY. You can also trivially refactor the above examples to use subqueries or CTEs to accomplish the goal.

jodrellblank 1y ago
You apparently can in SQLIte though: https://sqlfiddle.com/sqlite/online-compiler https://sqlfiddle.com/sqlite/online-compiler and query: SELECT LOWER(Name) as LName FROM Product where LName like '%frame%';
marzell 1y ago
You absolutely can in many engines, for instance in Snowflake... with the small exception that in all supporting engines you actually need to use HAVING instead of WHERE in your second example (because it compares an aggregation, otherwise WHERE is fine). You can also use "correlated column aliases" (I can't recall the proper name) i.e. SELECT id AS foo, foo || '_1' as foo_n, right(foo_n, 1) as foo_ordinal FROM MyTable WHERE foo = 1; Again, if this isn't all part of SQL standards, the reality is that a lot of engines have semi-standard (sometimes very proprietary too) ways of handling these now common patterns. For real-world use cases, the standards are unfortunately becoming increasingly irrelevant. I think it would be better in the long term to use standards, but if they can't keep up with actual usage then they will just get ignored.