SQL-like query language for csv

Comparison Operators

operator description
Relational Operators Compare values
IS Compare a value with ternary value
BETWEEN Check if a value is with in a range of values
LIKE Check if a string matches a pattern
IN Check if a value is within a set of values
ANY Check if any of values fulfill conditions
ALL Check if all of values fulfill conditions
EXISTS Check if a subquery returns at least one row

A comparison operator returns a ternary value.

Relational Operators

operator description
= LHS is equal to RHS
== LHS and RHS are of the same type, and LHS is equal to RHS
< LHS is less than RHS
<= LHS is less than or equal to RHS
> LHS is greater than RHS
>= LHS is greater than or equal to RHS
<>, != LHS is not equal to RHS
relational_operation
  : value operator value
  | row_value operator row_value
value
value
row_value
Row Value

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. If conversions failed, next the relational operater attempts to convert the values to float, and next to datetime, boolean, at last to string.

If either of operands is null or all conversions failed, then the comparison returns UNKNOWN.

Identical operator does not perform automatic type conversion. The result will be true only when both operands are of the same type.

In case of row_values comparison, both of row_values must be tha same lengths. Values at the same indices are compared in order from left to right.

IS

value IS [NOT] NULL
value
value

Check if a value is a null value.

value IS [NOT] ternary
value
value
ternary
ternary

Evaluate the ternary value of a value and check if the ternary value is equal to ternary.

BETWEEN

between_operation
  : value [NOT] BETWEEN low AND high
  | row_value [NOT] BETWEEN row_value_low AND row_value_high
value
value
low
value
high
value
row_value
Row Value
row_value_low
Row Value
row_value_high
Row Value

Check a value is greater than or equal to low and less than or equal to high.

The BETWEEN operation is equivalent to followings.

low <= value AND value <= high
NOT (low <= value AND value <= high)

LIKE

string [NOT] LIKE pattern
string
string
pattern
string

Returns TRUE if string matches pattern, otherwise returns FALSE. If string is a null, return UNKNOWN.

In pattern, following special characters can be used.

%
any number of characters
_ (U+005F Low Line)
exactly one character

IN

in_operation
  : value [NOT] IN (value [, value ...])
  | value [NOT] IN single_field_subquery
  | row_value [NOT] IN (row_value [, row_value ...])
  | row_value [NOT] IN multiple_fields_subquery
value
value
row_value
Row Value
single_field_subquery
subquery
multiple_fields_subquery
subquery

Check if value or row_value is in within the set of values or the result set of select_query.

IN is equivalent to = ANY.

NOT IN is equivalent to <> ALL.

ANY

any_operation
  : value relational_operator ANY (value [, value ...])
  | value relational_operator ANY single_field_subquery
  | row_value relational_operator ANY (row_value [, row_value ...])
  | row_value relational_operator ANY multiple_fields_subquery
value
value
row_value
Row Value
relational_operator
relational operator
single_field_subquery
subquery
multiple_fields_subquery
subquery

Compare value or row_value to each listed values or each records retrieved by select_query. If any of comparison results is TRUE, returns TRUE. If there is no TRUE result and there is at least one UNKNOWN result, returns UNKNOWN. Otherwise returns FALSE.

If select_query returns no record, returns FALSE.

ALL

all_operation
  : value relational_operator ALL (value [, value ...])
  | value relational_operator ALL single_field_subquery
  | row_value relational_operator ALL (row_value [, row_value ...])
  | row_value relational_operator ALL multiple_fields_subquery
value
value
row_value
Row Value
relational_operator
relational operator
single_field_subquery
subquery
multiple_fields_subquery
subquery

Compare value or row_value to every listed values or each records retrieved by select_query. If any of comparison results is FALSE, returns FALSE. If there is no FALSE result and there is at least one UNKNOWN result, returns UNKNOWN. Otherwise returns TRUE.

If select_query returns no record, returns TRUE.

Exists

EXISTS (select_query)
select_query
Select Query

Returns TRUE if a select_query returns at least one record, otherwise returns FALSE.