For the HAVING clause

search-condition::=comparison-predicate[ANDsearch-condition]

For the WHERE clause

search-condition::={{'('search-condition')'|comparison-predicate}
           |NOT{0'('search-condition')'|comparison-predicate}
           |search-conditionOR{'('search-condition')'|comparison-predicate}
           |search-conditionAND{'('search-condition')'|comparison-predicate}}

Performs the logical operation specified in the search condition and returns only those results for which the condition is true in a relation.

A search condition is specified using the WHERE or HAVING clauses.

comparison-predicate

For details about specifying a comparison predicate, see 4.4.18 Comparison predicate.

Specify a search condition in a WHERE or HAVING clause. The logical operations that can be specified differ depending on the clause. The following table shows the logical operations that can be specified.

Table 4-6 Logical operations that can be specified

No.	Logical operation	WHERE clause	HAVING clause
1	AND	Can be specified.	Can be specified.
2	NOT	Can be specified.	Cannot be specified.
3	OR	Can be specified.	Cannot be specified.

• The following limitations apply when specifying a WHERE clause:
  
  • The logical operator OR can only be specified if all of the comparison predicates for two terms are in the same relation.
  • The logical operator NOT can only be specified if all of the comparison predicates in the right monomial are in the same relation.
  Multiple relations cannot be specified. The following shows examples of a relation in which s1 and s2 both use the schemas (c1 INT, c2 INT, and c3 INT).
  

  Correct specification example: OR using the same relation is specified

  s1.c1 < 1 OR s1.c2 < 1

  Incorrect specification example: OR using multiple relations is specified

  s1.c1 < 1 OR s2.c1 < 1

  Correct specification example: NOT using the same relation is specified

  NOT(s1.c1 < 1) AND NOT(s2.c1 < 1)

  Incorrect specification example: NOT using multiple relations is specified

  NOT((s1.c1 < 1) AND (s2.c1 <1))

  Correct specification example: OR and NOT using the same relation are specified

  (s1.c1 < 1 OR s1.c2 < 1) AND NOT(s2.c1 < 1)

  Incorrect specification example: OR and NOT using multiple relations are specified

  s1.c1 < 1 OR s1.c2 < 1 AND NOT(s2.c1 < 1)...

• Logical operations are evaluated according to the following hierarchy: what is in parentheses, NOT, AND, and OR. For example, the following two operations have the same meaning:
  

  s1.c1 < 1 OR (s1.c2 < 1 AND NOT (s1.c3 < 3))
  s1.c1 < 1 OR (s1.c2 < 1 AND s1.c3 >= 3)

• The parentheses (()) for search conditions may be omitted.
• A range from 1 to 255 comparison predicates and sets of parentheses can be specified in a search condition. Examples of how this is counted follow:
  

   
  NOT c1>1

  Since c1>1 is the only comparison predicate, the count is 1.

   
  NOT(c1>1)

  Since there are a comparison predicate (c1>1) and a set of parentheses, the count is 2.

   
  c1>1 AND c2>1

  Since there are two comparison predicates (c1>1 and c2>1), the count is 2.

   
  (c1>1) AND (c2>1)

  Since there are two comparison predicates (c1>1 and c2>1) and two sets of parentheses, the count is 4.

Outputs data in which the value of column a in relation s1 is greater than 1 but less than 5. The underlined part indicates a search condition.

REGISTER QUERY q1 SELECT * FROM s1[ROWS 100] WHERE s1.a < 5 AND s1.a > 1;