Beyond BETWEEN: Exploring Alternative Filtering Techniques in MariaDB

Functionality

It selects rows where a column value falls outside the specified minimum and maximum values.

Syntax

expr NOT BETWEEN min AND max

max: This is the upper bound of the range (inclusive).
min: This is the lower bound of the range (inclusive).
expr: This is the column you want to check against the range.

How it Works

Conversely, if the expression falls within the range (min to max), the row is excluded.
If the expression is less than min or greater than max, the row is included in the result.
The expression (expr) is evaluated against both min and max values.

Example

SELECT * FROM orders
WHERE date NOT BETWEEN '2023-12-01' AND '2024-01-01';

This query will return all orders except those placed between December 1st, 2023, and January 1st, 2024 (inclusive).

Key Points

Remember that for date/time comparisons, ensure consistent formatting between the expression and the specified values.
It can be used with various data types like dates, numbers, and strings (with proper comparisons).
NOT BETWEEN is essentially a shorthand for NOT (expr BETWEEN min AND max).

For NULL values, the behavior depends on MariaDB configuration. It might be treated as less than any value, greater than any value, or ignored altogether.
If min is greater than max, the query will always return NULL because no value can be both less than a higher value and greater than a lower value simultaneously.

Excluding Numbers Outside a Range

This query selects products with a price not between $50 and $100 (exclusive):

SELECT * FROM products
WHERE price NOT BETWEEN 50 AND 100;

Finding Orders Outside a Specific Month

This query retrieves orders placed in any month except February:

SELECT * FROM orders
WHERE MONTH(order_date) NOT BETWEEN 2 AND 2;  -- MONTH function extracts month number

Excluding Text Values with Wildcards

This query finds users with usernames that don't start with 'admin_' (ignoring case):

SELECT * FROM users
WHERE username NOT BETWEEN 'admin_' AND 'admin_\zzz';  -- Wildcard for any characters

Combining > and < Operators

This is a straightforward alternative for simple range exclusions. If you want to find orders placed outside the range (1, 5), you can rewrite the query as:

SELECT * FROM orders
WHERE value < 1 OR value > 5;

Using NOT IN with a Subquery

This approach is useful when the excluded range is defined by a set of specific values. For example, to exclude orders with IDs 2, 4, and 7:

SELECT * FROM orders
WHERE id NOT IN (2, 4, 7);

LEFT JOIN with Filtering

This method can be helpful when working with related tables. Imagine a table products and another table excluded_prices containing prices to exclude. You can achieve the same filtering as NOT BETWEEN using a LEFT JOIN and filtering on the joined table:

SELECT p.*
FROM products p
LEFT JOIN excluded_prices ex ON p.price = ex.price
WHERE ex.price IS NULL;  -- Include only rows where price is not in excluded_prices

Choosing the Right Alternative

The best alternative depends on the specific scenario:

When dealing with related tables and filtering based on another table's values, a LEFT JOIN with filtering might be suitable.
If the excluded range is a set of specific values, NOT IN with a subquery can be efficient.
For simple range exclusions, > and < might be sufficient.

Choose the approach that best reflects the logic of your query and maintains readability.
When dealing with large datasets and complex filtering, consider using indexes on the relevant columns for better performance.
NOT BETWEEN might be slightly more performant for basic range exclusions compared to combining > and < operators.