undefined | Better HN

0 pointsmarzell7mo ago0 comments

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

0 comments

sgarland7mo 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.

marzellOP7mo 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.

jodrellblank7mo ago

You apparently can in SQLIte though: https://sqlfiddle.com/sqlite/online-compiler and query:

    SELECT LOWER(Name) as LName FROM Product where LName like '%frame%';

j / k navigate · click thread line to collapse