SQL BETWEEN Tutorial

Example Table

We will use the following table named orders for our examples:

        CREATE TABLE orders (
            order_id INT PRIMARY KEY,
            customer_name VARCHAR(50),
            order_date DATE,
            amount DECIMAL(10, 2)
        );

        INSERT INTO orders (order_id, customer_name, order_date, amount) VALUES
        (1, 'Alice', '2023-01-15', 250.00),
        (2, 'Bob', '2023-02-20', 150.00),
        (3, 'Charlie', '2023-03-10', 300.00),
        (4, 'David', '2023-04-05', 100.00),
        (5, 'Eve', '2023-05-25', 200.00);
        

order_id	customer_name	order_date	amount
1	Alice	2023-01-15	250.00
2	Bob	2023-02-20	150.00
3	Charlie	2023-03-10	300.00
4	David	2023-04-05	100.00
5	Eve	2023-05-25	200.00

Using SQL BETWEEN

The SQL BETWEEN operator is used to filter the result set within a certain range. The values can be numbers, text, or dates.

Example 1: Orders Between Two Dates

SELECT * FROM orders WHERE order_date BETWEEN '2023-02-01' AND '2023-04-30';

Result:

order_id	2
customer_name	Bob
order_date	2023-02-20
amount	150.00
order_id	3
customer_name	Charlie
order_date	2023-03-10
amount	300.00
order_id	4
customer_name	David
order_date	2023-04-05
amount	100.00

Example 2: Orders with Amount Between 150 and 300

SELECT * FROM orders WHERE amount BETWEEN 150 AND 300;

Result:

order_id	1
customer_name	Alice
order_date	2023-01-15
amount	250.00
order_id	2
customer_name	Bob
order_date	2023-02-20
amount	150.00
order_id	3
customer_name	Charlie
order_date	2023-03-10
amount	300.00
order_id	5
customer_name	Eve
order_date	2023-05-25
amount	200.00