QDB features the following comparison operators:
- =, <, <=, >, >= and
!=, for arithmetic comparisons
- IN, an operator 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, based on 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.
Here, the term expression 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 = x OR a = y OR a = z.