SQLite Forum

Column names and aliases in expressions
If you reference a "name" in a context in which that use is *ambiguous*, you will get a message telling you that the "name" is ambiguous.

 > As an example, if I have I have a subquery that refers to a column that exists in several tables that are in the parent query including itself, which instance will be used?

None.  The computer will inform you that the column reference is *ambiguous*, which is an error condition that will prevent the query from being able to be prepared/compiled.

 > How do I refer to the specific one I want?

You use a fully qualified name that is not ambiguous.  A referent to a table column has the format `[[<schema>.]<table>.]<colname>` or `[<table-alias>.]<colname>`.  You must specifically identify the column completely and unambiguously.  Once you have done so, the query may be prepared/compiled without raising an *ambiguity* error.

Computers do not guess (unless they have been programmed to do so).  SQLite3 is not an IBM PL/1 Level H compiler.  It does not try to "guess what you meant", but will rather "do what you say".  If you say something ambiguous then you will be informed of your error.  If you say something which is syntactically valid (but semantically a bluster-duck) then you will get a bluster-ducking.

Mutatis mutandis for any other "name" which may be ambiguous when specified without sufficient qualifiers.