How do I name columns in a VALUES clause?

Question

For this query in SQLite 3.17.0 :

select T.* from (values (1),(2),(3),(4),(5)) as T;

there is no name for first column of T:

1
2
3
4
5

How do I name/alias the first column of T, or refer to it by index?

Answer 1

with cte(my_column_alias) as 
  (values (1),(2),(3),(4),(5))
select * from cte;

Answer 2

The VALUES form of a query does not have any mechanism for you to specify the column name. (The VALUES clause is intended to be used in CTEs or views where you can specify the column names elsewhere.)

As it happens, the columns returned by VALUES do have names (but they are undocumented):

sqlite> .header on
sqlite> .mode columns
sqlite> values (42);
column1
----------
42

In any case, even if that name does not survive the subquery, an empty column name is no problem at all:

select "" from (values (1),(2),(3),(4),(5));

To apply column name(s), wrap a CTE around it:

WITH T(my_column) AS (
  VALUES (1),(2),(3),(4),(5)
)
SELECT * FROM T;

or use a compound query (the WHERE 0 suppresses the row from the first query):

SELECT NULL AS my_column WHERE 0
UNION ALL
VALUES (1),(2),(3),(4),(5);

Answer 3

Although I recommend @Catherine's provided CTE answer - As it not only seems to perform faster in some instance then the answer I'll provide, but encourages development patterns and approaches that relate to principals like re-usability and single responsibility.

Though this answer (and probably all these answer) depend on PRAGMA settings (which I don't have enough knowledge on at this time to cover), It seems it is possible to both reference to generated column names and alias them.

That is, columns of the VALUES table are generated with column headers: column1, column2 and so on; So, you just alias a column by explicitly referencing the specific, generated column name.

SELECT
   v.[column1] [Id]
  ,v.[column2] [Name]
FROM (VALUES (1, 'Alex'), (2, 'Brad'), (3, 'Mary'), (4, 'Jennifer')) [v]

Answer 4

select 1 a union all 
select T.* from (values (1),(2),(3),(4),(5)) as T;

a
---
1
1
2
3
4
5

little trick and now you has column a

Answer 5

Per 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".

So, you could change to:

SELECT T.a AS my_column_alias FROM (
 SELECT 1 as a
 UNION ALL
 SELECT 2 as a
 UNION ALL
 SELECT 3 as a
 UNION ALL
 SELECT 4 as a
 UNION ALL
 SELECT 5 as a
) as T;

Then "my_column_alias" is your column name/alias.

my_column_alias
---------------
1
2
3
4
5

Answer 6

Here's a collection of the various values-based syntaxes I use. The first two are especially handy for unit tests where you can also use a memory-based database instance.

SELECT FROM VALUES

This simply returns the values as a result set with the named columns.

SELECT
    column1 as Id,
    column2 as Name
FROM (VALUES
    (1, 'Alex'),
    (2, 'Brad'),
    (3, 'Mary'),
    (4, 'Jennifer')
)

COMMON TABLE EXPRESSIONS (CTE)

Similar to the above, there's the CTE approach. This creates a 'name' for the value-selected data so you can use it elsewhere in your query as if it were a table. This allows you to create 'mini' tables which you can then join into the final output. (Here however, I'm just creating a single one called 'myTable'.)

WITH myTable(id, name) AS (
VALUES
    (1, 'Alex'),
    (2, 'Brad'),
    (3, 'Mary'),
    (4, 'Jennifer')
)
SELECT id, name
FROM myTable;

TEMPORARY TABLES (Including for completeness)

This is similar to the CTE above but the named results exist for the lifetime of the connection (or until you manually drop them, if sooner.) That's why unlike the above two which can be run without issue, this will fail if the table already exists, so make sure to 'drop' it first, or clear-then-repopulate it with the new data.

CREATE TEMP TABLE myTable (id Integer, name TEXT);

INSERT INTO myTable
VALUES 
    (1, 'Alex'),
    (2, 'Brad'),
    (3, 'Mary'),
    (4, 'Jennifer');

SELECT * FROM myTable;