Assignment and comparison
The basic operations of SQL are assignment and comparison.
Assignment operations are performed during the execution of statements such as CALL, INSERT, UPDATE, MERGE, FETCH, SELECT INTO, SET host-variable or SET assignment-statement, and VALUES INTO statements. In addition, when a function is invoked or a stored procedure is called, the arguments of the function or stored procedure are assigned. 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 data types of the operands must be compatible. The compatibility rule also applies to other operations that are described under Rules for result data types.
The following table shows the compatibility of data types for assignments and comparisons.
Operand | Binary integer | Decimal number | Floating point | Decimal floating point | Character string | Graphic string | Binary string | Date | Time | Timestamp without time zone | Timestamp with time zone | Row ID | User-defined type | XML8 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Binary integer | Yes | Yes | Yes | Yes | 1 | 1 | No | No | No | No | No | No | 2 | No |
Decimal number | Yes | Yes | Yes | Yes | 1 | 1 | No | No | No | No | No | No | 2 | No |
Floating point | Yes | Yes | Yes | Yes | 1 | 1 | No | No | No | No | No | No | 2 | No |
Decimal floating point | Yes | Yes | Yes | Yes | 1 | 1 | No | No | No | No | No | No | 2 | No |
Character string | 1 | 1 | 1 | 1 | Yes | Yes 3,4 | No 5 | Yes 7 | Yes 7 | Yes7 | Yes 7 | No | 2 | No |
Graphic string | 6 | 6 | 6 | 6 | Yes 3,4 | Yes | No | 3, 7 | 3, 7 | 3, 7 | 3, 7 | No | 2 | No |
Binary string | No | No | No | No | No 5 | No | Yes | No | No | No | No | No | 2 | No |
Date | No | No | No | No | 7 | 3,7 | No | Yes | No | No | No | No | 2 | No |
Time | No | No | No | No | 7 | 3,7 | No | No | Yes | No | No | No | 2 | No |
Timestamp without time zone | No | No | No | No | 7 | 3,7 | No | No | No | Yes | Yes | No | 2 | No |
Timestamp with time zone | No | No | No | No | 7 | 3,7 | No | No | No | Yes | Yes | No | 2 | No |
Row ID | No | No | No | No | No | No | No | No | No | No | No | Yes | 2 | No |
User-defined type | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | Yes2 | No |
XML8 | No | No | No | No | No | No | No | No | No | No | No | No | No | Yes |
|
Compatibility with a column that has a field procedure is determined by the data type of the column, which applies to the decoded form of its values.
A basic rule for assignment operations is that a null value cannot be assigned to:
- A column that cannot contain null values
- A non-Java™ host variable that does not have an associated indicator variable
For a host variable that does have an associated indicator variable, a null value is assigned by setting the indicator variable to a negative value. See Host variables for a discussion of indicator variables.
- A Java host variable that
is a primitive type
For a Java host variable that is not a primitive type, the value of that variable is set to a Java null value.