ESQL simple comparison operators

The simple comparison operators >, <, >=, <=, =, and <>.

This topic describes ESQL's simple comparison operators. For information about ESQL's complex comparison operators, see ESQL complex comparison operators.

ESQL provides a full set of comparison operators (predicates). Each compares two scalar values and returns a Boolean. If either operand is null the result is null. Otherwise the result is true if the condition is satisfied and false if it is not.

Comparison operators can be applied to all scalar data types. However, if the two operands are of different types, special rules apply. These are described in Implicit casts.

Some comparison operators also support the comparison of rows and lists, as follows:

Operator>: The first operand is greater than the second.
Operator <: The first operand is less than the second.
Operator>=: The first operand is greater than or equal to the second.
Operator <=: The first operand is less than or equal to the second.
Operator =: The first operand is equal to that of the second.
This operator can also compare rows and lists. See ROW and LIST comparisons for a description of list and row comparison.
Operator <>: The first operand is not equal to the second.
This operator can also compare rows and lists. See ROW and LIST comparisons for a description of list and row comparison.

The meanings of equal, less, and greater in this context are as follows:

For the numeric types (INTEGER, FLOAT, DECIMAL) the numeric values are compared. Thus 4.2 is greater than 2.4 and -2.4 is greater than -4.2.
For the date/time types (DATE, TIME, TIMESTAMP, GMTTIME, GMTTIMESTAMP but not INTERVAL) a later point in time is regarded as being greater than an earlier point in time. Thus the date 2004-03-31 is greater than the date 1947-10-24.
For the INTERVAL type, a larger interval of time is regarded as being greater than a smaller interval of time.

For the string types (CHARACTER, BLOB, BIT) the comparison is lexicographic. Starting from the left, the individual elements (each character, byte or bit) are compared. If no difference is found, the strings are equal. If a difference is found, the values are greater if the first different element in the first operand is greater than the corresponding element in the second and less if they are less. In the special case where two strings are of unequal length but equal as far as they go, the longer string is regarded as being greater than the shorter. Thus:

'ABD' is greater than 'ABC' 
'ABC' is greater than 'AB'

Trailing blanks are regarded as insignificant in character comparisons. Thus if you want to ensure that two strings are truly equal you need to compare both the strings themselves and their lengths. For example:

'ABC  ' is equal to 'ABC'

Note that comparing strings with a length of one is equivalent to comparing individual characters, bytes, or bits. Because ESQL has no single character, byte, or bit data types, it is standard practice to use strings of length one to compare single characters, bytes, or bits.