3 Common Ways to Use SQL BETWEEN Operator

Introduction

In this article, we examine the fundamental SQL BETWEEN operator and examine its applications, requirements, and adaptability to different kinds of data. You’ll obtain a thorough understanding of how to use the BETWEEN operator in your SQL queries, from comprehending the fundamental syntax to using it in real-world scenarios with dates, numbers, and texts. We also offer best practices and insights on possible dangers to guarantee correct results, making this a useful manual for anyone wishing to improve their SQL abilities.

Overview

• Understand the purpose and syntax of the SQL BETWEEN operator for filtering data in relational databases.
• Apply the SQL BETWEEN to effectively filter date ranges in SQL queries.
• Utilize the SQL BETWEEN operator to filter numeric data within specified ranges in SQL queries.
• Implement the SQL BETWEEN operator to filter strings based on alphabetical order in SQL queries.
• Practice best practices and avoid common pitfalls when using the SQL BETWEEN operator in SQL queries.

What is SQL Between?

To understand and work with relational databases better, it’s essential to first understand what constitutes a single condition. A condition in SQL consists of one or more expressions combined with one or more operators. Expressions can be varied, including:

• Numbers
• Columns in a table or view
• String literals, like ‘Teller’
• Built-in functions, such as `concat(‘Learning’, ‘ ‘, ‘SQL’)`
• Subqueries
• Lists of expressions, such as `(‘Teller’, ‘Head Teller’, ‘Operations Manager’)`

Operators used in conditions include:

• Comparison Operators: =, !=, <, >, <>, LIKE, IN, and BETWEEN
• Arithmetic Operators: +, −, *, and /

In the upcoming sections, let’s look at the “BETWEEN” operator in detail and also look at its syntax.

Applications of the BETWEEN Operator

When both an upper and lower limit for a range are required, the BETWEEN operator can be a more useful option than using two separate conditions.

Using BETWEEN on Dates

SELECT emp_id, fname, lname, start_date
FROM employee
WHERE start_date BETWEEN '2005-01-01' AND '2007-01-01';

This query retrieves all employees whose start date falls within 1 Jan, 2005 to 1 Jan, 2007.   Here is the result:

When using the BETWEEN operator, keep in mind to always specify the lower limit first, followed by the upper limit. If you reverse them:

SELECT emp_id, fname, lname, start_date
FROM employee
WHERE start_date BETWEEN '2007-01-01' AND '2005-01-01';

This query returns no results because it is equivalent to:

SELECT emp_id, fname, lname, start_date
FROM employee
WHERE start_date >= '2007-01-01'
AND start_date <= '2005-01-01';

It is impossible to satisfy both conditions simultaneously, resulting in an empty set. 

Additionally, remember that this operator includes the boundary values. So, if your intended range is from January 1, 2005, to December 31, 2006, ensure your query reflects that accurately to avoid unintended inclusions or exclusions.

Using BETWEEN in Numeric Data

SELECT account_id, product_cd, cust_id, avail_balance
FROM account
WHERE avail_balance BETWEEN 3000 AND 5000 order by avail_balance;

This query retrieves all accounts with balances between $3,000 and $5,000:

BETWEEN in Strings

SELECT emp_id, fname, lname, start_date
FROM employee
WHERE fname BETWEEN 'Laura' AND 'Susan';

This query will retrieve the records containing names between Laura and Susan in dictionary order including both Laura and Susan from the previously seen Employee table.

Also Read: Understanding SQL Not Equal Operator

Conclusion

The BETWEEN clause is an essential operator in SQL. Whether working with dates, numbers, or even strings, it simplifies conditions that would otherwise require multiple comparison operators. Remember to always specify the lower limit first and be mindful that the range includes the boundary values. By understanding and utilizing them effectively, it will make working relational databases much easier. 

Frequently Asked Questions

Mounish V

I'm a tech enthusiast, graduated from Vellore Institute of Technology. I'm working as a Data Science Trainee right now. I am very much interested in Deep Learning and Generative AI.