MSSQL has the same (very frustrating) limitation. The obvious and ugly solution would be to wrap in a subquery (bonus: still std sql compliant, and performs well):

    SELECT bigname
    FROM (
        SELECT UPPER(name) AS bigname
        FROM people
    ) x
    WHERE bigname = 'JOE'

Add columns as necessary, it should work fine.

But this method can make large queries with lots of aliasing very difficult to read and lead to deep nesting. There is an alternative in MSSQL and (via a quick search) pgsql as well to keep the crazy nesting to a minimum:

    -- MSSQL
    SELECT bigname
    FROM people
    CROSS APPLY (SELECT UPPER(name) as bigname) x
    WHERE bigname = 'JOE'

    -- PGSQL (not 100% sure of syntax)
    SELECT bigname
    FROM people
    LEFT JOIN LATERAL (SELECT UPPER(name) as bigname) x on true
    WHERE bigname = 'JOE'

The problem is this might have a negative impact on performance because it mucks with the query planner. I've seen everything from huge, complex, CROSS APPLYs have literally zero performance penalty to completely benign single-column renames tanking query performance 10x, so beware if you use this.

It's because the WHERE clause is evaluated before the SELECT. The reason that SQLite allows you to do that is because SQLite is much, much simpler than PgSQL, MySQL and SQL Server (all of which don't allow you to do this).

http://tinman.cs.gsu.edu/~raj/sql/node22.html

Yes. I wish SQL select-statements put the select-clause last:

   from people
   where name = 'Joe'
   select upper(name) as bigname

1. It would mirror the other clauses (insert, update, and delete) which all begin with the table name.

2. It would be easier on my head. I skip the select-clause anyway when reading SQL, then come back up to it at the end, when I know the columns' source.

3. It would show that using column aliases in the where-clause would fail (unless the database did some kind of two-pass processing).

You just described LINQ

You would think that an initial pass of SELECT could be made only to note any aliases. It would be a much appreciated nod to usability.

That seems straightforward enough, don't know why I assumed SQLite's implementation of it was a feature as opposed to a consequence of its overall simplified model. I think I assumed/hoped that aliasing worked as a kind of function definition which WHERE could magically understand and evaluate.

As a counter point teradata (a ridiculously expensive closed source but fairly complex database) supports this feature. So it is definitely possible for this feature to be supported.

in SQLSERVER you could build your selects (using unique names or aliases) and then wrap it all in a common table expression like so:

; with cte as ( select a as A, b as B, c as C from dbo.table1 tbl1 join dbo.table2 tbl2 on tbl2.id = tbl1.id) select A, B, C from cte

I’m always saddened when I see this feature lacking in so many databases. Especially as Teradata supports this feature so i get to use it at my day job but not on any open source projects.