3.4.2 Notes on comparing data

This subsection provides more information on comparing data.

Organization of this subsection
(1) Data type general comparison
(2) Comparison of character data
(3) Comparison of numeric data
(4) Comparison of date, time, and timestamp data

(1) Data type general comparison

In a search specified by the GROUP BY clause, lines in which all strings are the same are treated as in the same group.

(2) Comparison of character data

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.

Hint
About casting of character data
Besides character data that appears in comparisons, character data that appears in a value expression can also be cast.
The following table shows the formats of character data that can be compared with numeric data after the character data is cast.

Table 3-9 Formats of character data that can be compared with numeric data after being cast

Numeric data typeCast specificationCharacter data format
RulesExample
Integer type(TINYINT)
(SMALLINT)
(INT[EGER])
(BIGINT)
  • Specifies only the numbers 0 to 9.
  • A period must not be included.
  • The value must fit in the numeric data range.
  • For a positive value, the plus sign (+) cannot be specified.
Examples in which comparison can be made
  • '12'
  • '-1' (The minus sign is coded.)
Examples in which comparison causes an error
  • '12.0' (A period is included.)
  • '1b3' (A non-numeric value is included.)
  • '+1' (The plus sign is coded.)
Real number and decimal number types(REAL)
(FLOAT)
(DOUBLE)
(DEC[IMAL])
  • Specifies only the numbers 0 to 9.
  • A period may be included.
  • The value must fit in the numeric data range.
  • The plus sign (+) or minus sign (-) can be specified for the value.
Examples in which comparison can be made
  • '12'
  • '12.0' (A period is included.)
  • '-1' (The minus sign is coded.)
  • '+1' (The plus sign is coded.)
Example in which comparison causes an error
  • '1b3.0' (A non-numeric value is included.)

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.

(3) Comparison of numeric data

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

(4) Comparison of date, time, and timestamp data

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.