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 1. Data Type Compatibility
 
 
 
Operands
 
 
 
  Binary Integer
  Decimal Number 
  Floating Point 
  Decimal Floating Point
  Character String
  Graphic String
  Binary String
  Date
  Time
  Timestamp
  DataLink
  Row ID
  XML 7
  User-defined Type
Binary Integer Y Y Y Y Y 1 — — — — — — — 3
Decimal Number 4 Y Y Y Y Y 1 — — — — — — — 3
Floating Point Y Y Y Y Y 1 — — — — — — — 3
Decimal Floating-Point Y Y Y Y Y 1 — — — — — — — 3
Character String Y Y Y Y Y 1 2 — — — — — — 3
Graphic String 1 1 1 1 1 Y — 1  1  1  — — — 3
Binary String — — — — 2 — Y — — — — — — 3
Date — — — — — 1  — Y — Y — — — 3
Time — — — — — 1  — — Y — — — — 3
Timestamp — — — — — 1  — Y — Y — — — 3
DataLink — — — — — — — — — — 5 — — 3
Row ID — — — — — — — — — — — 6 — 3
XML 7 — — — — — — — — — — — — Y 3
User-defined Type 3 3 3 3 3 3 3 3 3 3 3 3 3 3
Notes:
  1. Only Unicode graphic strings are compatible. Unicode and FOR BIT DATA are not compatible.
  2. Character strings, except those with FOR BIT DATA, are not compatible with binary strings. 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. 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.

    A value with an array type is comparable only to a value that is defined with the same array type. A value with an array type can be assigned to an array of the same type. For additional information, see Array type assignments.

  4. Decimal refers to both packed and zoned decimal.
  5. A DataLink operand can only be assigned to another DataLink operand and cannot be compared to any data type.
  6. A ROWID operand can only be assigned to another ROWID operand and cannot be compared to any data type.
  7. Character and graphic strings can be assigned to XML columns. However, XML cannot be assigned to a character or graphic string column. For comparisons, XML can only be compared using the IS NULL predicate.

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

  • a column that cannot contain null values
  • a host variable that does not have an associated indicator variable
  • a Java™ host variable that is a primitive type.

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.