SQL Notes
Learn how to use the SQL BETWEEN operator to filter data within a specified range, work with numbers, dates, and text values, and write cleaner SQL queries.
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.
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 25
Notice:
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 excluded
Reality:
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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, filtering, between, operator
Related SQL Complete Guide Topics