BETWEEN Operator in SQL

The BETWEEN operator in SQL is used to filter the result set to include values within a specified range. It can be applied to numeric, text, or date values and includes both the lower and upper bounds.

---

Key Points

1. Inclusive Range Filtering:
   The BETWEEN operator includes the start and end values of the range.

2. Works with Multiple Data Types:
   It can be used with numbers, dates, or even text (depending on the database collation rules).

---

Syntax

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

---

Example

Using BETWEEN with Numbers

SELECT first_name, salary
FROM employees
WHERE salary BETWEEN 50000 AND 100000;

• Retrieves employees whose salaries are between 50,000 and 100,000, inclusive.

Using BETWEEN with Dates

SELECT first_name, hire_date
FROM employees
WHERE hire_date BETWEEN '2020-01-01' AND '2023-12-31';

• Finds employees hired between January 1, 2020, and December 31, 2023.

Using BETWEEN with Text

SELECT product_name
FROM products
WHERE product_name BETWEEN 'A' AND 'M';

• Returns products with names starting alphabetically from 'A' to 'M'.

---

When to Use the BETWEEN Operator

• To filter numeric ranges, such as salaries or quantities.

• To work with date ranges, such as finding records within specific time periods.

• To filter alphabetic data within a range of values.

Note: Some database systems may treat text comparisons differently depending on collation rules. Always ensure proper format when using BETWEEN with dates or strings.