The SELECT statement can be written as a subquery within a complex event processing statement. A subquery can be written in a SELECT clause, a WHERE clause, and in an event stream and pattern filter expression. Describe the subquery enclosed between parentheses, "(" and ")".
A data window or another view must be specified in the event stream definition in the subquery. Only SELECT, FROM, and WHERE clauses can be described in subqueries. The GROUP BY clause, HAVING clause, JOIN, and OUTPUT clause cannot be specified.
The table below shows the keywords that can be used when describing a subquery in a WHERE clause condition.
Keyword | Syntax | Explanation |
|---|---|---|
EXISTS, | [not] exists ( subquery ) | If the subquery returns at least one row, the EXISTS condition is TRUE. If the subquery returns no rows, the NOT EXISTS condition is TRUE. |
IN, NOT IN | expression [not] in ( subquery ) | If at least one value returned by the subquery matches the expression value, the IN condition is TRUE. If no values returned by the subquery match the expression value, the NOT IN condition is TRUE. |
ANY, SOME | expression operator any ( subquery ) expression operator some ( subquery ) | The operator evaluates the expression value and the subquery result, and is TRUE if even one is TRUE. The subquery only need return one property. The operator is either =, !=, <>, <, <=, >, or >=. ANY and SOME have the same meaning. |
ALL | expression operator all ( subquery ) | The operator evaluates the expression value and the subquery result, and is TRUE if ALL are TRUE. The subquery only needs to return one property. The operator is either =, !=, <>, <, <=, >, or >=. |
