Some database management systems, including SQLite, allow us to use the VALUES keyword as a stand alone SQL statement. So we can use VALUES to return a constant table, usually with less code than would be required if we were to use the SELECT statement.

The following examples demonstrate how.

Example

Here’s a quick example:

VALUES 
  (1, 'Fred'), 
  (2, 'Wilma'), 
  (3, 'Barney');

Result:

column1  column2
-------  -------
1        Fred   
2        Wilma  
3        Barney 

It’s easy to see how the values provided are translated into columns and rows. We provide the values as a comma separated list, and each item represents data in a different column. So if the list contains two items, then there are two columns. If the list contains ten items, then there are ten columns.

The columns are automatically named as column1, column2, and so on.

The SELECT Equivalent

While it’s true that we can get the same results with a SELECT statement, the VALUES statement provides us with a more concise way of doing it.

Here’s how we’d achieve the same result with a SELECT statement:

SELECT 1 AS column1, 'Fred' AS column2
UNION ALL 
SELECT 2, 'Wilma'
UNION ALL
SELECT 3, 'Barney';

Result:

column1  column2
-------  -------
1        Fred   
2        Wilma  
3        Barney 

We get the same result, but it requires more code.

Change the Column Names

As mentioned, the columns are automatically named as column1, column2, and so on.

However, we can use the following techinique to rename the columns to something more meaningful:

SELECT
  column1 AS id,
  column2 AS name
FROM (
  VALUES 
    (1, 'Fred'), 
    (2, 'Wilma'), 
    (3, 'Barney')
);

Result:

id  name  
--  ------
1   Fred  
2   Wilma 
3   Barney

In this case I provided aliases for the columns. This resulted in the aliases being returned instead of the default names provided by SQLite.

Sort the Results

SQLite doesn’t allow us to use an ORDER BY clause directly against the VALUES statement itself. However we can get around this by placing the VALUES statement into a subquery, then sorting that subquery:

SELECT * FROM (
  VALUES 
    (1, 'Fred'), 
    (2, 'Wilma'), 
    (3, 'Barney')
) AS t1
ORDER BY column2;

Result:

column1  column2
-------  -------
3        Barney 
1        Fred   
2        Wilma  

If we renamed the columns using aliases, we have the choice of specifying the alias or the original (default) column name.

Limit the Results

We can use a similar technique to limit the results to a specified number of rows. In this case, we need to use the LIMIT clause:

SELECT * FROM (
  VALUES 
    (1, 'Fred'), 
    (2, 'Wilma'), 
    (3, 'Barney')
) AS t1
LIMIT 2;

Result:

column1  column2
-------  -------
1        Fred   
2        Wilma  

In this case I limited the results to just two rows.