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 }

Description:

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:

*,/,DIV and MOD for multiplication, division, division without a remainder and modulus
Unary - , binary + and - for addition and substraction
Relational operators <,>,=,>=,<=,<>,.=,.=.,~ and predicates (BETWEEN, LIKE etc.)
NOT operator
AND operator
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
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	UNKNOWN	UNKNOWN	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	UNKNOWN	FALSE	UNKNOWN	TRUE
UNKNOWN	TRUE	UNKNOWN	TRUE	UNKNOWN
UNKNOWN	FALSE	FALSE	UNKNOWN	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

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

The OVERLAPS predicate for interval overlapping is not implemented.
Predicates for full-text search and for obtaining privileges are an extension of 602SQL.

List of topics:

Expressions

SQL Language in 602SQL

Queries in SQL