Comparison rules

The way in which a comparison operation is performed depends on the operands. Lexicographical comparison rules apply to the comparison of rows with more than one column; in the case of comparisons of single-column rows and values, the comparison rules are based on the data types. These rules are collected in the following paragraphs.

Lexicographical comparison

The result of the comparison is derived from the comparison of the values in corresponding columns of the two rows. The values in columns situated further to the right are only significant if the values in all the previous columns are the same for both operands (sorting in the lexicon also occurs according to these comparison rules).

In formal terms this means:
For a comparison of two rows with the comparison operator OP that is either “<” or “>”, with column values L1,L2,...,Ln in the left-hand operand and with column values R1,R2,...,Rn in the right-hand operand, the result is the truth value true or false or unknown respectively, if there is an i index between 1 and n , so that all the comparisons

L1    =   R1
L2    =   R2
 .    .    .
 .    .    . 
 .    .    .
L(i-1) = R(i-1)

return the truth value true, and the comparison

Li OP Ri

returns the truth value true, or false, or unknown, respectively.

The individual comparisons are carried out as described below, depending on the data type.

Please note the following:

• The value in one of the columns may well be NULL without the result of the whole comparison being unknown.

  For example the comparison (1,CAST(NULL AS INT)) < (2,0) the truth value true as a result. The second column is ignored in the comparison because the values of the first columns are already different.

• Not all columns need to be relevant for the comparison result. You should not, therefore, rely on all of the columns in both rows always being evaluated.

• The comparison (L1, L2, ..., Ln) = (R1, R2, ..., Rn) is equivalent to the comparison L1 = R1 AND L2 = R2 ... AND Ln = Rn.

  In the case of the comparison operators “<”, “<=”, “>=”, and “>”, however, there is no straightforward correspondence.

Comparing two values

If one or both of the operands are the NULL value, all comparison operators return the truth value unknown (see also section "NULL value").

Alphanumeric values

Two alphanumeric values are compared from left to right character by character. If the two values have different lengths, the shorter string is padded on the right with blanks (X'40') so that both values have the same length.

Two strings are identical if each has the same character at the same position.

If two strings are not identical, the EBCDIC code of the first two differing characters determines which string is greater or smaller.

National values

Two national values are compared from left to right code unit by code unit. If the two values have different lengths, the shorter string is padded on the right with blanks (NX'0020') so that both values have the same length.

Two strings are identical if each has the same code unit at the same position.

If two strings are not identical, the binary value of the first two differing UTF-16 code units determines which string is greater or smaller.

Numeric values

Values of numeric data types are compared in accordance with their arithmetic value. Two numeric values are the same if they are both 0, or if they have the same sign and the same amount.

Time values

Dates, times and time stamps can be compared. The data type of both operands must be the same.

• One date is greater than another if it is a later date.

• One time is greater than another if it is a later point in time.

• One time stamp is greater than another if either the date is later or, if the date is the same, the time is later.

Examples

1. 1 <= 1 is always true.

2. Comparing alphanumeric values:
   Select the customers from the CUSTOMERS table that come from Munich, and include the customer information:

   SELECT company, cust_info, city FROM customers WHERE city = 'Munich'
      company           cust_info          city
      Siemens AG        Electrical          Munich
      Login GmbH        PC networks         Munich
      Plenzer Trading   Fruit market        Munich
   

3. Comparing with a subquery that returns an atomic value:
   Select the items that need the greatest number of part 501 from the PURPOSE table:

   SELECT item_num FROM purpose 
      WHERE part = 501 AND number = (SELECT MAX(number) 
      FROM purpose WHERE part = 501)
   

   The subquery returns one row exactly, as the maximum is determined for a single group.

   item_num

   200

   You can also write the example with the comparison of two rows each with two columns:

   SELECT item_num FROM purpose 
      WHERE (part, number) = (SELECT 501, MAX(number) 
      FROM purpose WHERE part = 501)
   

4. In this example a cursor table is defined with ORDER BY.
   The WHERE clause selects those rows that come after the rows with cust_num 012 and target DATE'<date>'' in the order stipulated by ORDER BY:

   DECLARE cur_order CURSOR FOR
                       SELECT order_num, cust_num, atext, target FROM orders
                       WHERE (cust_num, target) > (012, DATE'<date>')
      ORDER BY cust_num, target
   

   You will only receive orders which are to be finished after the specified date from a customer with customer number 012, and all orders from customers with a greater customer number.

   The lexicographical comparison rules differ from the comparison rules for ORDER BY only in the case of NULL values.

5. Lexicographical comparison of rows

   DECLARE rest_purpose CURSOR FOR
      SELECT item_num, part, SUM(number) 
      FROM purpose
      WHERE (item_num, part) > (:last_item_num, :last_part)
      GROUP BY item_num, part 
      HAVING SUM(number) > 0
      ORDER BY item_num, part
   

   This cursor reads how many exemplars of each part are contained in the various items. Items are read in ascending order by their item number; items with identical item numbers are read in ascending order by part number.

   The WHERE clause allows for reading the cursor table piecemeal (FETCH). For example, if you have read up to item 120 and up to part 230 and if you have opened the cursor again with the user variables :last_item_num = 120 and :last_part = 230, the cursor table will only contain entries for item 120 and parts with numbers > 230 and entries for items with numbers > 120 (and any parts).