SQL BETWEEN Operator

The SQL BETWEEN operator is a special type of comparison operator used to filter records whose values fall within a specified range. You can use it with numeric values, dates, and other comparable data types supported by your database.

The SQL BETWEEN operator is commonly used with the WHERE clause to retrieve only the records that satisfy a particular range condition.

For example, suppose a company stores employee information in a database. You want to find all employees whose salaries range from ₹30,000 to ₹50,000.

Instead of writing separate comparison conditions, you can use the BETWEEN operator to simplify your SQL query.

SELECT *
FROM Employees
WHERE Salary BETWEEN 30000 AND 50000;

This query retrieves all employee records with salaries greater than or equal to ₹30,000 and less than or equal to ₹50,000.

The important point to remember is that BETWEEN includes both boundary values. Therefore, employees earning exactly ₹30,000 or ₹50,000 are also included in the result.

Syntax of SQL BETWEEN Operator

---

The SQL BETWEEN operator uses a simple syntax to retrieve records whose values fall within a specified range. You commonly use it inside the WHERE clause of an SQL statement.

The general syntax of BETWEEN operator in SQL is:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN lower_value AND upper_value;

In this syntax:

• SELECT: Specifies the columns you want to retrieve.
• FROM: Specifies the table containing the data.
• WHERE: Filters records based on a condition.
• column_name: The column whose values you want to evaluate.
• BETWEEN: The operator that checks whether a value falls within a specified range (inclusive of the boundaries).
• lower_value: The lower boundary of the range. It simply represents the start of your range.
• AND: Syntax keyword that separates the lower and upper boundaries in the BETWEEN condition.
• upper_value: The upper boundary of the range. It simply represents the end of your range.

Basic Example of the SQL BETWEEN Operator

---

A company may want to identify employees whose salaries fall within a particular range for salary analysis or reporting. SQL’s BETWEEN operator makes it easy to retrieve employees whose salaries fall within the specified range.

Example: Find employees earning between ₹30,000 and ₹60,000.

Step 1: Create an Employees table

First, create a table named Employees to store employee details, including their IDs, names, departments, and salaries.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

In this query:

• CREATE TABLE Employees creates a table named Employees.
• EmployeeID INT PRIMARY KEY stores a unique ID for each employee.
• EmployeeName VARCHAR(50) stores the employee’s name.
• Department VARCHAR(50) stores the employee’s department.
• Salary DECIMAL(10, 2) stores salary values with up to two decimal places.

Step 2: Insert sample employee records

Insert some sample records into the Employees table.

INSERT INTO Employees
    (EmployeeID, EmployeeName, Department, Salary)
VALUES
    (101, 'Amit', 'IT', 25000),
    (102, 'Priya', 'HR', 30000),
    (103, 'Rahul', 'IT', 40000),
    (104, 'Neha', 'Finance', 50000),
    (105, 'Ravi', 'Marketing', 60000),
    (106, 'Sneha', 'HR', 65000);

Step 3: Display all employee records

Before filtering, retrieve all the records to see the data stored in the table.

SELECT * FROM Employees;

Expected Output:

EmployeeID	EmployeeName	Department	Salary
101	Amit	IT	25,000.00
102	Priya	HR	30,000.00
103	Rahul	IT	40,000.00
104	Neha	Finance	50,000.00
105	Ravi	Marketing	60,000.00
106	Sneha	HR	65,000.00

Step 4: Filter employees using the BETWEEN operator

Now, retrieve employees whose salaries fall between ₹30,000 and ₹60,000.

SELECT *
FROM Employees
WHERE Salary BETWEEN 30000 AND 60000;

Expected Output:

EmployeeID	EmployeeName	Department	Salary
102	Priya	HR	30,000.00
103	Rahul	IT	40,000.00
104	Neha	Finance	50,000.00
105	Ravi	Marketing	60,000.00

In this example:

• SELECT * retrieves all columns from the matching employee records.
• FROM Employees specifies the table from which to retrieve data.
• WHERE applies a condition to filter employee records.
• Salary BETWEEN 30000 AND 60000 selects employees earning at least ₹30,000 and at most ₹60,000.
• Priya and Ravi are included because BETWEEN includes both boundary values.
• Amit and Sneha are excluded because their salaries fall outside the specified range.

How Does the SQL BETWEEN Operator Work?

The SQL BETWEEN operator works by checking whether a column value falls within a specified range. It includes both the lower and upper boundary values in the specified range. The database evaluates the salary of each employee against the specified range.

Employee	Salary	Condition result
Amit	25,000	Excluded — below 30,000
Priya	30,000	Included — lower boundary
Rahul	40,000	Included — within range
Neha	50,000	Included — within range
Ravi	60,000	Included — upper boundary
Sneha	65,000	Excluded — above 60,000

The query returns four employees because their salaries fall between ₹30,000 and ₹60,000, inclusive.

Internal Working of the BETWEEN Operator

The filter condition:

Salary BETWEEN 30000 AND 60000

is logically equivalent to:

Salary >= 30000
AND Salary <= 60000

The first comparison checks whether the salary is greater than or equal to ₹30,000. The second checks whether it is less than or equal to ₹60,000. Both conditions must be satisfied for a row to match.

Where Is the SQL BETWEEN Operator Used?

---

You can use the SQL BETWEEN operator in database queries whenever you need to retrieve records whose values fall within a specific range. The following are some common applications where you can use the BETWEEN operator.

• Filtering employee salaries within the specified range.
• Filtering product prices within the specified range.
• To retrieve students whose examination marks fall within a particular range.
• Filtering orders by date.
• To find bank transactions within a particular amount range.

Real-World Example of BETWEEN Operator in SQL

---

A school may want to find students whose examination marks fall within a specific range. For example, the school wants to identify students who scored between 60 and 90 marks in an examination.

The BETWEEN operator provided by SQL can filter students whose marks are within this range. It includes both boundary values, 60 and 90.

Step 1: Create the Students Table

First, create a table named Students to store student details and examination marks.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50),
    Subject VARCHAR(50),
    Marks INT
);

In this SQL query:

• StudentID stores each student’s unique ID.
• StudentName stores the student’s name.
• Subject stores the examination subject.
• Marks stores the marks obtained by the student.

Step 2: Insert Student Records

Insert sample student records into the Students table.

INSERT INTO Students (StudentID, StudentName, Subject, Marks)
VALUES
(1, 'Amit', 'Science', 45),
(2, 'Priya', 'Science', 60),
(3, 'Rahul', 'Science', 75),
(4, 'Neha', 'Science', 85),
(5, 'Ravi', 'Science', 90),
(6, 'Sneha', 'Science', 95);

Step 3: Display All Student Records

Use the SELECT statement to display all records before applying the filter.

SELECT * FROM Students;

Expected Output:

StudentID	StudentName	Subject	Marks
1	Amit	Science	45
2	Priya	Science	60
3	Rahul	Science	75
4	Neha	Science	85
5	Ravi	Science	90
6	Sneha	Science	95

Step 4: Filter Students Using the BETWEEN Operator

Now, we will find students who scored between 60 and 90 marks using the following SQL query.

SELECT *
FROM Students
WHERE Marks BETWEEN 60 AND 90;

Expected Output:

StudentID	StudentName	Subject	Marks
2	Priya	Science	60
3	Rahul	Science	75
4	Neha	Science	85
5	Ravi	Science	90

Step 5: Understand the Equivalent SQL Condition

The BETWEEN condition is equivalent to using the >= and <= comparison operators together.

SELECT *
FROM Students
WHERE Marks >= 60 AND Marks <= 90;

This query returns the same four students.

SQL NOT BETWEEN Operator

---

The SQL NOT BETWEEN operator is used to select records whose values fall outside a specified range. It is the opposite of the BETWEEN operator. The general syntax to use NOT BETWEEN operator is:

SELECT column1, column2, ...
FROM table_name
WHERE column_name NOT BETWEEN lower_value AND upper_value;

Example of NOT BETWEEN Operator

Suppose a school may want to identify students who scored less than 60 or greater than 90 marks in an examination. The NOT BETWEEN operator can retrieve those students. Run the following SQL query to retrieve this result.

SELECT *
FROM Students
WHERE Marks NOT BETWEEN 60 AND 90;

Expected Output:

StudentID	StudentName	Subject	Marks
1	Amit	Science	45
6	Sneha	Science	95

Key Points of SQL BETWEEN and NOT BETWEEN Operators

• The BETWEEN operator checks whether a column value falls within a specified range. It includes both boundary values.
• Use the SQL BETWEEN operator when you want to filter records within an inclusive range.
• It is commonly used with the WHERE clause.
• It works with numbers, dates, and other appropriately comparable values.
• The NOT BETWEEN operator selects values outside the range.
• If a value is NULL, the condition does not evaluate to true, so that row is not returned by a WHERE clause using this condition.