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 8

Start of change  User-defined TypeEnd of change
Binary Integer Y Y Y Y Y 1 — — — — — — — 4
Decimal Number 5 Y Y Y Y Y 1 — — — — — — — 4
Floating Point Y Y Y Y Y 1 — — — — — — — 4
Decimal Floating-Point Y Y Y Y Y 1 — — — — — — — 4
Character String Y Y Y Y Y 1 2 3 3 3 — — — 4
Graphic String 1 1 1 1 1 Y — 1 3 1 3 1 3 — — — 4
Binary String — — — — 2 — Y — — — — — — 4
Date — — — — 3 1 3 — Y — — — — — 4
Time — — — — 3 1 3 — — Y — — — — 4
Timestamp — — — — 3 1 3 — — — Y — — — 4
DataLink — — — — — — — — — — 6 — — 4
Row ID — — — — — — — — — — — 7 — 4
XML 8 — — — — — — — — — — — — Y 4
Start of changeUser-defined TypeEnd of change 4 4 4 4 4 4 4 4 4 4 4 4 4 4
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. 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.

    Start of changeA 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.End of change

  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.
  8. 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.

Parent topic: Language elements