SQL Topics
BETWEEN Operator
title: BETWEEN Operator
In real-world databases, users frequently need to retrieve records that fall within a specific range rather than matching a single value.
For example:
- A school wants students aged between 18 and 25.
- A company wants employees earning between ā¹30,000 and ā¹80,000.
- An online store wants products priced between ā¹1,000 and ā¹10,000.
- A bank wants transactions made between two dates.
Writing multiple comparison operators for these situations can make queries longer and harder to read.
To simplify range-based filtering, SQL provides the BETWEEN Operator.
The BETWEEN operator allows you to select values that fall within a specified range. It works with numbers, dates, and even text values, making it one of the most useful filtering operators in SQL.
What is the BETWEEN Operator?
The BETWEEN operator is used to filter values within a specific range.
Example:
SELECT *
FROM Students
WHERE Age BETWEEN 18 AND 25;Result:
Only students whose age is between 18 and 25 are returned.
The range includes both boundary values.
Why is BETWEEN Important?
Consider a Students table:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul | 17 |
| 2 | Priya | 20 |
| 3 | Amit | 24 |
| 4 | Neha | 27 |
Without BETWEEN:
SELECT *
FROM Students
WHERE Age >= 18
AND Age <= 25;With BETWEEN:
SELECT *
FROM Students
WHERE Age BETWEEN 18 AND 25;Both queries produce the same result, but BETWEEN is cleaner and easier to read.
Basic BETWEEN Syntax
The general syntax is:
SELECT ColumnName
FROM TableName
WHERE ColumnName BETWEEN Value1 AND Value2;Example:
SELECT *
FROM Employees
WHERE Salary BETWEEN 30000 AND 60000;This returns employees whose salaries fall within the specified range.
Understanding the Syntax
Example:
SELECT *
FROM Products
WHERE Price BETWEEN 1000 AND 5000;SELECT
Retrieves data.
FROM
Specifies the source table.
WHERE
Applies filtering conditions.
BETWEEN
Defines the starting and ending range.
1000 AND 5000
Range boundaries.
All values between these limits are returned.
BETWEEN Includes Boundary Values
A common interview question is whether BETWEEN includes the boundary values.
Example:
SELECT *
FROM Students
WHERE Age BETWEEN 18 AND 25;Returned ages:
18
19
20
21
22
23
24
25Notice:
18 Included
25 IncludedBETWEEN is inclusive.
Equivalent condition:
WHERE Age >= 18
AND Age <= 25Creating a Sample Table
Example:
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(100),
Age INT
);Insert records:
INSERT INTO Students VALUES
(1, 'Rahul', 17),
(2, 'Priya', 20),
(3, 'Amit', 24),
(4, 'Neha', 27);Using BETWEEN with Numbers
Query:
SELECT *
FROM Students
WHERE Age BETWEEN 18 AND 25;Result:
| StudentID | Name | Age |
|---|---|---|
| 2 | Priya | 20 |
| 3 | Amit | 24 |
Only students within the specified age range are returned.
BETWEEN with Salary Ranges
Example:
SELECT *
FROM Employees
WHERE Salary BETWEEN 30000 AND 80000;Result:
Employees earning between ā¹30,000 and ā¹80,000 are displayed.
Useful for payroll analysis.
BETWEEN with Product Prices
Example:
SELECT *
FROM Products
WHERE Price BETWEEN 1000 AND 5000;Result:
Products within the specified price range are returned.
Common in e-commerce filtering systems.
BETWEEN with Dates
BETWEEN is widely used with dates.
Example:
SELECT *
FROM Orders
WHERE OrderDate
BETWEEN '2025-01-01'
AND '2025-01-31';Result:
Orders placed during January 2025 are returned.
This is extremely common in reporting systems.
Date Range Example
Orders table:
| OrderID | OrderDate |
|---|---|
| 1 | 2025-01-05 |
| 2 | 2025-01-15 |
| 3 | 2025-02-10 |
Query:
SELECT *
FROM Orders
WHERE OrderDate
BETWEEN '2025-01-01'
AND '2025-01-31';Result:
| OrderID | OrderDate |
|---|---|
| 1 | 2025-01-05 |
| 2 | 2025-01-15 |
February orders are excluded.
BETWEEN with Text Values
BETWEEN can also compare text alphabetically.
Example:
SELECT *
FROM Students
WHERE Name BETWEEN 'A' AND 'M';Result:
Names beginning within that alphabetical range are returned.
Text comparisons depend on database collation settings.
NOT BETWEEN
Sometimes you need values outside a range.
SQL provides NOT BETWEEN.
Example:
SELECT *
FROM Students
WHERE Age NOT BETWEEN 18 AND 25;Result:
| StudentID | Name | Age |
|---|---|---|
| 1 | Rahul | 17 |
| 4 | Neha | 27 |
Only ages outside the range are returned.
BETWEEN with AND Conditions
Example:
SELECT *
FROM Employees
WHERE Salary BETWEEN 30000 AND 80000
AND Department = 'IT';Result:
Only IT employees within the salary range are displayed.
BETWEEN with ORDER BY
Example:
SELECT *
FROM Products
WHERE Price BETWEEN 1000 AND 5000
ORDER BY Price DESC;Process:
Filter Products
ā
Sort ResultsA common real-world pattern.
Real-World Example
Imagine an online shopping website.
Products table:
| ProductName | Price |
|---|---|
| Mouse | 500 |
| Keyboard | 1500 |
| Monitor | 8000 |
| Laptop | 60000 |
User selects:
Price Range:
ā¹1000 - ā¹10000Query:
SELECT *
FROM Products
WHERE Price BETWEEN 1000 AND 10000;Result:
| ProductName | Price |
|---|---|
| Keyboard | 1500 |
| Monitor | 8000 |
Only matching products are displayed.
BETWEEN vs Comparison Operators
These queries are equivalent:
Using BETWEEN:
SELECT *
FROM Students
WHERE Age BETWEEN 18 AND 25;Using comparisons:
SELECT *
FROM Students
WHERE Age >= 18
AND Age <= 25;BETWEEN improves readability.
Common Errors
Reversing Range Values
Wrong:
WHERE Age BETWEEN 25 AND 18;Usually returns no records.
Correct:
WHERE Age BETWEEN 18 AND 25;Forgetting Quotes for Dates
Wrong:
WHERE OrderDate
BETWEEN 2025-01-01
AND 2025-01-31;Correct:
WHERE OrderDate
BETWEEN '2025-01-01'
AND '2025-01-31';Assuming BETWEEN Excludes Boundaries
Wrong assumption:
18 and 25 are excludedReality:
18 Included
25 IncludedBest Practices
Use BETWEEN for Range Queries
Cleaner than multiple comparison operators.
Verify Boundary Values
Remember that BETWEEN is inclusive.
Use Proper Date Formats
Example:
'2025-01-01'Avoid ambiguous date formats.
Combine with ORDER BY
Improves readability of filtered results.
Use Indexes for Performance
Range searches on indexed columns can be significantly faster.
Common Interview Questions
What is the BETWEEN operator?
BETWEEN filters values that fall within a specified range.
Does BETWEEN include boundary values?
Yes.
Both starting and ending values are included.
Can BETWEEN work with dates?
Yes.
It is commonly used for date range filtering.
What is NOT BETWEEN?
NOT BETWEEN returns values outside the specified range.
Summary
The BETWEEN operator is a powerful SQL feature used to filter records within a specified range. It simplifies queries, improves readability, and is commonly used with numbers, dates, and text values.
In this lesson, you learned:
- What BETWEEN is
- Why it is important
- Basic syntax
- Numeric ranges
- Date ranges
- Text ranges
- NOT BETWEEN
- BETWEEN vs comparison operators
- Common mistakes
- Best practices
Understanding BETWEEN is essential because range-based filtering is one of the most common requirements in SQL querying and reporting.
Next Step
Continue to the next lesson:
IN Operator ā
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for BETWEEN Operator.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, filtering, between
Related SQL Topics