Specifies a selection condition, join condition, update condition, or delete condition to filter rows of data.
WHERE condition | filterexpression
condition | A Boolean expression representing a selection, update, delete, or join condition, depending on the context. |
filterexpression | A Boolean expression based on the columns from a stream. |
The WHERE clause acts as a selection condition when used with a FROM clause.
The Boolean expression in this clause creates a selection that filters rows arriving in the query's data sources before passing them on to the SELECT clause. WHERE clause filtering is performed before the GROUP BY clause and before aggregation (if any), so it cannot include aggregate functions or the filtering of results based on the results of aggregates. You can use the HAVING clause for post-aggregate filtering.
The selection condition can include literals, column references from the query's data sources listed in the FROM clause, operators, scalar functions, parameters, and parentheses.
In a query, column references within the selection condition must refer to columns in one of the query's data sources.
When used in conjunction with the comma-separated syntax form of the FROM clause, the WHERE clause creates one or more join condition for the comma-separated join. The use of a WHERE clause is optional in a comma-separated join. In the absence of a join condition, all rows from all data sources are selected. When a WHERE clause is present, its syntax resembles the ON clause with ANSI join syntax.
The join condition can be any valid Boolean expression that specifies the condition for the join. All column references in this form of the WHERE clause must refer to data sources specified with the FROM clause.
Filter expressions are supported only in input streams.
When using columns in a filter expression, use the nodeName.columnName notation. nodeName is the name of the input stream.
CREATE INPUT WINDOW QTrades SCHEMA ( Id integer, TradeTime date, Venue string, Symbol string, Price float, Shares integer ) PRIMARY KEY (Id); CREATE OUTPUT WINDOW QTradesComputeSelected PRIMARY KEY DEDUCED AS SELECT trd.* FROM QTrades trd WHERE trd.Symbol IN ('DELL','CSCO','SAP') ;
CREATE INPUT WINDOW QTrades SCHEMA ( Id integer, TradeTime date, Venue string, Symbol string, Price float, Shares integer ) PRIMARY KEY (Id); CREATE OUTPUT WINDOW RecentQTrades PRIMARY KEY DEDUCED AS SELECT q.Symbol, nth(0, q.Price) Price, nth(0, q.Shares) Shares FROM QTrades q GROUP BY q.Symbol GROUP ORDER BY q.ROWID DESC ; CREATE INPUT WINDOW Positions SCHEMA (BookId STRING, Symbol STRING, SharesHeld INTEGER) PRIMARY KEY (BookId, Symbol) ; CREATE OUTPUT WINDOW PositionValue PRIMARY KEY (BookId, Symbol) AS SELECT pos.BookId, pos.Symbol, pos.SharesHeld, pos.SharesHeld * q.Price Value FROM Positions pos, RecentQTrades q WHERE pos.Symbol = q.Symbol ;
CREATE INPUT STREAM LSETradesFiltered SCHEMA ( Id integer, TradeTime date, Venue string, Symbol string, Price float, Shares integer ) WHERE LSETradesFiltered.Symbol IN ('SAP', 'CSCO', 'DELL') ;