Home / SQL / BETWEEN

BETWEEN

Overview

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.

BETWEEN is inclusive, meaning it includes the boundary values. It's equivalent to column >= value1 AND column <= value2.

Basic Syntax

BETWEEN Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

BETWEEN vs AND Conditions

BETWEEN is equivalent to using >= AND <=:

Using AND (equivalent)

SELECT * FROM Products
WHERE price >= 50 AND price <= 100;

Using BETWEEN (more readable)

SELECT * FROM Products
WHERE price BETWEEN 50 AND 100;

Both queries return the same results, but BETWEEN is more readable.

Examples with Numeric Values

Example 1: Price Range

Products in price range

SELECT product_name, price
FROM Products
WHERE price BETWEEN 50 AND 100;

Returns: Products with prices from 50 to 100 (inclusive).

Example 2: Quantity Range

Stock quantity range

SELECT *
FROM Products
WHERE stock_quantity BETWEEN 10 AND 50;

Examples with Date Values

Example 1: Date Range

Orders in date range

SELECT *
FROM Orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-01-31';

Returns: Orders from January 1, 2024 to January 31, 2024 (inclusive).

Example 2: Date Range with Time

Include time component

SELECT *
FROM Orders
WHERE order_date BETWEEN '2024-01-01 00:00:00' AND '2024-01-31 23:59:59';

Examples with Text Values

BETWEEN can also work with text, comparing alphabetically:

Alphabetical range

SELECT *
FROM Customers
WHERE name BETWEEN 'A' AND 'M';

Returns: Customers whose names start with A through M (alphabetically).

Product name range

SELECT *
FROM Products
WHERE product_name BETWEEN 'Laptop' AND 'Monitor';

NOT BETWEEN

Use NOT BETWEEN to exclude values within a range:

Exclude range

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

Returns: Products with prices less than 50 or greater than 100.

BETWEEN with Multiple Conditions

You can combine BETWEEN with AND/OR:

Multiple conditions

SELECT *
FROM Products
WHERE price BETWEEN 50 AND 100
  AND category = 'Electronics';

Important Notes

BETWEEN is Inclusive

BETWEEN includes both boundary values:

BETWEEN is inclusive

BETWEEN 50 AND 100 includes:
  - 50 (lower boundary)
  - 100 (upper boundary)
  - All values in between

Order of Values Matters

The first value must be less than or equal to the second value:

Correct order

-- Correct: lower value first
WHERE price BETWEEN 50 AND 100;

-- Incorrect: higher value first (returns no results)
WHERE price BETWEEN 100 AND 50;

Common Use Cases

Filtering products by price range
Finding orders within a date range
Selecting records within numeric ranges
Filtering data by age groups
Selecting alphabetical ranges

BETWEEN vs Comparison Operators

Understanding the equivalence:

Equivalent expressions

-- These are equivalent:
WHERE price BETWEEN 50 AND 100;
WHERE price >= 50 AND price <= 100;

-- NOT BETWEEN is equivalent to:
WHERE price NOT BETWEEN 50 AND 100;
WHERE price < 50 OR price > 100;

Performance Considerations

Indexes help: BETWEEN benefits from indexes on the column
Range scans: BETWEEN often uses index range scans efficiently
Data types: Works efficiently with numeric and date types
Boundary values: Including boundaries allows index usage

Best Practices

Use BETWEEN for ranges: More readable than >= AND <=
Check value order: Ensure lower value comes first
Consider inclusive boundaries: Remember BETWEEN includes boundaries
Use appropriate data types: Works best with numeric and date types
Index the column: For better performance on large tables

Common Mistakes

Reversed order: Putting higher value first returns no results
Excluding boundaries: Forgetting BETWEEN is inclusive
Date format: Using wrong date format for date comparisons
Text comparison: Not understanding alphabetical ordering for text

Next Steps

Learn about other filtering options like IN for lists of values, or explore using Aliases to simplify queries.

← In Aliases →