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