The SQL BETWEEN operator is used to filter results within a specified range, including the start and end values. It can be applied to numeric, date, and text columns, providing an efficient way to retrieve data that falls within a certain range.

In this article, we will explore the SQL BETWEEN operator with examples.

SQL BETWEEN Operator

The SQL ‘BETWEEN' operator is used to filter the result set within a specified range. It can be applied to numeric, date, and text columns. The BETWEEN operator is inclusive, meaning it includes the start and end values in the result set.

Syntax:

The basic syntax of the ‘BETWEEN' the operator is as follows:

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

Parameters:

• column_name(s): The name of the column(s) you want to retrieve data from.
• table_name: The name of the table containing the data.
• column_name: The name of the column you want to apply the BETWEEN filter to.
• value1: The starting value of the range.
• value2: The ending value of the range.

Creating a Sample Table

Let’s create a sample table named Employees to demonstrate the ‘BETWEEN' operator.

Creating the Table

CREATE TABLE Employees (
    EmployeeID INT,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Salary DECIMAL(10, 2),
    HireDate DATE
);

Inserting Sample Data

INSERT INTO Employees (EmployeeID, FirstName, LastName, Age, Salary, HireDate)
VALUES 
(1, 'John', 'Doe', 28, 50000, '2021-01-15'),
(2, 'Jane', 'Smith', 34, 60000, '2020-03-22'),
(3, 'Sam', 'Brown', 45, 75000, '2018-07-10'),
(4, 'Sue', 'Wilson', 25, 48000, '2021-10-01'),
(5, 'Tom', 'Harris', 38, 68000, '2019-05-12');

Output:

SELECT EmployeeID, FirstName, LastName, Age
FROM Employees
WHERE Age BETWEEN 30 AND 40;

SELECT EmployeeID, FirstName, LastName, HireDate
FROM Employees
WHERE HireDate BETWEEN '2020-01-01' AND '2021-12-31';

SELECT EmployeeID, FirstName, LastName
FROM Employees
WHERE LastName BETWEEN 'Brown' AND 'Smith';