Values to be compared must be of compatible data types. 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, that is, character differences take precedence over length differences.
For example, the variable-length column with the value '
town', one trailing blank, is equal to the variable-length column with the value '
town', two trailing blanks.
When comparing a character string to a national character string, the character string is implicitly converted to a national character string, before the comparison is performed.
A collation determines whether a character string is less than, equal to, or greater than another when sorting or comparing data.
SQL only permits compatible character strings to be compared. That is, you can compare character strings only if the source and target strings belong to the same collation or are coerced into having the same collation.
A character string that is defined with a named collation can only be compared to a character string that is either defined with the same named collation or is defined without a collation.
In the case where one of the strings is not associated with a named collation then it will be implicitly coerced to the same collation as the other string.
A collation specified in the column-definition will take precedence over a domain collation.
For more information on character sets, see Character Sets.
For more information on collations, see Mimer SQL User's Manual, Collations.
INTEGER, DECIMAL and FLOAT values are always compared according to their algebraic values.
INTEGER values compared with DECIMAL or FLOAT values are treated as DECIMAL or FLOAT 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 FLOAT values are treated as FLOAT.
Thus all the following comparisons evaluate to TRUE:1 = 1.0 2 < 2.3E0 35.3 = 035.300 35.3 > 3.5E1
A REAL or DOUBLE PRECISION value (A) compared with an INTEGER, DECIMAL or FLOAT value (B) is handled in the following manner:A' = CAST(A AS type-of-B); Q = (A = CAST(A' AS type-of-A));
If Q is TRUE then A' is a close approximation of A in he type of B. Comparisons are made between A' and B in the type B.
If Q is FALSE then A' is NOT a close approximation of A in the type of B. A is considered to be unequal to any value in type B. However A' and B can be compared for magnitude (> or <).
Thus all the following comparisons evaluate to TRUE:CAST(1 AS REAL) = 1 CAST(1.1 AS REAL) <> 1 CAST(1.1 AS REAL) = 1.1 CAST(1.1 AS REAL) <> 1.10000000 CAST(1.1 AS REAL) = 1.10000002
Datetime and Interval Comparisons
DATETIMEvalues may be compared if they are assignment-compatible, as defined in Datetime Assignment Rules.
DATETIMEcomparisons are performed in accordance with chronological ordering.
TIMESTAMPvalues are compared, the seconds precision of the value with the lowest seconds precision is extended by adding trailing zeros.
INTERVALvalues may be compared if they are assignment-compatible, as defined in Interval Assignment Rules.
INTERVALcomparisons are performed in accordance with their sign and magnitude.
It is not possible to compare
INTERVALtypes with different interval precisions are conceptually converted to the same interval precision, prior to any comparison, by adding fields as required.
Binary values are compared bytewise. If the two binary values have different lengths they are not equal.
Boolean values are compared to
FALSE. When comparing truth values
FALSEis less than
When equals true is to be evaluated it is unnecessary to write the
= TRUEpart. I.e.WHERE boolcol = TRUE
is typically written asWHERE boolcol
= FALSEis typically re-written using
NOT. I.e.WHERE boolcol = FALSE
is usually expressed asWHERE NOT boolcolThe
BOOLEAN TESTsyntax is supported for truth value tests, i.e.:boolean-primary
IS NOT TRUEboolean-primary
IS NOT FALSEboolean-primary
IS NOT UNKNOWN
All comparisons involving a null value 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
NULLpredicate to test for the presence or absence of null value in a column, see The NULL Predicate.
Considerable care is required in writing search conditions involving columns which may contain null values. 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, Handling Null Values, for further discussion of this point.
The following truth tables summarize the outcome of conditional expressions where comparisons are negated by
NOTor joined by
A question mark (
?) represents the truth value unknown,
Trepresents the value
Frepresents the value
This section summarizes standard compliance concerning comparisons.
Mimer Information Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40