Skip to main content

BETWEEN Operator

The BETWEEN operator is used to filter rows where a column or expression value falls within a specified range, inclusive of the boundary values. It simplifies range-based comparisons in SQL queries.

Syntax

value [NOT] BETWEEN lower_bound AND upper_bound

  • value: The column or expression being tested.

  • lower_bound: The starting value of the range (inclusive).

  • upper_bound: The ending value of the range (inclusive).

Limits

  • Both lower_bound and upper_bound must be comparable to value. Mismatched data types will result in an error.

  • The range must make logical sense; swapping lower_bound and upper_bound will return no results.

Returns

  • Returns TRUE if value is greater than or equal to lower_bound and less than or equal to upper_bound.

  • Returns FALSE if value falls outside the range.

  • Returns NULL if value, lower_bound, or upper_bound is NULL.

Examples

Using BETWEEN with numbers

-- Example 1: Filter rows where a value falls within a numeric range
> SELECT * 
  FROM employees 
  WHERE salary BETWEEN 50000 AND 100000;

-- Example 2: Using NOT BETWEEN to exclude a range
> SELECT * 
  FROM products 
  WHERE price NOT BETWEEN 10 AND 50;

Using BETWEEN with dates

-- Example 3: Filter rows within a date range
> SELECT * 
  FROM orders 
  WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

-- Example 4: Using NOT BETWEEN with dates
> SELECT * 
  FROM events 
  WHERE event_date NOT BETWEEN '2023-06-01' AND '2023-06-30';