Returns TRUE when the input expression (numeric or string) is within the specified lower and upper boundary.
<expr> [ NOT ] BETWEEN <lower_bound> AND <upper_bound>
Copy
Arguments¶expr
The input expression.
lower_bound
The lower boundary.
upper_bound
The upper boundary.
The function returns a value of type BOOLEAN.
Usage notes¶expr BETWEEN lower_bound AND upper_bound is equivalent to expr >= lower_bound AND expr <= upper_bound.
The specified upper boundary must be greater than the lower boundary.
The data types of the argument values must be the same or compatible.
If the function implicitly casts a value to a different data type, it might return unexpected results.
For example, when expr is a TIMESTAMP value, and the lower_bound and upper_bound values are DATE values, the DATE values are implicitly cast to TIMESTAMP values, and the time is set to 00:00:00. For the following WHERE clause, assume timestamp_column is a column of type TIMESTAMP in a table:
WHERE timestamp_column BETWEEN '2025-04-30' AND '2025-04-31'
Copy
When the DATE values are implicitly cast, the WHERE clause is interpreted as the following:
WHERE timestamp_column BETWEEN '2025-04-30 00:00:00' AND '2025-04-31 00:00:00'
Copy
With this WHERE clause, the function returns FALSE for virtually all timestamp_column values on 2025-04-31, which might not be intended. To avoid this specific issue, you can specify the next day for upper_bound when you call the function:
WHERE timestamp_column BETWEEN '2025-04-30' AND '2025-05-01'
Copy
The expression A BETWEEN X AND Y is equivalent to A >= X AND A <= Y. The collations used for comparing with X and Y are independent and do not need to be identical, but both need to be compatible with the collation of A.
Here are a few simple examples of using BETWEEN with numeric and string values:
SELECT 'true' WHERE 1 BETWEEN 0 AND 10;
Copy
+--------+ | 'TRUE' | |--------| | true | +--------+
SELECT 'true' WHERE 1.35 BETWEEN 1 AND 2;
Copy
+--------+ | 'TRUE' | |--------| | true | +--------+
SELECT 'true' WHERE 'the' BETWEEN 'that' AND 'then';
Copy
+--------+ | 'TRUE' | |--------| | true | +--------+
The following examples use COLLATE with BETWEEN:
SELECT 'm' BETWEEN COLLATE('A', 'lower') AND COLLATE('Z', 'lower');
Copy
+-------------------------------------------------------------+
| 'M' BETWEEN COLLATE('A', 'LOWER') AND COLLATE('Z', 'LOWER') |
|-------------------------------------------------------------|
| True |
+-------------------------------------------------------------+
SELECT COLLATE('m', 'upper') BETWEEN 'A' AND 'Z';
Copy
+-------------------------------------------+
| COLLATE('M', 'UPPER') BETWEEN 'A' AND 'Z' |
|-------------------------------------------|
| True |
+-------------------------------------------+
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4