QDB features the following comparison operators:
-
=, <, <=, >, >= and !=, for arithmetic comparisons
-
IN, an operation to test for set membership
-
IS, which works similar to = except when at least
one operand is NULL. If both operands are NULL,
IS evaluates to 1 (true). If one operand is
NULL but the other isn't, the operator evaluates to
0 (false).
-
IS NOT, which works similar to != except when at
least one operand is NULL. If both operands are NULL,
IS NOT evaluates to 0 (false). If one operand
is NULL but the other isn't, the operator evaluates to
1 (true).
-
BETWEEN, the ternary comparison operator, which tests if a value lies within a range
The results of a comparison depend on the storage classes of the
two values being compared, according to the following rules:
- A value with storage class NULL is considered less than any other value (including another value with storage class NULL).
- An INTEGER or REAL value is less than any TEXT or BLOB value.
When you compare an INTEGER or REAL to another INTEGER or REAL, a numerical comparison is performed.
- A TEXT value is less than a BLOB value. When you compare two TEXT values, the C library function memcmp() is used to
determine the result.
- When you compare two BLOB values, the result is always determined using memcmp().
QDB may attempt to convert values between the numeric storage
classes (INTEGER and REAL) and TEXT before performing a comparison.
For binary comparisons, this is done in the cases enumerated below.
The term "expression" below refers to any
SQL scalar expression or literal other than a column value.
- When a column value is compared to the result of an
expression, the affinity of the column is applied to the result of
the expression before the comparison takes place.
- When two column values are compared, if one column has
INTEGER or NUMERIC affinity and the other doesn't, the NUMERIC
affinity is applied to any values with storage class TEXT extracted
from the non-NUMERIC column.
- When the results of two expressions are compared, no conversions occur. The results are compared as they are presented. If a string is compared to a number, the number will always be less than the string.
In QDB, the expression
a BETWEEN b AND c
is equivalent to
a >= b AND a <= c,
even if this means that different affinities are applied to
a in each of the comparisons required to evaluate the expression.
Expressions of the type a IN (SELECT b ....) are handled by the rules enumerated above for binary comparisons (e.g. in a
similar manner to a = b). For example, if b is a column value and a is an expression, then the affinity of b is applied to a before any comparisons take place.
QDB treats the expression a IN (x, y, z) as equivalent to
a = z OR a = y OR a = z.