Reputation: 1306
I want to generate a list of names from an SQLite SELECT
query as rows. This states:
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 is:
john |
mary |
paul |
But this is unsupported in SQLite. I can UNION
a series of SELECT
statements:
SELECT 'john' AS name
UNION ALL SELECT 'mary' AS name
UNION ALL SELECT 'paul' AS name;
Is there an easier way?
Upvotes: 2
Views: 2077
Reputation: 202
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
Upvotes: 2
Reputation: 629
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.
Upvotes: 0
Reputation: 659367
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';
Upvotes: 0