Comparison Operators

    +

    Comparison operators enable you to compare two expressions.

    The following table describes each comparison operator and its return values.

    Table 1. Comparison operators
    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"
        }
      ]
    }