github.com/mithrandie/csvq@v1.18.1/docs/_posts/2006-01-02-comparison-operators.md (about)

     1  ---
     2  layout: default
     3  title: Comparison Operators - Reference Manual - csvq
     4  category: reference
     5  ---
     6  
     7  # Comparison Operators
     8  
     9  | operator | description |
    10  | :- | :- |
    11  | [Relational Operators](#relational_operators) | Compare values |
    12  | [IS](#is)           | Compare a value with ternary value |
    13  | [BETWEEN](#between) | Check if a value is with in a range of values |
    14  | [LIKE](#like)       | Check if a string matches a pattern |
    15  | [IN](#in)           | Check if a value is within a set of values |
    16  | [ANY](#any)         | Check if any of values fulfill conditions |
    17  | [ALL](#all)         | Check if all values fulfill conditions |
    18  | [EXISTS](#exists)   | Check if a subquery returns at least one row |
    19  
    20  A comparison operator returns a ternary value.
    21  
    22  ## Relational Operators
    23  {: #relational_operators}
    24  
    25  | operator | description                                               |
    26  |:---------|:----------------------------------------------------------|
    27  | \=       | LHS is equal to RHS                                       |
    28  | \=\=     | LHS and RHS are of the same type, and LHS is equal to RHS |
    29  | <        | LHS is less than RHS                                      |
    30  | <\=      | LHS is less than or equal to RHS                          |
    31  | >        | LHS is greater than RHS                                   |
    32  | >\=      | LHS is greater than or equal to RHS                       |
    33  | <>, !\=  | LHS is not equal to RHS                                   |
    34  
    35  ```sql
    36  relational_operation
    37    : value operator value
    38    | row_value operator row_value
    39  ```
    40  
    41  _value_
    42  : [value]({{ '/reference/value.html' | relative_url }})
    43  
    44  _row_value_
    45  : [Row Value]({{ '/reference/row-value.html' | relative_url }})
    46  
    47  Except for identical operator("=="), at first, the relational operator attempts to convert both of operands to integer values, and if both conversions are successful then compares them.
    48  If conversions failed, next the relational operater attempts to convert the values to float, and next to datetime, boolean, at last to string.
    49  
    50  If either of operands is null or all conversions failed, then the comparison returns UNKNOWN.
    51  
    52  Identical operator does not perform automatic type conversion.
    53  The result will be true only when both operands are of the same type.
    54  
    55  In case of _row_values_ comparison, both of _row_values_ must be tha same lengths.
    56  Values at the same indices are compared in order from left to right.
    57  
    58  
    59  ## IS
    60  {: #is}
    61  
    62  ```sql
    63  value IS [NOT] NULL
    64  ```
    65  
    66  _value_
    67  : [value]({{ '/reference/value.html' | relative_url }})
    68  
    69  Check if a _value_ is a null value.
    70  
    71  ```sql
    72  value IS [NOT] ternary
    73  ```
    74  
    75  _value_
    76  : [value]({{ '/reference/value.html' | relative_url }})
    77  
    78  _ternary_
    79  : [ternary]({{ '/reference/value.html#ternary' | relative_url }})
    80  
    81  Evaluate the ternary value of a _value_ and check if the ternary value is equal to _ternary_.
    82  
    83  ## BETWEEN
    84  {: #between}
    85  
    86  ```sql
    87  between_operation
    88    : value [NOT] BETWEEN low AND high
    89    | row_value [NOT] BETWEEN row_value_low AND row_value_high
    90  ```
    91  
    92  _value_
    93  : [value]({{ '/reference/value.html' | relative_url }})
    94  
    95  _low_
    96  : [value]({{ '/reference/value.html' | relative_url }})
    97  
    98  _high_
    99  : [value]({{ '/reference/value.html' | relative_url }})
   100  
   101  _row_value_
   102  : [Row Value]({{ '/reference/row-value.html' | relative_url }})
   103  
   104  _row_value_low_
   105  : [Row Value]({{ '/reference/row-value.html' | relative_url }})
   106  
   107  _row_value_high_
   108  : [Row Value]({{ '/reference/row-value.html' | relative_url }})
   109  
   110  Check a _value_ is greater than or equal to _low_ and less than or equal to _high_.
   111  
   112  The BETWEEN operation is equivalent to followings.
   113  ```sql
   114  low <= value AND value <= high
   115  NOT (low <= value AND value <= high)
   116  ```
   117  
   118  ## LIKE
   119  {: #like}
   120  
   121  ```sql
   122  string [NOT] LIKE pattern
   123  ```
   124  
   125  _string_
   126  : [string]({{ '/reference/value.html#string' | relative_url }})
   127  
   128  _pattern_
   129  : [string]({{ '/reference/value.html#string' | relative_url }})
   130  
   131  Returns TRUE if _string_ matches _pattern_, otherwise returns FALSE.
   132  If _string_ is a null, return UNKNOWN. 
   133  
   134  In _pattern_, following special characters can be used.
   135  
   136  %
   137  : any number of characters
   138  
   139  _ (U+005F Low Line)
   140  : exactly one character
   141  
   142  ## IN
   143  {: #in}
   144  
   145  ```sql
   146  in_operation
   147    : value [NOT] IN (value [, value ...])
   148    | value [NOT] IN single_field_subquery
   149    | row_value [NOT] IN (row_value [, row_value ...])
   150    | row_value [NOT] IN multiple_fields_subquery
   151  ```
   152  
   153  _value_
   154  : [value]({{ '/reference/value.html' | relative_url }})
   155  
   156  _row_value_
   157  : [Row Value]({{ '/reference/row-value.html' | relative_url }})
   158  
   159  _single_field_subquery_
   160  : [subquery]({{ '/reference/value.html#subquery' | relative_url }})
   161  
   162  _multiple_fields_subquery_
   163  : [subquery]({{ '/reference/value.html#subquery' | relative_url }})
   164  
   165  Check if _value_ or _row_value_ is in within the set of _values_ or the result set of _select_query_.
   166  
   167  _IN_ is equivalent to [= ANY](#any).
   168  
   169  _NOT IN_ is equivalent to [<> ALL](#all).
   170  
   171  ## ANY
   172  {: #any}
   173  
   174  ```sql
   175  any_operation
   176    : value relational_operator ANY (value [, value ...])
   177    | value relational_operator ANY single_field_subquery
   178    | row_value relational_operator ANY (row_value [, row_value ...])
   179    | row_value relational_operator ANY multiple_fields_subquery
   180  ```
   181  
   182  _value_
   183  : [value]({{ '/reference/value.html' | relative_url }})
   184  
   185  _row_value_
   186  : [Row Value]({{ '/reference/row-value.html' | relative_url }})
   187  
   188  _relational_operator_
   189  : [relational operator](#relational_operators)
   190  
   191  _single_field_subquery_
   192  : [subquery]({{ '/reference/value.html#subquery' | relative_url }})
   193  
   194  _multiple_fields_subquery_
   195  : [subquery]({{ '/reference/value.html#subquery' | relative_url }})
   196  
   197  Compare _value_ or _row_value_ to each listed _values_ or each records retrieved by _select_query_.
   198  If any of comparison results is TRUE, returns TRUE.
   199  If there is no TRUE result and there is at least one UNKNOWN result, returns UNKNOWN.
   200  Otherwise, returns FALSE.
   201  
   202  If _select_query_ returns no record, returns FALSE.
   203  
   204  ## ALL
   205  {: #all}
   206  
   207  ```sql
   208  all_operation
   209    : value relational_operator ALL (value [, value ...])
   210    | value relational_operator ALL single_field_subquery
   211    | row_value relational_operator ALL (row_value [, row_value ...])
   212    | row_value relational_operator ALL multiple_fields_subquery
   213  ```
   214  
   215  _value_
   216  : [value]({{ '/reference/value.html' | relative_url }})
   217  
   218  _row_value_
   219  : [Row Value]({{ '/reference/row-value.html' | relative_url }})
   220  
   221  _relational_operator_
   222  : [relational operator](#relational_operators)
   223  
   224  _single_field_subquery_
   225  : [subquery]({{ '/reference/value.html#subquery' | relative_url }})
   226  
   227  _multiple_fields_subquery_
   228  : [subquery]({{ '/reference/value.html#subquery' | relative_url }})
   229  
   230  Compare _value_ or _row_value_ to every listed _values_ or each records retrieved by _select_query_.
   231  If any of comparison results is FALSE, returns FALSE.
   232  If there is no FALSE result and there is at least one UNKNOWN result, returns UNKNOWN.
   233  Otherwise, returns TRUE.
   234  
   235  If _select_query_ returns no record, returns TRUE.
   236  
   237  ## Exists
   238  {: #exists}
   239  
   240  ```sql
   241  EXISTS (select_query)
   242  ```
   243  
   244  _select_query_
   245  : [Select Query]({{ '/reference/select-query.html' | relative_url }})
   246  
   247  Returns TRUE if a _select_query_ returns at least one record, otherwise returns FALSE.