7.14.1. between¶
7.14.1.1. Summary¶
between is used for checking the specified value exists in the specific range.
It is often used for combination with filter option in select.
7.14.1.2. Syntax¶
between has five parameters:
between(column_or_value, min, min_border, max, max_border)
7.14.1.3. Usage¶
Here are a schema definition and sample data to show usage:
Execution example:
table_create Users TABLE_HASH_KEY ShortText
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create Users age COLUMN_SCALAR Int32
# [[0, 1337566253.89858, 0.000355720520019531], true]
table_create Ages TABLE_HASH_KEY Int32
# [[0, 1337566253.89858, 0.000355720520019531], true]
column_create Ages user_age COLUMN_INDEX Users age
# [[0, 1337566253.89858, 0.000355720520019531], true]
load --table Users
[
{"_key": "Alice", "age": 12},
{"_key": "Bob", "age": 13},
{"_key": "Calros", "age": 15},
{"_key": "Dave", "age": 16},
{"_key": "Eric", "age": 20}
{"_key": "Frank", "age": 21}
]
# [[0, 1337566253.89858, 0.000355720520019531], 6]
Here is the query to show the persons to match PG-13 rating (MPAA).
Execution example:
select Users --filter 'between(age, 13, "include", 16, "include")'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 3
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "age",
# "Int32"
# ]
# ],
# [
# 2,
# "Bob",
# 13
# ],
# [
# 3,
# "Calros",
# 15
# ],
# [
# 4,
# "Dave",
# 16
# ]
# ]
# ]
# ]
It returns 13, 14, 15 and 16 years old users.
between function accepts not only a column of table, but also the value.
If you specify the value as 1st parameter, it is checked whether the value is included or not. if it matches to the specified range, it returns the all records because between function returns true.
If it doesn't match to the specified range, it returns no records because between function returns false.
Execution example:
select Users --filter 'between(14, 13, "include", 16, "include")'
# [
# [
# 0,
# 1337566253.89858,
# 0.000355720520019531
# ],
# [
# [
# [
# 6
# ],
# [
# [
# "_id",
# "UInt32"
# ],
# [
# "_key",
# "ShortText"
# ],
# [
# "age",
# "Int32"
# ]
# ],
# [
# 1,
# "Alice",
# 12
# ],
# [
# 2,
# "Bob",
# 13
# ],
# [
# 3,
# "Calros",
# 15
# ],
# [
# 4,
# "Dave",
# 16
# ],
# [
# 5,
# "Eric",
# 20
# ],
# [
# 6,
# "Frank",
# 21
# ]
# ]
# ]
# ]
In the above case, it returns all the records, because 14 exists in between 13 and 16. This behavior is used for checking the specified value exists or not in the table.
7.14.1.4. Parameters¶
There are five required parameters, column_or_value, and min, min_border, max and max_border.
7.14.1.4.1. column_or_value¶
Specifies a column of the table or the value.
7.14.1.4.2. min¶
Specifies the minimal border value of the range.
You can control the behavior that the value of max is included or excluded by max_border parameter.
7.14.1.4.3. min_border¶
Specifies whether the specified range contains the value of min or not.
The value of min_border are either "include" or "exclude". If it is "include", min value is included. If it is "exclude", min value is not included.
7.14.1.4.4. max¶
Specifies the maximum border value of the range.
You can control the behavior that the value of max is included or excluded by max_border parameter.
7.14.1.4.5. max_border¶
Specifies whether the specified range contains the value of max or not.
The value of max_border are either "include" or "exclude". If it is "include", max value is included. If it is "exclude", max value is not included.
7.14.1.5. Return value¶
between returns whether the value of column exists in specified the value of range or not. If record is matched to specified the value of range, it returns true. Otherwise, it returns false.