SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-85
Comparison Predicates
Considerations for Comparison Predicates
When a Comparison Predicate Is True
NonStop SQL/MX determines whether a relationship is true or false by comparing
values in corresponding positions in sequence, until it finds the first nonequal pair.
You cannot use a comparison predicate in a WHERE or HAVING clause to compare
row value constructors when the value expressions in one row value constructor are
equal to null. Use the IS NULL predicate instead.
Suppose that there are two rows with multiple components, X and Y:
X=(X1,X2,...,Xn), Y=(Y1,Y2,...,Yn).
Predicate X=Y is true if for all i=1,...,n: Xi=Yi. For this predicate, NonStop
SQL/MX must look through all values. Predicate X = Y is false if for some i Xi<>Yi.
When SQL finds nonequal components, it stops and does not look at remaining
components.
Predicate X<>Y is true if X=Y is false. If X1<>Y1, NonStop SQL/MX does not look at all
components. It stops and returns a value of false for the X=Y predicate and a value of
true for the X<>Y predicate. Predicate X<>Y is false if X=Y is true, or for all
i=1,...,n: Xi=Yi. In this situation, NonStop SQL/MX must look through all
components.
Predicate X>Y is true if for some index m Xm>Ym and for all i=1,..,m-1: Xi=Yi.
NonStop SQL/MX does not look through all components. It stops when it finds the first
nonequal components, Xm<>Ym. If Xm>Ym, the predicate is true. Otherwise the
predicate is false. The predicate is also false if all components are equal, or X=Y.
Predicate X>=Y is true if X>Y is true or X=Y is true. In this scenario, NonStop SQL/MX
might look through all components and return true if they are all equal. It stops at the
first nonequal components, Xm<>Ym. If Xm>Ym, the predicate is true. Otherwise, it is
false.
Predicate X<Y is true if for some index m Xm<Ym, and for all i=1,..,m-1: Xi=Yi.
NonStop SQL/MX does not look through all components. It stops when it finds the first
nonequal components Xm<>Ym. If Xm<Ym, the predicate is true. Otherwise, the
predicate is false. The predicate is also false if all components are equal, or X=Y.
Predicate X<=Y is true if X<Y is true or X=Y is true. In this scenario, NonStop SQL/MX
might need to look through all components and return true if they are all equal. It stops
at the first nonequal components, Xm<>Ym. If Xm<Ym, the predicate is true. Otherwise,
it is false.
Comparing Character Data
For comparisons between character strings of different lengths, the shorter string is
padded on the right with spaces (HEX 20) until it is the length of the longer string. Both
fixed-length and variable-length strings are padded in this way.