Comparison Operators
Comparison operators enable you to compare two expressions.
The following table describes each comparison operator and its return values.
Operator | Description | Returns |
---|---|---|
= |
Equal to. Functionally equivalent to == for compatibility with other languages. |
TRUE or FALSE |
== |
Equal to. Functionally equivalent to = for compatibility with other languages. |
TRUE or FALSE |
!= |
Not equal to. Functionally equivalent to <> for compatibility with other languages. |
TRUE or FALSE |
<> |
Not equal to. Functionally equivalent to != for compatibility with other languages. |
TRUE or FALSE |
> |
Greater than. |
TRUE or FALSE |
>= |
Greater than or equal to. |
TRUE or FALSE |
< |
Less than. |
TRUE or FALSE |
<= |
Less than or equal to. |
TRUE or FALSE |
BETWEEN |
Search criteria for a query where the value is between two values, including the end values specified in the range. Values can be numbers, text, or dates. |
TRUE or FALSE |
NOT BETWEEN |
Search criteria for a query where the value is outside the range of two values, including the end values specified in the range. Values can be numbers, text, or dates. |
TRUE or FALSE |
LIKE |
Match string with a wildcard expression. Use % for zero or more wildcards and _ to match any character at this place in a string. The wildcard characters can be escaped by preceding them with a backslash (\). Backslash itself can also be escaped by preceding it with another backslash. |
TRUE or FALSE |
NOT LIKE |
Inverse of LIKE. Return TRUE if string is not similar to given string. |
TRUE or FALSE |
IS NULL |
Field has value of NULL. |
TRUE or FALSE |
IS NOT NULL |
Field has value or is missing. |
TRUE or FALSE |
IS MISSING |
No value for field found. |
TRUE or FALSE |
IS NOT MISSING |
Value for field found or value is NULL. |
TRUE or FALSE |
IS VALUED |
Value for field found. Value is neither missing nor NULL |
TRUE or FALSE |
IS NOT VALUED |
Value for field not found. Value is NULL. |
TRUE or FALSE |
Strings
String comparison is done using a raw-byte collation of UTF-8 encoded strings (sometimes referred to as binary, C, or memcmp). This collation is case sensitive. Case-insensitive comparisons can be performed using the UPPER() or LOWER() functions. See String Functions for more information.
Arrays and Objects
Arrays are compared element-wise. Objects are first compared by length; objects of equal length are compared pairwise, with the pairs sorted by name.
NULL and MISSING
The IS/IS NOT NULL/MISSING family of operators lets you specify conditions based on the existence (or absence) of attributes in a data set.
Additionally, these comparison situations produce the following results.
-
If either operand in a comparison is MISSING, the result is MISSING.
-
If either operand in a comparison is NULL, the result is NULL.
-
If either operand is MISSING or NULL, the result is MISSING or NULL.
Example
SELECT fname, children FROM tutorial WHERE children IS NULL
Returns:
{ "results": [ { "children": null, "fname": "Fred" } ] }
Example
SELECT fname, children FROM tutorial WHERE children IS MISSING
Returns
{ "results": [ { "fname": "Harry" }, { "fname": "Jane" } ] }