WHERE clause

<< Click to Display Table of Contents >>

Navigation:  Streaming SQL Reference Guide > SELECT statement  >

WHERE clause

Previous pageReturn to chapter overviewNext page

The WHERE clause extracts records that meet a specified condition. The condition can be a numeric or string comparison, or use the BETWEEN, LIKE, OR IN operators: see SQL Operators. Conditions can be combined using logical operators such as AND, OR, and NOT.

The WHERE clause is like the HAVING clause. It applies to groups, that is, results from a WHERE clause are individual original rows, whereas results from a HAVING clause represent groupings or aggregations of original rows.

WHERE and HAVING can both appear in a single SELECT statement. The WHERE selects from the stream or table those individual rows that satisfy its condition (the WHERE-condition). The GROUP BY criteria apply only to the rows selected by the WHERE condition. Such a grouping, for example "GROUP BY CustomerID", can be further qualified by a HAVING-condition, which then selects aggregations of rows satisfying its condition within the specified grouping. For example, "GROUP BY ClientID HAVING SUM(ShipmentValue) > 3600" would select only those clients whose various shipments that fit the WHERE criteria also had values that added up to exceed 3600.

Syntax Chart for "condition" used in the WHERE and HAVING Clauses

To see where this clause fits into the SELECT statement, see the chart in the topic SELECT statement.)

select_06_condition

The condition must be a Boolean predicate expression. The query returns only rows for which the predicate evaluates to TRUE; if the condition evaluates to NULL, the row is not emitted.

The condition in the WHERE clause cannot contain windowed aggregation expressions, because if the where clause condition caused rows to be dropped, it would alter the contents of the window.

WHERE is also discussed in the topics JOIN clause and HAVING clause in this guide.