The SQL SELECT statement is possibly the most commonly used SQL statement. It’s often used to return data from a database, but it can also be used to call functions that return data. The SELECT statement can also be used to return static values, such as string literals.
But the SELECT statement isn’t the only way we can return data in SQL. Another way to return values is with the VALUES statement.
The VALUES Statement
Many SQL developers are familiar with the VALUES clause that can be used in an INSERT statement. This clause enables us to insert tabular data into a table or view.
The VALUES statement works in a similar fashion, except that it’s not necessarily for inserting data. It can simply return the data that we specify.
The VALUES statement enables us to return tabular data without the need for a SELECT statement. Basically, we simply start the statement with VALUES instead of SELECT, followed by the data we need to return.
That said, the syntax is a bit different to a SELECT statement, and it can also vary between DBMSs.
Example
Here’s a basic example to demonstrate how the VALUES statement works:
VALUES (1, 2, 3), (4, 5, 6);Result:
column1 | column2 | column3
---------+---------+---------
1 | 2 | 3
4 | 5 | 6
(2 rows)
We can see that the statement returns the values that we provided between the parentheses, each set of parentheses representing a different row in the result.
The above statement works in PostgreSQL, SQLite, and MariaDB.
In MySQL the syntax is slightly different in that we need to prefix each set of parentheses with the ROW keyword:
VALUES ROW(1, 2, 3), ROW(4, 5, 6);Result:
column1 | column2 | column3
---------+---------+---------
1 | 2 | 3
4 | 5 | 6
(2 rows)
See Using VALUES as a Stand Alone Statement in MySQL for more about MySQL’s implementation.
Ordering the Results
We can use SQL clauses such as ORDER BY to manipulate the results as we wish:
VALUES (1, 15, 31), (4, 22, 6), (7, 8, 19), (2, 0, 11)
ORDER BY column1 DESC;Result:
column1 | column2 | column3
---------+---------+---------
7 | 8 | 19
4 | 22 | 6
2 | 0 | 11
1 | 15 | 31
(4 rows)
And here it is ordered by another column:
VALUES (1, 15, 31), (4, 22, 6), (7, 8, 19), (2, 0, 11)
ORDER BY column2 DESC;Result:
column1 | column2 | column3
---------+---------+---------
4 | 22 | 6
1 | 15 | 31
7 | 8 | 19
2 | 0 | 11
(4 rows)
Limiting the Number of Rows Returned
Depending on your DBMS, you may be able to limit the number of rows returned by the VALUES statement. For example, we can use the LIMIT clause in PostgreSQL and MariaDB:
VALUES (1, 15, 31), (4, 22, 6), (7, 8, 19), (2, 0, 11)
LIMIT 2;Result:
column1 | column2 | column3
---------+---------+---------
1 | 15 | 31
4 | 22 | 6
(2 rows)
Another way to do this (depending on your DBMS) is with the FETCH clause:
VALUES (1, 15, 31), (4, 22, 6), (7, 8, 19), (2, 0, 11)
FETCH FIRST 2 ROWS ONLY;Result:
column1 | column2 | column3
---------+---------+---------
1 | 15 | 31
4 | 22 | 6
(2 rows)
Offsetting the Output
Some DBMSs allow us to use the OFFSET clause in order to skip a certain number of rows before outputting the rest:
VALUES
(1, 'Peter', 'Griffin'),
(2, 'Homer', 'Simpson'),
(3, 'Ned', 'Flanders'),
(4, 'Barney', 'Rubble'),
(5, 'George', 'Costanza')
OFFSET 2 ROWS;Result:
column1 | column2 | column3
---------+---------+----------
3 | Ned | Flanders
4 | Barney | Rubble
5 | George | Costanza
(3 rows)
And we can incorporate this with the LIMIT clause to restrict the number of rows returned:
VALUES
(1, 'Peter', 'Griffin'),
(2, 'Homer', 'Simpson'),
(3, 'Ned', 'Flanders'),
(4, 'Barney', 'Rubble'),
(5, 'George', 'Costanza')
LIMIT 2
OFFSET 2;Result:
column1 | column2 | column3
---------+---------+----------
3 | Ned | Flanders
4 | Barney | Rubble
(2 rows)
This can also be done with the FETCH clause:
VALUES
(1, 'Peter', 'Griffin'),
(2, 'Homer', 'Simpson'),
(3, 'Ned', 'Flanders'),
(4, 'Barney', 'Rubble'),
(5, 'George', 'Costanza')
OFFSET 2
FETCH FIRST 2 ROWS ONLY;Result:
column1 | column2 | column3
---------+---------+----------
3 | Ned | Flanders
4 | Barney | Rubble
(2 rows)