Query

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide >

Query

Previous pageReturn to chapter overviewNext page

Syntax

<query> :=

    <select>

  | <query> <set-operator> [ ALL ] <query>

  | VALUES <row-constructor> { , <row-constructor> }...

  | '(' <query> ')'

 <set-operator> :=

    EXCEPT

  | INTERSECT

  | UNION

 <row-constructor> :=

    [ ROW ] ( <expression> { , <expression> }... )

query01

select

The select box in the chart above represents any SELECT command; that command is described in detail on its own page.

Set operators (EXCEPT, INTERSECT, UNION)

Set operators combine rows produced by queries using set operations:

EXCEPT returns all rows that are in the first set but not in the second

INTERSECT returns all rows that are in both first and second sets

UNION returns all rows that are in either set

In all cases, the two sets must have the same number of columns, and the column types must be assignment-compatible. The column names of the resulting relation are the names of the columns of the first query.

With the ALL keyword, the operators use the semantics of a mathematical multiset, meaning that duplicate rows are not eliminated. For example, if a particular row occurs 5 times in the first set and 2 times in the second set, then UNION ALL will emit the row 3 + 2 = 5 times.

ALL is not currently supported for EXCEPT or INTERSECT.

All operators are left-associative, and INTERSECT has higher precedence than EXCEPT or UNION, which have the same precedence. To override default precedence, you can use parentheses. For example:

SELECT * FROM a

UNION

SELECT * FROM b

INTERSECT

SELECT * FROM c

EXCEPT

SELECT * FROM d

EXCEPT

SELECT * FROM E

 

is equivalent to the fully-parenthesized query

( ( SELECT * FROM a

   UNION

   ( SELECT * FROM b

     INTERSECT

     SELECT * FROM c) )

 EXCEPT

 SELECT * FROM d )

EXCEPT

SELECT * FROM e

Streaming set operators

UNION ALL is the only set operator that can be applied to streams. Both sides of the operator must be streams; it is an error if one side is a stream and the other is a relation.

For example, the following query produces a stream of orders taken over the phone or via the web:

SELECT STREAM *

 FROM PhoneOrders

UNION ALL

SELECT STREAM *

 FROM WebOrders

 

Rowtime generation. The rowtime of a row emitted from streaming UNION ALL is the same as the timestamp of the input row.

Rowtime bounds. SQLstream ensures the property, required of all streams, that the ROWTIME column is ascending by merging the incoming rows on the basis of timestamp. If the first set has rows timestamped 10:00 and 10:30 and the second set has only reached 10:15, SQLstream will pause the first set, and wait for the second set to reach 10:30. It would be advantageous, in this case, if the producer of the second set were to send a rowtime bound. Rowtime bounds from all producers are propagated to the consumer, unless they are eclipsed by a following row or rowtime bound.

For more detail, see the topic Rowtime Bound in this guide.

VALUES operator

The VALUES operator expresses a constant relation in a query. (See also the discussion of VALUES in the topic SELECT in this guide.)

VALUES can be used as a top-level query, as follows:

VALUES 1 + 2 > 3;

EXPR$0

======

FALSE

VALUES

   (42, 'Fred'),

   (34, 'Wilma');

EXPR$0 EXPR$1

====== ======

   42 Fred

   34 Wilma

 

Note that the system has generated arbitrary column names for anonymous expressions. You can assign column names by putting VALUES into a subquery and using an AS clause:

SELECT *

FROM (

   VALUES

       (42, 'Fred'),

       (34, 'Wilma')) AS t (age, name);

AGE NAME

=== =====

42 Fred

34 Wilma