Assignments and comparisons

The basic operations of SQL are assignment and comparison. Assignment operations are performed during the execution of CALL, INSERT, UPDATE, FETCH, SELECT, SET variable, and VALUES INTO statements. Comparison operations are performed during the execution of statements that include predicates and other language elements such as MAX, MIN, DISTINCT, GROUP BY, and ORDER BY.

The basic rule for both operations is that the data type of the operands involved must be compatible. The compatibility rule also applies to UNION, EXCEPT, INTERSECT, concatenation, CASE expressions, and the CONCAT, VALUE, COALESCE, IFNULL, MIN, and MAX scalar functions. The compatibility matrix is as follows:

Table 15. Data Type Compatibility
 
 
 
Operands
 
 
 
  Binary Integer
  Decimal Number 
  Floating Point
  Character String
  Graphic String
  Binary String
  Date
  Time
  Timestamp
  DataLink
  Row ID
  Distinct Type
Binary Integer Yes Yes Yes Yes 1 No No No No No No 4
Decimal Number 5 Yes Yes Yes Yes 1 No No No No No No 4
Floating Point Yes Yes Yes Yes 1 No No No No No No 4
Character String Yes Yes Yes Yes 1 2 3 3 3 No No 4
Graphic String 1 1 1 1 Yes No 1 3 1 3 1 3 No No 4
Binary String No No No 2 No Yes No No No No No 4
Date No No No 3 1 3 No Yes No No No No 4
Time No No No 3 1 3 No No Yes No No No 4
Timestamp No No No 3 1 3 No No No Yes No No 4
DataLink No No No No No No No No No 6 No 4
Row ID No No No No No No No No No No 7 4
Distinct Type 4 4 4 4 4 4 4 4 4 4 4 4
Notes:
  1. Only Unicode graphic strings are compatible.
  2. All character strings, even those with FOR BIT DATA, are not compatible with binary strings except during assignments to or from variables or parameter markers. In this case, FOR BIT DATA character strings and binary strings are considered compatible and any padding is performed based on the data type of the target. For example, when assigning a FOR BIT DATA column value to a fixed-length binary variable, any necessary padding uses a pad byte of X'00'.
  3. The datetime values and strings are not compatible in concatenation or in the CONCAT scalar function.
  4. A value with a distinct type is comparable only to a value that is defined with the same distinct type. In general, the database manager supports assignments between a distinct type value and its source data type. For additional information, see Distinct type assignments.
  5. Decimal refers to both packed and zoned decimal.
  6. A DataLink operand can only be assigned to another DataLink operand and cannot be compared to any data type.
  7. A ROWID operand can only be assigned to another ROWID operand and cannot be compared to any data type.

A basic rule for assignment operations is that a null value cannot be assigned to:

See References to host variables for a discussion of indicator variables.

For any comparison that involves null values, see the description of the comparison operation for information about the specific handling of null values.