This post focuses on an extremely handy operator, SQL BETWEEN, which is often overlooked yet can immensely optimize your querying process.
What is SQL BETWEEN?
The SQL BETWEEN operator is used in a WHERE clause to select a range of data between two values. This operation is inclusive, meaning the end values are part of the results.
If you need to filter out records from your dataset that lie within a certain range, SQL BETWEEN is your go-to tool.
SQL BETWEEN: The Syntax
Here’s a look at the basic syntax of the BETWEEN operator
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
The above SQL query will return all records from the table table_name where the column_name value falls between value1 and value2 (inclusive).
1) SQL BETWEEN in Action
Let’s consider an example using the following Employees table
| ID | Name | Salary |
| -- | ---- | ------ |
| 1 | John | 5000 |
| 2 | Sara | 8000 |
| 3 | Tom | 15000 |
| 4 | Mary | 7000 |
| 5 | Sam | 11000 |
| 6 | Ben | 3000 |
Say, you want to find all employees earning salaries between 5000 and 10000. This is how you can do it using the BETWEEN operator
SELECT Name, Salary
FROM Employees
WHERE Salary BETWEEN 5000 AND 10000;
And here is your result set
| Name | Salary |
| ---- | ------ |
| John | 5000 |
| Sara | 8000 |
| Mary | 7000 |
2) The NOT BETWEEN Operator
SQL offers the NOT BETWEEN operator to fetch records that lie outside the specified range. The syntax is the same as BETWEEN, but with the addition of the NOT keyword
SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;
Using the Employees table again, let’s fetch all employees who earn either less than 5000 or more than 10000. Here’s the appropriate query
SELECT Name, Salary
FROM Employees
WHERE Salary NOT BETWEEN 5000 AND 10000;
The result would be
| Name | Salary |
| ---- | ------ |
| Tom | 15000 |
| Sam | 11000 |
| Ben | 3000 |
3) BETWEEN with Dates
The BETWEEN operator works seamlessly with date ranges. This is particularly helpful when dealing with data of date or timestamp types.
Let’s assume you have an Orders table
| OrderID | Product | OrderDate |
| ------- | ---------- | ---------- |
| 1 | Apple | 2023-01-15 |
| 2 | Orange | 2023-02-20 |
| 3 | Banana | 2023-03-10 |
| 4 | Grape | 2023-04-30 |
| 5 | Mango | 2023-05-05 |
| 6 | Watermelon | 2023-06-12 |
To get all orders placed between February 1, 2023, and May 31, 2023, you can use
SELECT Product, OrderDate
FROM Orders
WHERE OrderDate BETWEEN '2023-02-01' AND '2023-05-31';
This would yield
| Product | OrderDate |
| ------- | ---------- |
| Orange | 2023-02-20 |
| Banana | 2023-03-10 |
| Grape | 2023-04-30 |
| Mango | 2023-05-05 |
In Conclusion
SQL’s BETWEEN operator is a powerful tool for filtering data within a certain range. It can work with numbers, text (considering alphabetical order), and dates.
Keep in mind that the operation is inclusive, and if you wish to exclude the range boundaries, you’ll need to adjust your values accordingly.
