Sometimes, we just want to try or demo a specific way to use SQL. Even if the query is simple, it can become a tedious task if it requires test data that is not yet available in any table. In such cases, the values
statement can be handy.
In its full power, values
can not only provide data to the insert
statement, but be used as stand-alone statement very much like select
. Unlike select
, values
doesn’t access any tables but only the data that is explicitly listed in the SQL statement.
The following example demonstrates that COUNT(*)
and COUNT(expression)
mean something different:0
SELECT COUNT(c1)
, COUNT(*)
FROM (VALUES (1)
, (NULL)
) t1(c1)
The example uses the values
clause to produce a table with two rows and one column (of unknown name). One of the values is null
. To be able to refer to this column, and thus be able to apply an aggregate function upon it, the example aliases this derived table as t1 and its column as c1 using the alias columns in from
syntax. Alternatively, you can use a with
clause to name the columns. t1
and its columns can now be used like a regular table of view.
Compatibility
The example conforms to full SQL-92 and later standards with the optional features F641, F591 and F661.1
- Only without keyword
row
- Requires keyword
row
:values row('r1c1','r1c2'), row('r2c1', 'r2c2')
- Needs
from
clause column renaming • Only without keywordrow
- Requires column names in
with
clause • Only without keywordrow
- Accepts missing column aliases
- Only for derived tables (not regular tables or views)