Create a comparison condition

The WHERE clause of a SELECT statement specifies the rows that you want to see. A comparison condition employs specific keywords and operators to define the search criteria.

For example, you might use one of the keywords BETWEEN, IN, LIKE, or MATCHES to test for equality, or the keywords IS NULL to test for null values. You can combine the keyword NOT with any of these keywords to specify the opposite condition.

The following table lists the relational operators that you can use in a WHERE clause in place of a keyword to test for equality.
Operator
Operation
=
equals
!= or <>
does not equal
>
greater than
>=
greater than or equal to
<
less than
<=
less than or equal to

For CHAR expressions, greater than means after in ASCII collating order, where lowercase letters are after uppercase letters, and both are after numerals. See the ASCII Character Set chart in the HCL OneDB™ Guide to SQL: Syntax. For DATE and DATETIME expressions, greater than means later in time, and for INTERVAL expressions, it means of longer duration.

You cannot use TEXT or BYTE columns to create a comparison condition, except when you use the IS NULL or IS NOT NULL keywords to test for NULL values.

You cannot specify BLOB or CLOB columns to create a comparison condition on , except when you use the IS NULL or IS NOT NULL keywords to test for NULL values.

You can use the preceding keywords and operators in a WHERE clause to create comparison-condition queries that perform the following actions:
  • Include values
  • Exclude values
  • Find a range of values
  • Find a subset of values
  • Identify NULL values
To perform variable text searches using the following criteria, use the preceding keywords and operators in a WHERE clause to create comparison-condition queries:
  • Exact-text comparison
  • Single-character wildcards
  • Restricted single-character wildcards
  • Variable-length wildcards
  • Subscripting

The following section contains examples that illustrate these types of queries.