602SQL Documentation Index  

Condition (SQL)

condition ::= boolean_factor { OR boolean_factor }
boolean_factor ::= boolean_expression { AND boolean_expression }
boolean_expression ::= [ NOT ] predicate
predicate ::= expression relational_operator expression    
    | IS_predicate 
    | generalized_comparison    
    | BETWEEN_predicate    
    | IN_predicate
    | EXISTS_predicate
    | UNIQUE_predicate
    | LIKE_predicate 
    | IS_NULL_predicate
    | Fulltext predicate
    | privilege_predicates
    | ( condition )
relational_operator ::= > | < | <= | >= | = | <>
IS_predicate ::= expression IS [NOT] { TRUE | FALSE | UNKNOWN }


A condition is a general expression that can mean TRUE, FALSE or UNKNOWN.

A condition is used for evaluating the CASE statement, WHERE and HAVING clauses in a query specification, CHECK clause in an integrity constraint or JOIN specification, UPDATE and DELETE statements, trigger definition, or when evaluating the IF, CASE, WHILE and REPEAT statements.

The operator priority in conditions is defined as follows:

  1. *,/,DIV and MOD for multiplication, division, division without a remainder and modulus
  2. Unary - , binary + and - for addition and substraction
  3. Relational operators <,>,=,>=,<=,<>,.=,.=.,~ and predicates (BETWEEN, LIKE etc.)
  4. NOT operator
  5. AND operator
  6. OR operator

The UNKNOWN value is equivalent to the NULL value.

The OR operator is a boolean disjunction. The result is TRUE if at least one operand is equal TRUE, FALSE if both operands are FALSE, or otherwise it is UNKNOWN.

The AND operator is a boolean conjunction. The result is TRUE if both operands are equal TRUE, FALSE if at least one operand is FALSE, or otherwise it is UNKNOWN.

The NOT operator does a boolean negation. Negated TRUE equals FALSE, negated FALSE equals TRUE, and negated UNKNOWN equals UNKNOWN.

The table of truth logic:

 a  b  a AND b  a OR b  NOT a

When two expression values are compared using relational operators, it is assumed that both expressions are either of the number type, character string type, binary string type, or DATE/TIME/TIMESTAMP type.

The IS predicate assumes that the contained expression is of the boolean type and compares it with a boolean constant. 602SQL allows you to use relational operators in this function.

Further description of other predicate types can be found on these pages:

602SQL variances

List of topics: