SQLite Forum

Column names and aliases in expressions
I'm sure this is documented better somewhere, but bare column names are resolved in a manner similar to other scoped programming languages.  If for example, you refer to a bare column name in a subquery, it will first try to resolve it using a table in that subquery and if none of those tables have such a column, it will next look at the nearest "enclosing" query... and so on up to the outermost query.

Note that if you add a new column to an existing table, you can, therefore, drastically change the meaning of an existing query.  Hence, I would recommend using bare column names only in very simple queries.  If the queries start to get more complex, it is better to qualify the column names.  Furthermore, since the same table is likely to be referenced more than once in a complex query, it is often necessary to use table aliases when qualifying column names.

As other posters have said, you'll get an error if a bare column name is ambiguous, e.g., if the same column name occurs in more than one table in the same query.  However, the real danger is when it is clear to the SQL Engine which column is being referenced, but not clear to you, the developer!  I've been burned by this a few times.