SQL Server BETWEEN Operator

The BETWEEN operator in SQL Server is used to filter the query results. It allows you to specify a range of numeric values, dates, or text data types, returning records that fall within the defined range. This tool simplifies queries when searching and avoids the need for multiple AND conditions, making the query more readable and efficient. In today's tutorial, we will go over the syntax of the BETWEEN operator, its real-life applications, and alternatives, using dbForge Studio for SQL Server—the GUI tool to speed up almost any database experience.

Syntax

First things first, so we will begin with an example of the BETWEEN syntax:

SELECT column1, column2, …, columnN
FROM table
WHERE column1 BETWEEN value1 AND value2;

In the SELECT statement above:

• column1, column2, …, columnN: Specifies the columns you would like to retrieve from the table.
• FROM table: Indicates the table from which the data will be selected.
• WHERE: Filters the rows that will be included in the result. It specifies the condition that must be true for a row to be returned.
• BETWEEN value1 AND value2: Specifies the range filter. It retrieves rows where the value in column1 falls between value1 and value2 (inclusive of both values). The BETWEEN operator simplifies checking whether a value lies within the specified range.

Let's see how it works with the help of the AdventureWorks2022 demo database:

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 100 AND 500;

This query retrieves the product IDs, names, and prices for products that have prices between $100 and $500.

Working with Date and Time

Along with prices, sorting date and time is yet another common use case for the BETWEEN operator. It can be particularly helpful when you need to extract data over a certain period: monthly reports, transactions between certain dates, etc.

Filtering date ranges

To filter only those orders that were placed between two dates, you can execute this query:

SELECT SalesOrderID, OrderDate, CustomerID
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2023-03-31' AND '2023-09-15';

As a result, the orders placed between Mar/31/2023 and Sep/15/2023 will be returned.

Using BETWEEN with date and time data types

You can use the BETWEEN operator with date and time data types, such as DATETIME, DATE, TIME, SMALLDATETIME, and DATETIME2. This is useful when filtering records by specific timestamps or ranges that include both date and time:

SELECT SalesOrderID, OrderDate, CustomerID
FROM Sales.SalesOrderHeader
WHERE OrderDate BETWEEN '2023-05-31 08:00:00' AND '2023-05-31 17:00:00';

Unlike the previous example, the query above provides the orders made between 8:00 AM and 5:00 PM on May/31/2023.

Inclusive nature of BETWEEN

An observant reader may have noticed that the BETWEEN queries in our examples return not only the values that fall within the specified range but also include the boundary values themselves.

Let's take a look at an example where we filter the results to include only the products where the ListPrice is precisely between 101.24 and 120.00:

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice BETWEEN 101.24 AND 120.00;

As you can see, the result includes products priced at 101.24, 120.00 and any prices in between.

Replacing the BETWEEN Operator

While the BETWEEN operator is a popular way to filter values within a specific range, there are several alternative methods to achieve the same result using other SQL functions and operators.

Using >= and <= for equivalent comparisons

One of the simplest ways to replicate the logic of BETWEEN is by using a combination of the greater than or equal to (>=) and less than or equal to (<=) operators.

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice >= 100 AND ListPrice <= 500;

This query filters rows where the price is greater than or equal to 100 and less than or equal to 500, which is functionally equivalent to using the BETWEEN operator.

Using GREATEST and LEAST functions

Another approach is to use the GREATEST and LEAST functions, which ensure that the value falls within a specific range. The GREATEST function returns the larger of two values, while the LEAST function returns the smaller one.

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice = GREATEST(100, LEAST(ListPrice, 500));

Using logical operators for equivalence

You can also achieve the same results by using logical operators such as AND and OR. In this case, the logic checks whether the value is not less than the lower bound and not greater than the upper bound.

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE NOT (ListPrice < 100 OR ListPrice > 500);

Using NOT BETWEEN

What if we need to filter all the values besides the specified ones? The NOT BETWEEN operator works similarly to BETWEEN but only returns the data outside the specified range. It excludes the boundary values and all values in between:

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE ListPrice NOT BETWEEN 100 AND 500;

This query returns products with ListPrice that are either below 100 or above 500, excluding prices within the range.

Equivalent AND condition

The NOT BETWEEN operator has alternatives among logical operators as well. For instance, we can use the greater than (>) and less than (<) operators along with the AND condition to achieve the same results as in the previous section:

SELECT ProductID, Name, ListPrice
FROM Production.Product
WHERE NOT (ListPrice > 100 AND ListPrice < 500);                                

Handling NULL values

When the BETWEEN operator encounters a NULL value, it returns NULL or FALSE, depending on the comparison context. SQL treats NULL as an unknown value. Therefore, a column containing NULL will not be included in the result set when using BETWEEN. This means that NULL values are ignored in range comparisons unless they are explicitly handled.

SELECT EmployeeID, FirstName, LastName, HireDate
FROM Employees
WHERE HireDate BETWEEN '2020-01-01' AND '2024-01-01';

In this case, employees with a NULL value for HireDate will not be included in the result set, as BETWEEN does not return rows with NULL values. If you do wish to include or handle NULL values, you can modify your query using IS NULL:

SELECT EmployeeID, FirstName, LastName, HireDate
FROM Employees
WHERE HireDate BETWEEN '2020-01-01' AND '2024-01-01' OR HireDate IS NULL;

Try it yourself with dbForge Studio

Even though SQL Server Management Studio (SSMS) is the most popular and familiar tool that allows you to work with SQL Server databases, it is not the only one. Moreover, in the continuously evolving world of database development, administration, and management, new GUIs are flooding the market. How do you choose the perfect tool for your needs among all this variety?

Let's compare dbForge Studio for SQL Server with SSMS so that you can make a proper decision on which solution best aligns with your daily requirements.

Take advantage of dbForge Studio for SQL Server by downloading a free, fully functional 30-day trial version and installing it on your computer. With a huge pack of advanced features and an intuitive GUI, this all-in-one tool can maximize your productivity and make SQL Server database development, administration, and management process efficient.

For a more visual comparison of the two solutions, watch the SSMS vs dbForge Studio for SQL Server - Features Comparison video on our YouTube channel.

Further learning

• SQL Server Tutorials
• dbForge Studio Documentation
• dbForge Studio Video Tutorials
• Devart Academy