This subsection provides more information on comparing data.
In a search specified by the GROUP BY clause, lines in which all strings are the same are treated as in the same group.
Comparison of character data follows the rules for comparing String type data in Java.
Character data can only be compared with date, time, or timestamp data if the character string constant conforms to the date, time, or timestamp data format.
Numeric data from a WHERE clause can only be compared with character data if the character data is explicitly cast to the numeric data type and if the character data format conforms to the comparison-target numeric data type.
Table 3-9 Formats of character data that can be compared with numeric data after being cast
Numeric data type | Cast specification | Character data format | |
---|---|---|---|
Rules | Example | ||
Integer type | (TINYINT) (SMALLINT) (INT[EGER]) (BIGINT) |
|
|
Real number and decimal number types | (REAL) (FLOAT) (DOUBLE) (DEC[IMAL]) |
|
|
Note: The addition of the plus or minus sign follows the implementation method for the java.lang.Number class.
For details about the formats with casting specified, see 4.4.19 Value expression.
If the data types to be compared are different, the data type with the smaller range is implicitly cast to the data type with the larger range, and the two are then compared. The hierarchy of range sizes is as follows:
DECIMAL = NUMERIC > FLOAT = DOUBLE > REAL > BIGINT > INTEGER > SMALLINT > TINYINT
Comparison of date, time, and timestamp data follows the rules for comparing the Java java.sql.Date class, java.sql.Time class, and java.sql.Timestamp classes.