17.1 Converting Data Types

Numeric data types are all mutually comparable and mutually assignable. If an assignment would result in a loss of the most significant digits, an exception condition is raised. If least significant digits are lost, the implementation defines what rounding or truncating occurs and does not report an exception condition. Most often, one value is converted to the same data type as the other and then the comparison is done in the usual way. The chosen data type is the “higher” of the two, using the following ordering: SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, REAL, FLOAT, DOUBLE PRECISION.

Floating-point hardware will often affect comparisons for REAL, FLOAT, and DOUBLE PRECISION numbers. There is no good way to avoid this, since it is not always reasonable to use DECIMAL or NUMERIC in their place. A host language will probably use the same floating-point hardware, so at least errors will be constant across the application.

CHARACTER (or CHAR) and CHARACTER VARYING (or VARCHAR) data types are comparable if and only if they are taken from the same character repertoire. This means that ASCII characters cannot be compared with graphics characters, English cannot be compared to Arabic, and so on. In most implementations this is not a problem, because the database usually has only one repertoire.

The comparison takes the shorter of the two strings and pads it with spaces. The strings are compared position by position from left to right, using the collating sequence for the repertoire—ASCII or EBCDIC in most cases.

Temporal (or < datetime >, as they are called in the standard) data types are mutually assignable only if the source and target of the assignment have the same < datetime > fields. That is, you cannot compare a date and a time. The CAST() operator can do explicit type conversions before you do a comparison.

Here is a table of the valid combinations of source (rows in the table) and target (columns) data types in Standard SQL. Y (“yes”) means that the combination is syntactically valid without restriction; M (“maybe”) indicates that the combination is valid subject to other syntax rules; and N (“no”) indicates that the combination is not valid.

Where:

EN = Exact Numeric

AN = Approximate Numeric

C = Character (Fixed- or Variable-length)

FC = Fixed-length Character

VC = Variable-length Character

B = Bit String (Fixed- or Variable-length)

FB = Fixed-length Bit String

VB = Variable-length Bit String

D = Date

T = Time

TS = Timestamp

YM = Year-Month Interval

DT = Day-Time Interval

17.2 Row Comparisons in SQL

Standard SQL generalized the theta operators so they would work on row expressions and not just on scalars. This is not a popular feature yet, but it is very handy for situations where a key is made from more than one column, and so forth. This makes SQL more orthogonal and it has an intuitive feel to it. Take three row constants:

A = (10, 20, 30, 40);

B = (10, NULL, 30, 40);

C = (10, NULL, 30, 100);

It seems reasonable to define a row comparison as valid only when the data types of each corresponding column in the rows are union-compatible. If not, the operation is an error and should report a warning. It also seems reasonable to define the results of the comparison to the AND-ed results of each corresponding column using the same operator. That is, (A = B) becomes:

((10, 20, 30, 40) = (10, NULL, 30, 40));

becomes:

((10 = 10) AND (20 = NULL) AND (30 = 30) AND (40 = 40))

becomes:

(TRUE AND UNKNOWN AND TRUE AND TRUE);

becomes:

(UNKNOWN);

This seems to be reasonable and conforms to the idea that a NULL is a missing value that we expect to resolve at a future date, so we cannot draw a conclusion about this comparison just yet. Now consider the comparison (A = C), which becomes:

((10, 20, 30, 40) = (10, NULL, 30, 100));

becomes:

((10 = 10) AND (20 = NULL) AND (30 = 30) AND (40 = 100));

becomes:

(TRUE AND UNKNOWN AND TRUE AND FALSE);

becomes:

(FALSE);

There is no way to pick a value from column 2 of row C such that the UNKNOWN result will change to TRUE because the fourth column is always FALSE. This leaves you with a situation that is not very intuitive. The first case can resolve to TRUE or FALSE, but the second case can only go to FALSE.

Standard SQL decided that the theta operators would work as shown in the table below. The expression RX < comp op > RY is shorthand for a row RX compared to a row RY; likewise, RXi means the i-th column in the row RX. The results are still TRUE, FALSE, or UNKNOWN, if there is no error in type matching. The rules favor solid tests for TRUE or FALSE, using UNKNOWN as a last resort.

The idea of these rules is that as you read the rows from left to right, the values in one row are always greater than or less than) those in the other row after some column. This is how it would work if you were alphabetizing words.

The rules are:

1. RX = RY is TRUE if and only if RXi = RYi for all i.

2. RX <> RY is TRUE if and only if RXi <> RYi for some i.

3. RX < RY is TRUE if and only if RXi = RYi for all i < n and RXn < RYn for some n.

4. RX > RY is TRUE if and only if RXi = RYi for all i < n and RXn > RYn for some n.

5. RX < = RY is TRUE if and only if Rx = Ry or Rx < Ry.

6. RX > =  RY is TRUE if and only if Rx = Ry or Rx > Ry.

7. RX = RY is FALSE if and only if RX <> RY is TRUE.

8. RX <> RY is FALSE if and only if RX = RY is TRUE.

9. RX < RY is FALSE if and only if RX > =  RY is TRUE.

10. RX > RY is FALSE if and only if RX < = RY is TRUE.

11. RX < = RY is FALSE if and only if RX > RY is TRUE.

12. 17. RX > =  RY is FALSE if and only if RX < RY is TRUE.

13. RX < comp op > RY is UNKNOWN if and only if RX < comp op > RY is neither TRUE nor FALSE.

The negations are defined so that the NOT operator will still have its usual properties. Notice that a NULL in a row will give an UNKNOWN result in a comparison. Consider this expression:

(a, b, c) < (x, y, z)

which becomes

((a < x)

OR ((a = x) AND (b < y))

OR ((a = x) AND (b = y) AND (c < z)))

The standard allows a single-row expression of any sort, including a single-row subquery, on either side of a comparison. Likewise, the BETWEEN predicate can use row expressions in any position in Standard SQL.

17.3 IS [NOT] DISTINCT FROM Operator

The SQL 2003 Standards added a verbose but useful theta operator. SQL has two kinds of comparisons, or equivalence classes; equality and grouping.

Equality treats NULLs as incomparable and gets us into the three valued logic that returns {TRUE, FALSE, UNKNOWN}.

Grouping treats NULLs as equal values and gets us into the usual two valued logic that returns {TRUE, FALSE}. This is why a GROUP BY puts all the NULLs in one group and you get that behavior in other places.

The theta operators we have discussed so far are based on the equality model, so if you wanted a comparison that grouped NULLs, you had to write elaborate CASE expressions. Now you can do it in one infix operator for either rows or scalars.

< expression 1 > IS NOT DISTINCT FROM < expression 2 >

is logically equivalent to

(< expression 1 > IS NOT NULL

AND < expression 2 > IS NOT NULL

AND < expression 1 > = < expression 2 >)

OR (< expression 1 > IS NULL AND < expression 2 > IS NULL)

Thus following the usual pattern for adding NOT into SQL constructs

< expression 1 > IS DISTINCT FROM < expression 2 >

is a shorthand for

NOT (< expression 1 > IS NOT DISTINCT FROM < expression 2 >)

This double negative was because the IS NOT DISTINCT FROM was defined first. I have no idea why.

You will see an attempt to get this functionality with search conditions like:

COALESCE (< expression 1 >, < absurd non-null value >) = COALESCE (< expression 2 >, < absurd non-null value >)

This avoids the problem that if the second parameter, an absurd value, was NULL, then COALESCE will be “NULL = NULL” which will result in UNKNOWN.

17.4 Monadic Operators

A monadic operator returns a logical value. But the problem in SQL is that we have scalar values, row values and table values. These operators are defined for one or more levels.

SELECT *
FROM Personnel
WHERE (job_title = 'Programmer'
AND birth_date < CAST ('1975-01-18' AS DATE)
AND (salary_amt < 50000.00) IS UNKNOWN;

SELECT *
FROM Personnel
WHERE (job_title IS NULL
AND birth_date < CAST ('1975-01-18' AS DATE)
AND salary_amt < 50000.00.00)
 OR (job_title = 'Programmer'
AND birth_date IS NULL
AND salary_amt < 50000.00.00)
 OR (job_title = 'Programmer'
AND birth_date < CAST ('1975-01-18' AS DATE)
AND salary_amt IS NULL)
 OR (job_title IS NULL
AND birth_date IS NULL
AND salary_amt < 50000.00.00)
 OR (job_title IS NULL
AND birth_date < CAST ('1975-01-18' AS DATE)
AND salary_amt IS NULL)
 OR (job_title = 'Programmer'
AND birth_date IS NULL
AND salary_amt IS NULL)
 OR (job_title IS NULL
AND birth_date IS NULL
AND salary_amt IS NULL);