Behavior of Select expressions containing select
In SQL, 'single quotes' are used for string values, while "double quotes" are used for table/column names.

SQLite allows all types of quotes for compatibility with other databases, but it uses the correct interpretation first, if possible.

So in the subquery (SELECT "Name"), it will try to use a column named Name, and there is one. In other words, the first query is interpreted as:

SELECT NAMES.Name, (SELECT NAMES.Name) FROM NAMES;


(The subquery just refers to the value in the current row of the outer query; this is a rather pointless instance of a correlated subquery.)



SQLite is designed as an embedded database that is accessed from a 'real'
 programming language, so it has no built-in mechanism for dynamic SQL. You have to do the column name lookup separately:

cursor = db.execute("select fname from fields where fid = ?", [2])
fname = cursor.fetchone()[0]
cursor = db.execute("SELECT name, {} FROM NAMES".format(fname))
for name, othercolumn in cursor:
    ...