How can I query a list of values into a column of rows using SQLite?

Question

I would like to take a list of names (e.g. john, mary, paul) and create a SQLite "select" query that will return this list in a column of rows. Based on this, the following was stated:

"According to the SQL92 standard, a VALUES expression should be able to stand on itself. For example, the following should return a one-column table with three rows:"

 VALUES 'john', 'mary', 'paul';

In theory, the result set would look like:

john
mary
paul

But as the page seems to indicate, this is an unsupported feature in SQLite. The only way that I can figure out how to do this is by unioning a series of select statements like so:

SELECT 'john' AS name
UNION ALL SELECT 'mary' AS name
UNION ALL SELECT 'paul' AS name;

Is this the only way to do this in SQLite or is there an easier way?

Answer 1

For all I know, what you have there already is the best compound SELECT statement SQLite has to offer. One tiny thing: you need the column alias once only.

SELECT 'john' AS name
UNION ALL SELECT 'mary' 
UNION ALL SELECT 'paul';

Answer 2

I found more expressive to use:

SELECT 'john' AS name UNION ALL VALUES ('mary'), ('paul')

You need to specify the first select to gain column aliases.

This construct is easier to read and has fewer limitations as stated in the sqlite documentation:

The phrase "VALUES(expr-list)" means the same thing as "SELECT expr-list". The phrase "VALUES(expr-list-1),...,(expr-list-N)" means the same thing as "SELECT expr-list-1 UNION ALL ... UNION ALL SELECT expr-list-N". Both forms are the same, except that the number of SELECT statements in a compound is limited by SQLITE_LIMIT_COMPOUND_SELECT whereas the number of rows in a VALUES clause has no arbitrary limit.

Answer 3

This did not work in SQLite 3.7, but works in SQLite 3.26:

SELECT * FROM (VALUES ('john'), ('mary'), ('paul')) ;

---

Unlike say PostgreSQL, however, SQLite won't let us alias the columns of such a rowset: SQLite itself names the columns as column, column2, and so on. To assign our own aliases, we must alias SQLite's aliases:

SELECT column1 AS name, column2 AS age
FROM (VALUES ('john',33), ('mary',44), ('paul',55)) ;

gives

name        age       
----------  ----------
john        33        
mary        44        
paul        55