1. SQL Reference Guide
  2. SQL Query Management
  3. Comparison Expressions
  4. Value Comparison Operators

Value Comparison Operators

Syntax

comparison_expression ::= concatenate_expression 
   [(value_comparison_operator | any_comparison_operator) add_expression]

value_comparison_operator ::= "=" | "!=" | ">" | ">=" | "<" | "<="

Semantics

Value comparison operators are primarily used to compare 2 values, one produced by the left operand and another from the right operand (this is in contrast to the sequence comparisons, defined in the following section which compare two sequences of values). If any operand returns more than one item, an error is raised. If both operands return the empty sequence, the operands are considered equal (so true will be returned if the operator is =, <=, or >=). If only one of the operands returns empty, the result of the comparison is false unless the operator is !=.

For the remainder of this section, we assume that each operand returns exactly one item. If an operand returns NULL, the result of the comparison expression is also NULL. Otherwise, the result is a boolean value that is computed as follows.

Among atomic items, if the types of the items are not comparable, false is returned. The following rules defined what atomic types are comparable and how the comparison is done in each case.
  • A numeric item is comparable with any other numeric item. If an integer or long value is compared to a float or double value, the integer/long will first be cast to float/double. If one of the operands is a number value, the other operand will first be cast to number (if not a number already).
  • A string item is comparable to another string item (using the java String.compareTo() method). A string item is also comparable to an enum item. In this case, before the comparison, the string is cast to an enum item in the type of the other enum item. Such a cast is possible only if the enum type contains a token whose string value is equal to the source string. If the cast is successful, the two enum items are then compared as explained below; otherwise, the two items are incomparable and false is returned.
  • Two enum items are comparable only if they belong to the same type. If so, the comparison is done on the ordinal numbers of the two enums (not their string values). As mentioned above, an enum item is also comparable to a string item, by casting the string to an enum item.
  • Binary and fixed binary items are comparable with each other for equality only. The 2 values are equal if their byte sequences have the same length and are equal byte-per-byte.
  • A boolean item is comparable with another boolean item, using the java Boolean.compareTo() method.
  • A timestamp item is comparable to another timestamp item, even if their precisions are different.
  • JNULL (json null) is comparable with JNULL. If the comparison operator is !=, JNULL is also comparable with every other kind of item, and the result of such a comparison is always true, except when the other item is also JNULL.
The semantics of comparisons among complex items are defined in a recursive fashion. Specifically:
  • A record is comparable with another record for equality only and only if they contain comparable values. More specifically, to be equal, the 2 records must have equal sizes (number of fields) and for each field in the first record, there must exist a field in the other record such that the two fields are at the same position within their containing records, have equal field names, and equal values.
  • A map is comparable with another map for equality only and only if they contain comparable values. Remember that json documents are modelled as maps, so 2 json documents can be compared for equality. More specifically, to be equal, the 2 maps must have equal sizes (number of fields) and for each field in the first map, there must exist a field in the other map such that the two fields have equal names and equal values.
  • An array is comparable to another array if the elements of the 2 arrays are comparable pair-wise. Comparison between 2 arrays is done lexicographically, that is, the arrays are compared like strings, with the array elements playing the role of the "characters" to compare.

As with atomic items, if two complex items are not comparable according to the above rules, false is returned. Furthermore, comparisons between atomic and complex items return false always.

The reason for returning false for incomparable items, instead of raising an error, is to handle truly schemaless applications, where different table rows may contain very different data or differently shaped data. As a result, even the writer of the query may not know what kind of items an operand may return and an operand may indeed return different kinds of items from different rows. Nevertheless, when the query writer compares "something" with, say, an integer, they expect that the "something" will be an integer and they would like to see results from the table rows that fulfill that expectation, instead of the whole query being rejected because some rows do not fulfill the expectation.

Example 6-42 Value Comparison Operator

We have already seen examples of comparisons among atomic items. Here is an example involving a comparison between two arrays.

Select the id and lastName for users who are connected with users 3, 20, and 10 only and in exactly this order. In this example, an array constructor (see Array and Map Constructors) is used to create an array with the values 3, 20, and 10, in this order. 

SELECT id, lastName FROM users
WHERE connections = [3, 20, 10];