BETWEEN¶

Tests whether a value falls within a specified range (inclusive).

Syntax¶

The BETWEEN operator is used in WHERE clauses:

SELECT column1, column2
FROM table_name
WHERE column1 BETWEEN lower_value AND upper_value

Parameters¶

expression: The value to test. Can be a column name, calculation, or any valid expression.
lower_bound: The lower bound of the range (inclusive).
upper_bound: The upper bound of the range (inclusive).
NOT (optional): Negates the result - returns true if the value is outside the range.

Returns¶

Returns: - TRUE if expression >= lower_bound AND expression <= upper_bound - FALSE otherwise - NULL if any operand is NULL

With NOT BETWEEN: - TRUE if expression < lower_bound OR expression > upper_bound - FALSE otherwise - NULL if any operand is NULL

Important: If lower_bound > upper_bound, the range is empty and BETWEEN always returns FALSE.

Examples¶

Setup¶

For these examples, assume we have a products table:

CREATE TABLE products(
    id BIGINT,
    name STRING,
    price BIGINT,
    stock INTEGER,
    PRIMARY KEY(id))

INSERT INTO products VALUES
    (1, 'Widget A', 100, 50),
    (2, 'Widget B', 150, 30),
    (3, 'Gadget X', 200, 20),
    (4, 'Tool A', 80, 100),
    (5, 'Tool B', 120, 15)

BETWEEN with Numbers¶

Find products with prices between 100 and 150 (inclusive):

SELECT name, price
FROM products
WHERE price BETWEEN 100 AND 150

`name`	`price`
`"Widget A"`	`100`
`"Widget B"`	`150`
`"Tool B"`	`120`

NOT BETWEEN¶

Find products with prices outside the range 100-150:

SELECT name, price
FROM products
WHERE price NOT BETWEEN 100 AND 150

`name`	`price`
`"Tool A"`	`80`
`"Gadget X"`	`200`

BETWEEN with Equal Bounds¶

Test for exact value using BETWEEN:

SELECT name, price
FROM products
WHERE price BETWEEN 100 AND 100

`name`	`price`
`"Widget A"`	`100`

This is equivalent to WHERE price = 100.

Empty Range¶

If lower bound > upper bound, no rows match:

SELECT name, price
FROM products
WHERE price BETWEEN 150 AND 100

Returns empty result set (no rows).

Combined with OR¶

Use multiple BETWEEN clauses with OR:

SELECT name, price
FROM products
WHERE price BETWEEN 80 AND 100 OR price BETWEEN 180 AND 220

`name`	`price`
`"Tool A"`	`80`
`"Widget A"`	`100`
`"Gadget X"`	`200`

Important Notes¶

Inclusive Range¶

BETWEEN uses inclusive bounds. Both lower_bound and upper_bound are included in the matching range.

NULL Handling¶

If any operand (expression, lower_bound, or upper_bound) is NULL, the result is NULL:

-- Returns NULL
WHERE NULL BETWEEN 1 AND 10

-- Returns NULL
WHERE price BETWEEN NULL AND 100

-- Returns NULL
WHERE price BETWEEN 100 AND NULL

Equivalence¶

BETWEEN is shorthand for a range check:

-- These are equivalent:
WHERE x BETWEEN a AND b
WHERE x >= a AND x <= b

-- These are equivalent:
WHERE x NOT BETWEEN a AND b
WHERE x < a OR x > b

Type Compatibility¶

The expression, lower_bound, and upper_bound must be of compatible types. The comparison follows SQL type coercion rules.