Comparison Conditions (Boolean Expressions)

Comparison conditions are often called Boolean expressions because they return a TRUE or FALSE result.

Six kinds of Boolean operators can specify a comparison condition:

  • Relational operators
  • [NOT] BETWEEN ...; AND operators
  • [NOT] IN operators
  • IS [NOT] NULL operators
  • Trigger-type operators
  • [NOT] LIKE or MATCHES operators
Their syntax is summarized in this diagram and explained in the sections that follow. 
(1)
Comparison Conditions

1   %Expression1  %Relational Operator2  %Expression1
1   %Expression1?  NOT BETWEEN  %Expression1 AND  %Expression1
1 3  %IN Condition4
1 
2.1 column
2.1 expression
1 IS
1?  NOT
1 NULL
1 5 %Trigger-Type Operator6
1 
2.1 string
2.1 column
1?  NOT
2.1 LIKE
2.1 3MATCHES
2.1  string? ESCAPE 'char'
2.1 column
Notes:
Element Description Restrictions Syntax
char An ASCII character to be the escape character in the quoted string. Single ( ' ) and double ( " ) quotation marks are not valid as char. See ESCAPE with LIKE and ESCAPE with MATCHES Quoted String
column Name of a column (or a field of a ROW-type column) whose data value is compared to NULL, to string, or to another column Can be qualified by the identifier, synonym, or alias of a table or view See Column Name
expression An SQL expression that returns a single value Must return a single value Expression
string A string delimited by single ( ' ) or double ( " ) quotation marks Both delimiters must be identical See Quoted String
The following sections describe the different types of comparison conditions:
For a discussion of comparison conditions in the context of the SELECT statement, see Using a Condition in the WHERE Clause.
Warning: A literal DATE or DATETIME value in a comparison condition should specify 4 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on the result. When you specify a 2-digit year, DBCENTURY can affect how the database server interprets the comparison condition, which might not work as you intended. For more information about DBCENTURY, see the HCL OneDB Guide to SQL: Reference.