SQL BETWEEN Operator

If you’re diving into the world of SQL, understanding the BETWEEN operator can be a game-changer for efficient data querying. The SQL BETWEEN operator is like your magic wand for filtering data, quickly narrowing down results based on a specified range. Whether you’re working with dates, numbers, or text, mastering this tool is essential. Ready to unlock new levels of database wizardry? Let’s explore how you can make the most of the SQL BETWEEN operator!

What is the SQL BETWEEN Operator?

The SQL BETWEEN operator is used in WHERE clauses to filter the result set within a specific range of values. It returns all rows where the column value falls between two values, including the boundary values themselves.

Definition

The BETWEEN operator helps you find data that lies within a range, whether it’s numbers, dates, or even text values (alphabetical ranges).

Syntax Format:

SELECT column_name
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• value1 is the start of the range.
• value2 is the end of the range.
• Both value1 and value2 are included in the results.

When to Use BETWEEN?

You can use the BETWEEN operator with:

1. Numbers – Filter results between two numeric values.
2. Dates – Select records within a date range.
3. Text – Get values that fall alphabetically between two strings.

Example Code Snippet

-- Example 1: Numbers
SELECT * FROM Employees
WHERE Salary BETWEEN 30000 AND 50000;

-- Example 2: Dates
SELECT * FROM Orders
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31';

-- Example 3: Text
SELECT * FROM Customers
WHERE CustomerName BETWEEN 'A' AND 'G';

These examples make it easy to retrieve specific results without writing multiple conditions using AND or OR.

Practical Examples

Let’s look at how the SQL BETWEEN operator works with different data types — numbers, dates, and text — using real-world scenarios.

Example 1: Numeric Range

Use Case: Fetch employees whose salaries are between 30,000 and 50,000.

SELECT EmployeeID, Name, Salary
FROM Employees
WHERE Salary BETWEEN 30000 AND 50000;

Result:

EmployeeID	Name	Salary
102	Priya Shah	32000
108	Arjun Patel	45000
115	Sneha Iyer	50000

Both 30,000 and 50,000 are included in the results.

Example 2: Date Range

Use Case: Find orders placed between 2024-01-01 and 2024-01-31.

SELECT OrderID, CustomerName, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2024-01-01' AND '2024-01-31';

Result:

OrderID	CustomerName	OrderDate
201	Alex Mason	2024-01-05
214	Maya Verma	2024-01-20
222	John Carter	2024-01-30

Useful for monthly reports and billing periods.

Example 3: Textual Range

Use Case: Get customers whose names fall alphabetically between ‘A’ and ‘G’.

SELECT CustomerID, CustomerName
FROM Customers
WHERE CustomerName BETWEEN 'A' AND 'G';

Result:

CustomerID	CustomerName
301	Aaron Smith
312	Bella Davis
329	George Hill

Ideal for alphabetical filters in directories or contact lists.

Key Benefits of Using BETWEEN

The BETWEEN operator isn’t just a shortcut — it’s a smart way to write better SQL. Here’s why it’s widely preferred by developers and data analysts:

1. Clean and Readable Queries

Using BETWEEN makes your SQL statements shorter and easier to understand. Instead of writing:

WHERE Salary >= 30000 AND Salary <= 50000

You simply write:

WHERE Salary BETWEEN 30000 AND 50000

This improves readability, especially when working on complex queries with multiple conditions.

2. Fewer Logical Errors

When using multiple AND conditions, it’s easy to flip comparison operators or mess up the logic. BETWEEN helps eliminate such mistakes by keeping things clear and straightforward.

Instead of this:

WHERE OrderDate >= '2024-01-01' AND OrderDate <= '2024-01-31'

Use this:

WHERE OrderDate BETWEEN '2024-01-01' AND '2024-01-31'

No chance of accidentally writing <= '2024-01-01' or other mix-ups.

3. Works Across Major SQL Databases

The BETWEEN operator is widely supported across all popular relational database systems:

• ✅ MySQL
• ✅ PostgreSQL
• ✅ SQL Server
• ✅ Oracle
• ✅ SQLite

That means you can write portable, cross-platform SQL queries without needing different syntax for different databases.

Common Mistakes and Tips

While the BETWEEN operator is simple to use, it’s easy to make mistakes if you’re not careful with how it behaves. Let’s clear up the most common pitfalls:

1. BETWEEN is Inclusive

Many beginners assume BETWEEN excludes the endpoints, but that’s not true.

SELECT * FROM Employees
WHERE Salary BETWEEN 30000 AND 50000;

This will include employees earning exactly 30,000 and 50,000.
✅ Always remember: BETWEEN includes both the lower and upper bounds.

2. BETWEEN Doesn’t Work with NULL Values

If the column you’re filtering contains NULL values, BETWEEN will not match those rows — because NULL is unknown, and no range comparison can be made with it.

-- This will NOT include rows where Salary is NULL
SELECT * FROM Employees
WHERE Salary BETWEEN 30000 AND 50000;

✅ Tip: Use IS NULL separately if you want to include NULLs.

SELECT * FROM Employees
WHERE Salary BETWEEN 30000 AND 50000 OR Salary IS NULL;

3. Incorrect vs. Correct Usage

Let’s compare some common mistakes:

❌ Incorrect Usage	✅ Correct Usage
WHERE date_column BETWEEN '2024-31-01' AND '2024-01-01'	WHERE date_column BETWEEN '2024-01-01' AND '2024-01-31'
WHERE name BETWEEN 'g' AND 'a'	WHERE name BETWEEN 'a' AND 'g'
WHERE age BETWEEN 18 AND NULL	WHERE age BETWEEN 18 AND 25 (and check NULLs separately)

(value1, value2)

Practical Uses of the SQL BETWEEN Operator

1. Customer Segmentation: Suppose you’re working for an e-commerce giant, and you’ve been tasked with segmenting customers for a marketing campaign. By using SQL’s BETWEEN operator, you can easily filter customer data to identify groups by age range, purchase amount, or order dates. For instance, a query could filter all customers between ages 25 and 35 who’ve spent between £100 and £500. This segmentation allows for a targeted approach in marketing efforts, increasing customer engagement.
   


2. Inventory Management: Consider a situation where you manage the inventory for a retail chain. Using the BETWEEN operator can help you efficiently manage stock by filtering products within a specific price range or quantity. Imagine a scenario where you want to identify all products with stock levels between 50 and 200 units. This helps streamline the restocking process by focusing on items that need prioritised attention without sifting through every single product manually.
   


3. Financial Reporting: For financial institutions, precise data reporting is crucial. SQL’s BETWEEN comes in handy to filter transactions within specific date ranges or amounts. Suppose a bank needs to compile a report on transactions between £1,000 and £5,000 over the last quarter. This helps in understanding customer behaviour and financial trends, ensuring more accurate financial planning and risk assessment.

Discover the future of coding with our AI-powered SQL online compiler. It’s a game-changer, letting you instantly write, run, and test your SQL code with the help of AI. Enjoy real-time feedback and quick results, making coding smoother and more efficient than ever before!

Conclusion

Mastering the SQL BETWEEN Operator can significantly enhance your database querying skills. It empowers you to filter data efficiently, saving time and effort. By trying it, you’ll feel accomplished. For more programming insights in Java, Python, C, C++, and beyond, explore Newtum. Happy coding!

Edited and Compiled by

This article was compiled and edited by @rasikadeshpande, who has over 4 years of experience in writing. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author