0

Consider the following setup:

CREATE TABLE NAMES(Id integer PRIMARY KEY, Name text);

INSERT INTO NAMES VALUES(1,'Tom');
INSERT INTO NAMES VALUES(2,'Lucy');
INSERT INTO NAMES VALUES(3,'Frank');

CREATE TABLE fields(fid integer, fname text);
INSERT into fields values(1, "Id");
insert into fields values(2, "Name");

SELECT name, (SELECT "Name") FROM NAMES;

The last statement will output

Tom|Tom
Lucy|Lucy
Frank|Frank

However, although select fname from fields where fid=2 yields ("Name") (as a single-cell table), replacing the last statement in the above query by

SELECT name, (select fname from fields where fid=2) FROM NAMES;

gives

Tom|Name
Lucy|Name
Frank|Name

Why? Is this intended?

Note: This has been tested using SQLite v3.20

  • 2
    yes, this works as intended. If you want to use a value ("Name") as a column, you need dynamic SQL. – ypercubeᵀᴹ Jan 3 '18 at 22:14
  • @yper-trollᵀᴹ and why does the former expression yield its output, despite it contains a select to obtain the field name? Is it because the result of the selection is independent of the database contents? – Bubaya Jan 4 '18 at 21:40
  • 1
    Not sure what you are asking. SELECT name, (SELECT "Name") FROM NAMES; is equivalent to SELECT name, "Name" FROM NAMES; – ypercubeᵀᴹ Jan 4 '18 at 22:01
2

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:
    ...
  • I do not remember having read this double- vs. single quotes issue. Thank you for pointing me to that; with single quotes, the behaviour indeed is different. – Bubaya Jan 8 '18 at 20:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.