BETWEEN range filtering in MySQL

Introduction

The BETWEEN filter helps you find values that fall within a specific range. It makes searching easier when you want to check if something is between two limits.

Finding all sales made between two dates.

Listing products priced between $10 and $50.

Getting students with scores between 70 and 90.

Filtering events happening between two times.

Selecting employees with ages between 25 and 35.

Syntax

MySQL

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

BETWEEN includes both value1 and value2 in the results.

Works with numbers, dates, and text (alphabetical order).

Examples

Finds orders placed in January 2024, including the first and last day.

MySQL

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

Lists product names priced from $10 up to $50.

MySQL

SELECT name FROM products WHERE price BETWEEN 10 AND 50;

Gets employees whose age is between 25 and 35 years.

MySQL

SELECT * FROM employees WHERE age BETWEEN 25 AND 35;

Sample Program

This creates a simple employees table, adds four employees, and selects those aged between 25 and 35.

MySQL

CREATE TABLE employees (id INT, name VARCHAR(20), age INT);
INSERT INTO employees VALUES (1, 'Alice', 24), (2, 'Bob', 30), (3, 'Carol', 35), (4, 'Dave', 40);
SELECT name, age FROM employees WHERE age BETWEEN 25 AND 35 ORDER BY age;

Important Notes

BETWEEN is inclusive: it includes the boundary values.

For dates, use the format 'YYYY-MM-DD' or your database's date format.

BETWEEN works well for continuous ranges but not for non-continuous sets.

Summary

BETWEEN helps filter data within a range easily.

It includes both the start and end values.

Works with numbers, dates, and text.