ow the NOT BETWEEN operator works in Mariadb?

The NOT BETWEEN operator is a logical operator that tests whether a value is outside a specified range. It is equivalent to using the NOT operator with the BETWEEN operator. The NOT BETWEEN operator returns 1 (true) if the value is not between the lower and upper bounds, and 0 (false) otherwise.

Syntax

The syntax of the NOT BETWEEN operator is as follows:

value NOT BETWEEN lower_bound AND upper_bound

• value is the expression or column to be tested.
• lower_bound and upper_bound are the expressions or columns that define the range. They can be of any data type that can be compared, such as numeric, date, time, string, etc. The lower_bound must be less than or equal to the upper_bound, otherwise the result is always 0 (false).

Examples

Example 1: Using NOT BETWEEN with numeric values

The following example uses the NOT BETWEEN operator to filter the rows from the products table where the price is not between 10 and 20.

SELECT id, name, price
FROM products
WHERE price NOT BETWEEN 10 AND 20;

The output is:

+----+-----------------+-------+
| id | name            | price |
+----+-----------------+-------+
|  1 | Laptop          |  1000 |
|  2 | Mouse           |     5 |
|  6 | Keyboard        |    25 |
|  7 | Monitor         |   200 |
|  8 | Printer         |   150 |
|  9 | Scanner         |   120 |
+----+-----------------+-------+

Example 2: Using NOT BETWEEN with date values

The following example uses the NOT BETWEEN operator to filter the rows from the orders table where the order_date is not between ‘2024-01-01’ and ‘2024-01-31’.

SELECT id, customer_id, order_date, total_amount
FROM orders
WHERE order_date NOT BETWEEN '2024-01-01' AND '2024-01-31';

The output is:

+----+-------------+------------+---------------+
| id | customer_id | order_date | total_amount  |
+----+-------------+------------+---------------+
|  1 |           1 | 2023-12-15 |       1500.00 |
|  4 |           2 | 2024-02-10 |        500.00 |
|  5 |           3 | 2024-02-15 |        300.00 |
|  6 |           4 | 2024-02-20 |        400.00 |
+----+-------------+------------+---------------+

Example 3: Using NOT BETWEEN with string values

The following example uses the NOT BETWEEN operator to filter the rows from the customers table where the last_name is not between ‘A’ and ‘M’.

SELECT id, first_name, last_name, email
FROM customers
WHERE last_name NOT BETWEEN 'A' AND 'M';

The output is:

+----+------------+-----------+---------------------+
| id | first_name | last_name | email               |
+----+------------+-----------+---------------------+
|  2 | Alice      | Smith     | [email protected] |
|  3 | Bob        | Jones     | [email protected]   |
|  4 | Charlie    | Brown     | [email protected]      |
+----+------------+-----------+---------------------+

Example 4: Using NOT BETWEEN with NULL values

The following example uses the NOT BETWEEN operator to filter the rows from the employees table where the salary is not between 3000 and 5000, or is NULL.

SELECT id, name, department, salary
FROM employees
WHERE salary NOT BETWEEN 3000 AND 5000
OR salary IS NULL;

The output is:

+----+---------+------------+--------+
| id | name    | department | salary |
+----+---------+------------+--------+
|  1 | John    | Sales      |   2000 |
|  2 | Jane    | Marketing  |   6000 |
|  4 | Mary    | HR         |   NULL |
|  5 | Peter   | IT         |   2500 |
|  6 | Patrick | IT         |   7000 |
+----+---------+------------+--------+

Note that the NOT BETWEEN operator returns NULL if any of the operands is NULL. Therefore, to include the rows with NULL values, we need to use the OR operator with the IS NULL operator.

Related Functions

Some of the functions that are related to the NOT BETWEEN operator are:

• BETWEEN: This is the opposite of the NOT BETWEEN operator. It tests whether a value is within a specified range. It returns 1 (true) if the value is between the lower and upper bounds, and 0 (false) otherwise.
• NOT: This is a logical operator that negates the result of another operator or expression. It returns 1 (true) if the operand is 0 (false), and 0 (false) if the operand is 1 (true) or NULL. It can be used with the BETWEEN operator to achieve the same effect as the NOT BETWEEN operator.
• IN: This is a logical operator that tests whether a value matches any value in a list of values. It returns 1 (true) if the value is in the list, and 0 (false) otherwise. It can be used to test for multiple discrete values, instead of a continuous range of values.

For example, the following query uses the IN operator to filter the rows from the products table where the price is either 5, 10, or 15.

SELECT id, name, price
FROM products
WHERE price IN (5, 10, 15);

The output is:

+----+-----------+-------+
| id | name      | price |
+----+-----------+-------+
|  2 | Mouse     |     5 |
|  3 | Pen       |    10 |
|  4 | Notebook  |    15 |
|  5 | USB Drive |    15 |
+----+-----------+-------+

Conclusion

The NOT BETWEEN operator is a useful way to filter the data based on a range of values. It can be used with any data type that can be compared, such as numeric, date, time, string, etc. It is equivalent to using the NOT operator with the BETWEEN operator. It returns 1 (true) if the value is not between the lower and upper bounds, and 0 (false) otherwise. To include the rows with NULL values, we need to use the OR operator with the IS NULL operator. Some of the related functions are BETWEEN, NOT, and IN.