SQL BETWEEN Operator Overview

The SQL BETWEEN operator is used to filter the results within a specified range. It is commonly used in the WHERE clause to select values that fall between a given lower and upper limit. The BETWEEN operator can be used with numerical, date, and text data types.

BETWEEN Syntax

The syntax for the BETWEEN operator is as follows:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

SQL BETWEEN Operator Example

To illustrate the BETWEEN operator, let’s use the following unique tables:

Sales Table

CREATE TABLE Sales (
    SaleID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    SaleDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO Sales (SaleID, ProductName, SaleDate, Amount)
VALUES
(1, 'Laptop', '2024-01-10', 1200.00),
(2, 'Smartphone', '2024-02-20', 800.00),
(3, 'Tablet', '2024-03-15', 450.00),
(4, 'Headphones', '2024-04-01', 150.00),
(5, 'Monitor', '2024-05-10', 300.00);

Using BETWEEN to Filter Numerical Ranges

To find all sales with an amount between $100 and $500, use the following query:

SELECT 
    SaleID, 
    ProductName, 
    SaleDate, 
    Amount
FROM 
    Sales
WHERE 
    Amount BETWEEN 100 AND 500;

Using BETWEEN to Filter Date Ranges

To find sales that occurred between January 1, 2024, and April 30, 2024, use the following query:

SELECT 
    SaleID, 
    ProductName, 
    SaleDate, 
    Amount
FROM 
    Sales
WHERE 
    SaleDate BETWEEN '2024-01-01' AND '2024-04-30';

Using BETWEEN with Text Values

For text values, the BETWEEN operator checks if a value falls within a given range of text. For instance, to find products with names between ‘Headphones’ and ‘Tablet’, use the following query:

SELECT 
    SaleID, 
    ProductName, 
    SaleDate, 
    Amount
FROM 
    Sales
WHERE 
    ProductName BETWEEN 'Headphones' AND 'Tablet';

Important Points About SQL BETWEEN Operator

• The BETWEEN operator is inclusive, meaning it includes the boundary values specified.
• It can be used with numerical, date, and text data types.
• When used with dates, the format should match the database’s date format.
• For text data types, the BETWEEN operator compares values lexicographically (alphabetical order).

SQL BETWEEN Operator with NOT Keyword

To exclude values within a specified range, use the NOT BETWEEN keyword. For example, to find all sales amounts not between $100 and $500:

SELECT 
    SaleID, 
    ProductName, 
    SaleDate, 
    Amount
FROM 
    Sales
WHERE 
    Amount NOT BETWEEN 100 AND 500;

SQL BETWEEN Operator with Aliases Example

Using table aliases can simplify queries, especially when dealing with multiple tables:

SELECT 
    s.SaleID, 
    s.ProductName, 
    s.SaleDate, 
    s.Amount
FROM 
    Sales s
WHERE 
    s.Amount BETWEEN 100 AND 500;