VALUES uses expressions to calculate one or more row values, and is often used within a larger SQL command such as SELECT and INSERT. When creating more than one row, the VALUES clause must specify the same number of elements for every row. The resulting table-columns data-types are derived from the explicit or inferred types of the expressions appearing in that column. VALUES is allowed syntactically wherever SELECT is permitted. See also the discussion of VALUES as an operator, in the topic Query in this guide.
This topic covers:
VALUES [ CYCLE ] ( expression [,...] ) [,...]
[ ORDER BY sort_expression [ ASC | DESC | USING operator ] [,...] ]
VALUES is a SQL operator, on a par with SELECT and UNION, enabling the following types of actions:
You can select from VALUES When you use VALUES in the FROM clause of a SELECT statement:
SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(x, y);
SELECT EXPR$0 AS X, EXPR$1 AS Y FROM (VALUES (1, 'a'), (2, 'b'));
If you want to check how a SQL function works, you can test it using VALUES:
0: jdbc:sqlstream:sdp://sqlstream-base> values(substring('Hello World',2,5));
'EXPR$0'
'ello '
1 row selected (0.116 seconds)
This can be particularly useful when validating regular expression usage in functions like REGEX_REPLACE:
0: jdbc:sqlstream:sdp://sqlstream-base> values regex_replace('The pen is mightier than the sword', 'i[a-zA-Z]', 'HAHA', 1, 0);
'EXPR$0'
'The pen HAHA mHAHAhtHAHAr than the sword'
1 row selected (0.1 seconds)
VALUES are often used to create reusable views - often for the OPTIONS_QUERY option associated with foreign streams.
CREATE OR REPLACE VIEW SALES.CSV_OPTIONS(SEPARATOR, QUOTE_CHARACTER, SKIP_HEADER) AS VALUES(',', '"', 'true');
!outputformat table
select * from sales.csv_options;
+-----------+-----------------+-------------+
| SEPARATOR | QUOTE_CHARACTER | SKIP_HEADER |
+-----------+-----------------+-------------+
| , | " | true |
+-----------+-----------------+-------------+
1 row selected (0.221 seconds)
The view definition also provides a third way to relabel the columns.
An important use of VALUES is in an INSERT statement, to insert a single row:
INSERT INTO emps (empno, name, deptno, gender)
VALUES (107, 'Jane Costa', 22, 'F');
However, you can also insert multiple rows:
INSERT INTO Trades (ticker, price, amount)
VALUES ('MSFT', 30.5, 1000),
('ORCL', 20.25, 2000);
Note: Using INSERT with streams engages some additional considerations as to rowtimes, pumps, and INSERT EXPEDITED. See the topic INSERT in this guide.
As well as VALUES, SQLstream supports a continuously streaming source using VALUES CYCLE. This is an extension to the SQL standard.
0: jdbc:sqlstream:sdp://sqlstream-base> values cycle(1),(2);
+------------+
| EXPR$0 |
+------------+
| 1 |
| 2 |
| 1 |
| 2 |
| 1 |
| 2 |
| 1 |
| 2 |
| 1 |
| 2 |
...
Rows are emitted as fast as possible, cycling the row values for ever or until the statement is cancelled.
This allows some simple performance testing of individual operations - especially aggregations:
1/1 select stream step(s.rowtime by interval '1' second),count(*)
from (select stream * from (values cycle(1))) s
group by step(s.rowtime by interval '1' second);
'EXPR$0','EXPR$1'
'2021-05-25 17:38:46.0','1834980'
'2021-05-25 17:38:47.0','4456380'
'2021-05-25 17:38:48.0','3669960'
'2021-05-25 17:38:49.0','4849590'
'2021-05-25 17:38:50.0','5111730'
'2021-05-25 17:38:51.0','5439405'
'2021-05-25 17:38:52.0','5308335'
'2021-05-25 17:38:53.0','4063170'
'2021-05-25 17:38:54.0','5177265'
This example on a late-2012 iMac (3.2 GHz Quad-Core Intel Core i5) is aggregating just under 5 million rows per second.
NOTE: VALUES CYCLE only supports literal values, not expressions (unlike VALUES which does support expressions).