BETWEEN with Numbers

The most common use of BETWEEN is with numeric values to find values within a range. BETWEEN is inclusive, meaning it includes the boundary values (1000 and 5000 in the first example).

BETWEEN with Dates

BETWEEN is very useful for filtering data within date ranges.

Tip: When working with date/time values, be careful about time components. For dates with time, '2023-01-31' typically refers to '2023-01-31 00:00:00', so it won't include events later in that day.

BETWEEN with Strings

BETWEEN can also be used with string values to find values within an alphabetical range.

Note: String comparisons are typically case-sensitive and follow the collation rules of the database. 'A' to 'N' would include 'A', 'B', ... 'M' but not 'N' itself, since it compares full strings.

Use NOT BETWEEN to find values outside a specified range.

Example 1: View Count Filtering

This example shows how to find videos within a specific view range:

Example 2: User Join Date Filtering

This example demonstrates finding users who joined during a specific period:

Example 3: Advanced Filtering with Multiple Ranges

This example shows how to combine multiple BETWEEN conditions with other filters:

1. NULL Values

BETWEEN does not match NULL values. If a column contains NULL, it won't be included in the results even if it would logically fall within the range.

2. Value Order Matters

The first value in a BETWEEN condition must be less than or equal to the second value, or no rows will be returned.

3. Data Type Considerations

Both values in a BETWEEN condition should be of the same data type as the column being compared, or implicit conversion will occur which might lead to unexpected results.

4. Performance

BETWEEN is typically optimized by databases and can use indexes effectively, similar to using >= and <= conditions.