Re: Wanted: algorithm for a kind of "between" query
- From: "--CELKO--" <jcelko212@xxxxxxxxxxxxx>
- Date: 13 Mar 2006 08:08:56 -0800
Here are the rules for row comparisons in Standard SQL taken from SQL
FOR SMARTIES 3-rd edition.
09.02. 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 for 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. 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.
.
- References:
- Wanted: algorithm for a kind of "between" query
- From: PrFry9
- Wanted: algorithm for a kind of "between" query
- Prev by Date: NOVOE2
- Next by Date: [ANNOUCE] New XML Schema Video Tutorial
- Previous by thread: Re: Wanted: algorithm for a kind of "between" query
- Next by thread: SST
- Index(es):
Relevant Pages
|
