SELECT statement

SELECT retrieves rows from tables, streams and views. You can use SELECT as a top-level statement, or as part of a query involving set operations, or as part of another statement, including (for example) when passed as a query into a UDX. For examples, see the topics INSERT, IN, EXISTS, CREATE VIEW, and CREATE PUMP in this guide.

In order to query a stream or streaming VIEW, you need to use the STREAM keyword.

The subclauses of the SELECT statement are described in the topics {SELECT clause](/sql-reference-guide/select-statement/index.html#select-cause), GROUP BY, Streaming GROUP BY, ORDER BY clause, HAVING clause, WINDOW clause and WHERE clause in this guide.

The end of this topic contains a chart which diagrams all options for the Select statement.

Syntax

<select> :=
    SELECT [STREAM] [ DISTINCT | ALL ]
    <select-clause>
    FROM <from-clause>
    [ <where-clause> ]
    [ <group-by-clause> ]
    [ <having-clause> ]
    [ <window-clause> ]
    [ <order-by-clause> ]
    [ <result-offset-clause> ]
    [ <fetch-first-clause> ]

The STREAM keyword and the Principle of Streaming SQL

The SQL query language was designed for querying stored relations, and producing finite relational results. SQLstream’s extension to SQL uses the STREAM keyword to allow queries against streams,or mixtures of streams and stored relations. In order to query a stream, you need to use the STREAM keyword.

The foundation of streaming SQL is the STREAM keyword, which tells the system to compute the time differential of a relation. The time differential of a relation is the change of the relation with respect to time. A streaming query computes the change in a relation with respect to time, or the change in an expression computed from several relations.

Let’s illustrate with an example. The query

SELECT * FROM OrdersTable;

is a conventional relational query, which returns the contents of OrdersTable at the moment that it is run.

We run the query at 10:00 and get the rows

orderId custNameproduct quantity
======= ========== ======= ========
100 Fred Smith Butter 5
101 Bill Jones Marmite2

Extra orders are placed at 10:15 and 10:25, so at 10:30, we can run the query again, and it returns the rows orderId custNameproduct quantity

======= ========== ======= ========
100 Fred Smith Butter 4
101 Bill Jones Marmite2
102 Ivy Black Rice6
103 John WuApples 3

Now let’s suppose that we have some way of monitoring the rows that are inserted into the OrdersTable, such that we can see every time a new row is added to the table. We can now describe this object as a stream that updates at the instant each order is placed. (We’ll call this stream Orders_Stream.) In the real world, this stream might be populated by an log file that is continually written to, a sensor that continually emits data, a stock price that keeps changing, and so on. While streaming queries are in many ways similar to “traditional” queries, streaming queries continue to execute after retrieving their initial results.

To query a stream, we use the STREAM keyword:

SELECT STREAM * FROM OrdersStream;

If we start running that query at 10:00, it will produce rows at 10:15 and 10:25. At 10:30 the query is still running, waiting for future orders:

ROWTIME orderId custNameproduct quantity
======== ======= ========== ======= ========
10:15:00 102 Ivy Black Rice6
10:25:00 103 John WuApples 3

Here, the system is saying “At 10:15:00 I executed the query *SELECT STREAM * FROM OrdersStream;* and found one row in the result that was not present at 10:14:59.999”. It generates the row with a value of 10:15:00 in the ROWTIME column, because that is when the row appeared. This is the core idea of a stream: a relation that keeps updating over time. (The ROWTIME column is crucial to streams, because all streams depend on the time rows entered s-Server or were recorded.)

You can apply this definition to more complicated queries. The query

SELECT STREAM * FROM Orders_Stream WHERE quantity > 5

has a row at 10:15 but no row at 10:25, because the relation

SELECT STREAM * FROM Orders WHERE quantity > 5

goes from empty to one row when order 103 is placed at 10:15, but is not affected when order 104 is placed at 10:25.

We can apply the same logic to queries involving any combination of SQL operators. You can write queries involving JOIN, GROUP BY, subqueries, set operations UNION, INTERSECT, EXCEPT, and even qualifiers such as IN and EXISTS. You can also write queries that combine streams and tables.

Syntax

SELECT clause

As shown in the chart below, the <select-clause> uses the following items after the STREAM or TABLE keyword:

 <select-list> :=
   <select-item> {, <select-item> }...

<select-item> :=
   <select-expression> [ [ AS ] <simple-identifier> ]

<simple-identifier> :=
   <identifier> | <quoted-identifier>

<select-expression> :=
   <identifier>. *  | *  | <expression>

The select list can include the system-generated ROWTIME. As a system-generated column, ROWTIME behaves as a stream column, but is not stored in the stream.

Note: Unadorned ROWTIME (not qualified with a stream name) only be specified in the SELECT list or the ORDER BY clause.

SELECT clause syntax chart

Syntax Chart for the SELECT-Clause: Sources and References

For “window-specification”, see window-stmt specification ; for “join-reference,” see the chart at JOIN clause.)

The body of the SELECT clause, after the STREAM, DISTINCT, ALL keywords if present, is a list of expressions.

Expressions

Each of these expressions may be:

  • a scalar expression
  • a call to an aggregate function, if this is an aggregating query (see GROUP BY
  • a call to an analytic function, if this is not an aggregating query
  • the wildcard expression * expands to all columns of all relations in the FROM clause
  • the wildcard expression alias. * expands to all columns of the relation named alias
  • the ROWTIME operator
  • a CASE expression

Each expression may be assigned an alias, using the AS column_name syntax. This is the name of the column in the result set of this query. If this query is in the FROM clause of an enclosing query, this will be the name that will be used to reference the column. The number of columns specified in the AS clause of a stream_or_table_reference must match the number of columns defined in the original stream or table.

SQLstream has a few simple rules to derive the alias of an expression that does not have an alias. The default alias of a column expression is the name of the column: for example, EMPS.DEPTNO is aliased DEPTNO by default. Other expressions are given an alias like EXPR$0. You should not assume that the system will generate the same alias each time.

In a streaming query, aliasing a column AS ROWTIME has a special meaning: See Setting a row’s timestamp.

Note: All streams have an implicit column called ROWTIME. This column may impact your use of the syntax ‘AS t(c1, c2,…)’ that is now supported by the SQL standard. Previously in a FROM clause you could only write

SELECT... FROM r1 AS t1 JOIN r2 as t2

but t1 and t2 would have the same columns as r1 and t2. The AS syntax enables you to rename r1’s columns by writing the following:

SELECT... FROM r1 AS t1(a, b, c)

(r1 must have precisely 3 columns for this syntax to work).

If r1 is a stream, then ROWTIME is implicitly included, but it doesn’t count as a column. As a result, if a stream has 3 columns without including ROWTIME, you cannot rename ROWTIME by specifying 4 columns. For example, if the stream Bids has three columns, the following code is invalid.

SELECT STREAM * FROM Bids (a, b, c, d)

It is also invalid to rename another column ROWTIME, as in the following example.

SELECT STREAM * FROM Bids (ROWTIME, a, b)

because that would imply renaming another column to ROWTIME.

Note: Since SELECT can refer to a TABLE or a STREAM, a minor confusion can arise from a standard error message that occurs when the object can’t be found: Even when the missing object is a stream, this standard message includes the phrase “… Table ‘’ not found”.

Syntax Chart for Expressions

Syntax Chart for Expressions

See also Expressions and Literals.

CASE Expression

The CASE expression enables you to specify a set of discrete test expressions and a specific return-value (expression) for each such test. Each test expression is specified in a WHEN clause; each return-value expression is specified in the corresponding THEN clause. Multiple such WHEN-THEN pairs can be specified.

CASE Syntax Chart

If you specify a comparison-test-expression before the first WHEN clause, then each expression in a WHEN clause is compared to that comparison-test-expression. The first one to match the comparison-test-expression causes the return-value from its corresponding THEN clause to be returned. If no WHEN clause expression matches the comparison-test-expression, the return-value is null unless an ELSE clause is specified, in which case the return-value in that ELSE clause is returned.

If you do not specify a comparison-test-expression before the first WHEN clause, then each expression in a WHEN clause is evaluated (left to right) and the first one to be true causes the return-value from its corresponding THEN clause to be returned. If no WHEN clause expression is true, the return-value is null unless an ELSE clause is specified, in which case the return-value in that ELSE clause is returned.

VALUES

VALUES uses expressions to calculate one or more row values, and is often used within a larger command. 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.

Syntax

VALUES ( 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 write VALUES (1), (2) to return two rows each with a single anonymous column. - You can write VALUES (1, ‘a’), (2, ‘b’) to return two rows of two columns. - You can name the columns using AS, as in the following example:

SELECT * FROM (VALUES (1, 'a'), (2, 'b')) AS t(x, y)

The most 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);

When you use VALUES in the FROM clause of a SELECT statement, the entire VALUES clause must be enclosed in parentheses, consistent with the fact that it operates as a query, not a table expression. See additional examples in the topic Relations in this guide.

Note: Using INSERT with streams engages some additional considerations as to rowtimes, pumps, and INSERT EXPEDITED. See the topic Insert in this guide.