How the BETWEEN AND operator works in Mariadb?

The BETWEEN AND operator in MariaDB is used to filter the result set within a certain range. It is a logical operator that returns true if the operand is within the range specified by the minimum and maximum values.

Syntax

The syntax for the BETWEEN AND operator in MariaDB is as follows:

expression BETWEEN min_value AND max_value

Where:

expression is the value to test.
min_value is the lower bound of the range.
max_value is the upper bound of the range.

The operator returns true if expression is greater than or equal to min_value and less than or equal to max_value.

Examples

Example 1: Numeric Range

This example demonstrates how to use the BETWEEN AND operator with numeric values.

SELECT 15 BETWEEN 10 AND 20;

+----------------------+
| 15 BETWEEN 10 AND 20 |
+----------------------+
|                    1 |
+----------------------+

The output is 1, which indicates true, as 15 is within the range of 10 and 20.

Example 2: Date Range

This example shows the BETWEEN AND operator used with date values.

SELECT '2023-03-16' BETWEEN '2023-01-01' AND '2023-12-31';

+----------------------------------------------------+
| '2023-03-16' BETWEEN '2023-01-01' AND '2023-12-31' |
+----------------------------------------------------+
|                                                  1 |
+----------------------------------------------------+

The output is 1, meaning the date ‘2023-03-16’ falls within the specified date range.

Example 3: Using `BETWEEN AND` with a Table Column

To demonstrate the BETWEEN AND operator with a table column, we first create a table and insert some rows.

DROP TABLE IF EXISTS example_table;
CREATE TABLE example_table (value INT);
INSERT INTO example_table VALUES (10), (15), (20), (25);

SELECT * FROM example_table WHERE value BETWEEN 10 AND 20;

+-------+
| value |
+-------+
|    10 |
|    15 |
|    20 |
+-------+

The output shows the rows where the value column is within the range of 10 and 20.

Example 4: Excluding Bounds

The BETWEEN AND operator includes the bounds. To exclude them, you can use the following:

SELECT 15 BETWEEN 10 AND 20 AND 15 NOT IN (10, 20);

+---------------------------------------------+
| 15 BETWEEN 10 AND 20 AND 15 NOT IN (10, 20) |
+---------------------------------------------+
|                                           1 |
+---------------------------------------------+

The output is 1, indicating true, as 15 is excluded from the range by the NOT IN condition.

Example 5: Character Range

The BETWEEN AND operator can also be used with characters.

SELECT 'm' BETWEEN 'a' AND 'z';

+-------------------------+
| 'm' BETWEEN 'a' AND 'z' |
+-------------------------+
|                       1 |
+-------------------------+

The output is 1, confirming that ’m’ is within the range of ‘a’ to ‘z’.

Below are a few functions related to the MariaDB BETWEEN AND operator:

MariaDB IN() function is used to check if a value is within a set of values.
MariaDB CASE statement is used for conditional logic within SQL queries.
MariaDB IF() function is used to return a value based on a condition.

Conclusion

The BETWEEN AND operator is a versatile tool in MariaDB that allows for easy range comparisons. Whether working with numbers, dates, or strings, it provides a straightforward syntax for defining inclusive ranges in queries. Remember to consider whether you want to include or exclude the boundary values in your range checks, as this can affect the results of your query.