Values to be compared must be of compatible data type. If values with incompatible data types are compared, an error occurs.
Character String Comparisons
Both fixed-length and variable-length character strings are compared character by character from left to right. If the strings are of different length, the shorter string is conceptually padded to the right with blanks before the comparison is made (i.e. character differences take precedence over length differences).
This means, for example, that the variable-length column with the value 'HOTEL ' (one trailing blank) is equal to the variable-length column with the value 'HOTEL ' (two trailing blanks).
It is the collating sequence that determines whether one character is less than or greater than another.
The collating sequence for characters is an extended ASCII character set as defined by ISO 8859-1, LATIN1, see Character Set.
Numerical values are always compared according to their algebraic values.
Integer values compared with decimal or floating point values are treated as decimal or floating respectively. When decimal values are compared with decimal, the lower precision value is conceptually padded with leading and trailing zeros as necessary. Decimal values compared with floating point values are treated as floating.
Thus all the following comparisons evaluate to "true":
1 = 1.0
2 < 2.3E0
35.3 = 035.300
35.3 > 3.5E1
Datetime and Interval Comparisons
Two DATETIME values may be compared if they are assignment-compatible, as defined in Datetime Assignment Rules.
DATETIME comparisons are performed in accordance with chronological ordering.
When two TIME or two TIMESTAMP values are compared, the seconds precision of the value with the lowest seconds precision is extended by adding trailing zeros.
Two INTERVAL values may be compared if they are assignment-compatible, as defined in Interval Assignment Rules.
INTERVAL comparisons are performed in accordance with their sign and magnitude.
It is not possible to compare YEAR-MONTH intervals with DAY-TIME intervals.
Comparable INTERVAL types with different interval precisions are conceptually converted to the same interval precision, prior to any comparison, by adding fields as required.
All comparisons involving NULL on either side of the comparison operator evaluate to "unknown". NULL is never equal to, greater than or less than anything else. SQL provides a special NULL predicate to test for the presence or absence of NULL in a column, see The NULL Predicate.
Considerable care is required in writing search conditions involving columns which may contain NULL. It is often very easy to overlook the effect of NULL comparisons, with the result that rows which should be included in the result table are omitted or vice versa. See the Mimer SQL User's Manual for further discussion of this point.
The following truth tables summarize the outcome of conditional expressions where comparisons are negated by NOT or joined by AND or OR.
A question mark represents the truth value "unknown".
This section summarizes standard compliance concerning comparisons.
YES Fully compliant.
Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40