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 |
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:
|
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.
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.