SELECT statement

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide >

SELECT statement

Previous pageReturn to chapter overviewNext page

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, 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> ]

 

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 custName   product quantity

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

   100 Fred Smith Butter         5

   101 Bill Jones Marmite        2

 

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 custName   product quantity

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

   100 Fred Smith Butter         4

   101 Bill Jones Marmite        2

   102 Ivy Black  Rice           6

   103 John Wu    Apples         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 custName   product quantity

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

10:15:00     102 Ivy Black  Rice           6

10:25:00     103 John Wu    Apples         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_06