SQL Between Operator: A Complete Overview with Examples

Numerous tools provided by SQL make it easier to extract important information from a variety of data formats. Sometimes, we need to extract a range of values from each value in each column of a table. For example, to gather information about every employee at a company who was born in a particular decade. An essential part of this query language that helps with these tasks is the SQL Between operator.

Overview

Both developers and analysts use SQL (Structured Query Language) as their go-to tool for querying databases. The BETWEEN operator is one of the many operators in SQL that is particularly useful for filtering data inside a given range.

In SQL, the BETWEEN operator enables you to obtain records whose values fall under a specific range. When working with numerical or date-based data, this operator helps to extract information that falls within a given range.

To help you become proficient with this potent tool, we will go deep into the syntax, use cases, and practical examples of the SQL BETWEEN operator. We will also look at how to use this operator in combination with the NOT Operator, numericals, and SQL between datetime, among others.

What is the SQL Between Operator?

If you want to find out if an expression falls inside a range of numbers, you can use the SQL Between operator. The start and end values of the range are included by this inclusive operator. Text, numeric, or date values are all possible.

The commands SELECT, INSERT, UPDATE, and DELETE can be used with this operator.

Let's examine the syntax of this operator to gain a better understanding of it.

Syntax:

This between SQL syntax is easy to understand. The SELECT command and this operator are frequently used together. Here is the syntax:

SELECT column_names

FROM table_name

WHERE column_name BETWEEN range_start AND range_end;

The SELECT statement specifies the columns that need to be retrieved.

The FROM statement specifies the table from which the columns are being obtained.

The WHERE clause makes use of the SQL BETWEEN operator. The ‘column_name’ option specifies the column to which we wish to apply the range condition. The ‘range_start’ and ‘range_end’ parameters specify the beginning and ending values, respectively, of the range of values.

Let's look at an example table named ‘Employeedetails’:

The following query will be used to extract the ID and name of the workers whose IDs fall between 4 and 7 from this table:

SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName

FROM EmployeeDetails

WHERE EmployeeID BETWEEN 4 AND 7;

As a result, the following will happen:

As we can see, since Between is inclusive, the result also includes ID values 4 and 7.

How to Use Between in SQL with the NOT Operator

To choose the values that do not fall inside the given range, we can alternatively combine the NOT operator and the between function in SQL. In the syntax above, this is accomplished by substituting the NOT BETWEEN keyword for the BETWEEN keyword.

For instance, the following query can be used to extract the ID and salary of every employee in the sample table whose pay does not fall between $30,000 and $80,000:

SELECT EmployeeID, Salary

FROM EmployeeDetails

WHERE Salary NOT BETWEEN 30000 AND 80000;

● SELECT EmployeeID, Salary: Specifies the columns to be retrieved from the table. EmployeeID represents the unique identifier of the employee, and Salary represents the salary of the employee.

● FROM EmployeeDetails: Specifies the table from which to retrieve the data, which is the ‘EmployeeDetails’ table in this case.

● WHERE Salary NOT BETWEEN 30000 AND 80000: Specifies the condition to filter the records. It retrieves records where the Salary does not fall between $30,000 and $80,000.

Output:

How to Use Numeric Values with Between Clause in SQL

To order the output according to a few columns, we can use the ORDER BY statement.

To extract the ID, name, and salary of every employee whose pay falls between $50,000 and $78,000, for instance, from the sample table and sort the results according to salary:

SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName, Salary

FROM EmployeeDetails

WHERE Salary BETWEEN 50000 AND 78000

ORDER BY Salary;

● SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName, Salary: Specifies the columns to be retrieved from the table. EmployeeID represents the unique identifier of the employee, CONCAT(FirstName, ' ', LastName) concatenates the first name and last name to form the full name, and Salary represents the salary of the employee.

● FROM EmployeeDetails: Specifies the table from which to retrieve the data, which is the ‘EmployeeDetails’ table in this case.

● WHERE Salary BETWEEN 50000 AND 78000: Specifies the condition to filter the records. It retrieves records where the Salary falls between $50,000 and $78,000.

● ORDER BY Salary: Specifies the column by which to order the results. In this case, the results will be ordered in ascending order based on the Salary column.

How to Use SQL Between with Dates

The SQL query between two dates returns a syntax error if the date is not enclosed in a single inverted comma when using the SQL Between with dates.

The objective is to obtain the ID, name, and birthdate of employees born between 1994 and 1997 from the ‘Employeedetails’ table:

SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName, Birthdate

FROM EmployeeDetails

WHERE Birthdate BETWEEN '1994-01-01' AND '1997-12-31';

● SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName, Birthdate: Specifies the columns to be retrieved from the table. EmployeeID represents the unique identifier of the employee, CONCAT(FirstName, ' ', LastName) concatenates the first name and last name to form the full name, and Birthdate represents the birthdate of the employee.

● FROM EmployeeDetails: Specifies the table from which to retrieve the data, which is the ‘EmployeeDetails’ table in this case.

● WHERE Birthdate BETWEEN '1994-01-01' AND '1997-12-31': Specifies the condition to filter the records. It retrieves records where the Birthdate falls between January 1, 1994, and December 31, 1997. The date values are enclosed in single quotes to ensure correct syntax when dealing with dates in SQL.

Output:

What if we do not specify the starting and ending values?

The SQL between date ranges must be specified with the exact numbers.

What occurs if we don't do that? Let's see:

As we can see, because the range values were entered incorrectly, the aforementioned query returns an empty set.

How to use Between with Text

Character data types can also be utilized using the SQL Between operator. When utilizing this on text, we must keep in mind that the query will return a syntax error if the data is not included in single inverted commas.

The goal is to obtain every employee's ID and name from the example tables whose names fall between Arun and Rahul:

SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName

FROM EmployeeDetails

WHERE FirstName BETWEEN 'Emily' AND 'John';

● SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName: Specifies the columns to be retrieved from the table. EmployeeID represents the unique identifier of the employee, and CONCAT(FirstName, ' ', LastName) concatenates the first name and last name to form the full name.

● FROM EmployeeDetails: Specifies the table from which to retrieve the data, which is the ‘EmployeeDetails’ table in this case.

● WHERE FirstName BETWEEN 'Emily' AND 'John': Specifies the condition to filter the records. It retrieves records where the FirstName falls alphabetically between Emily and John. The names are enclosed in single quotes to ensure correct syntax when dealing with character data types.

Output:

How to Use the SQL In Operator with SQL Between Operator

Multiple values can be specified in a WHERE clause using the SQL In operator, which also serves as an alternative to multiple OR conditions.

Using this operator with BETWEEN has the following syntax:

SELECT column_names

FROM table_name

WHERE column_1 BETWEEN range_start AND range_end

AND column_2 IN(value_1, value_2, value_3,...value_n);

By placing parentheses around the IN keyword, we can specify the values we want, and the IN operator may be used to determine if the values selected from the column specified in the column_2 argument belong to those values.

Alternatively, we may use the NOT IN keyword in place of the IN keyword to select the values that do not correspond to the values enclosed in parenthesis.

Let's look at a few instances of this.

The goal is to extract the ID, name, and pay of every employee whose name falls between A and R and whose ID falls between 3, 4, 5, or 6 from the example table. We use the following between in SQL query::

SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName, Salary

FROM EmployeeDetails

WHERE FirstName BETWEEN 'A' AND 'R'

AND EmployeeID IN (3, 4, 5, 6);

● SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName, Salary: Specifies the columns to be retrieved from the table. EmployeeID represents the unique identifier of the employee, CONCAT(FirstName, ' ', LastName) concatenates the first name and last name to form the full name, and Salary represents the salary of the employee.

● FROM EmployeeDetails: Specifies the table from which to retrieve the data, which is the ‘EmployeeDetails’ table in this case.

Output:

What if we use the NOT IN keyword?

Let's see what occurs when the NOT IN keyword is used.

To obtain the ID, name, and date of birth of workers whose IDs fall between 3 and 9 and whose names don't include ‘Jane’, ‘Micheal’, or ‘John’:

SELECT EmployeeID, CONCAT(FirstName, ' ', LastName) AS FullName, Birthdate

FROM EmployeeDetails

WHERE EmployeeID BETWEEN 3 AND 9

AND CONCAT(FirstName, ' ', LastName) NOT IN ('Jane Smith', 'Michael Johnson', 'John Doe');

Output:

Final Words

The BETWEEN operator in SQL is a powerful tool for filtering data within a specified range. Whether you are dealing with numerical values or date-based information, the BETWEEN operator allows you to extract relevant records efficiently. By mastering the syntax and understanding its applications, you can streamline your SQL queries and extract valuable insights from your database. So, the next time you need to filter data within a range, remember the BETWEEN operator and leverage its capabilities to your advantage.

Frequently Asked Questions

1. Is there a between command in SQL?

Ans: Yes, the BETWEEN operator in SQL allows filtering data within a specified range of values.

2. How to get data in SQL?

Ans: To obtain data within a range in SQL, you can use the BETWEEN operator in a WHERE clause of a query.

3. What is the difference between conditional in SQL?

Ans: In SQL, the CASE statement is used for conditional logic within queries, while the WHERE clause with BETWEEN is specifically for filtering data within a range.

4. How do you choose between two values in SQL?

Ans: In SQL, you can select between two values using the CASE statement or filter data between two values using the BETWEEN operator in a WHERE clause.

5. How do I run a between command in SQL?

Ans: To execute a BETWEEN command in SQL, you have to incorporate it into a SELECT statement with a WHERE clause, specifying the range of values.

6. Can we use between for string?

Ans: Yes, the BETWEEN operator in SQL can be used for string values, comparing them based on their alphabetical order.

7. How to find the date between two dates in SQL?

Ans: To discover dates within a range in SQL, you can employ the BETWEEN operator within a WHERE clause, specifying the start and end dates.

8. Is SQL between dates inclusive?

Ans: Yes, in SQL, the BETWEEN operator is inclusive, encompassing both the start and end dates in the specified range.

9. What is the difference between IN and BETWEEN in SQL?

Ans: The main distinction lies in their usage: IN is employed to filter data based on a set of specified values, while BETWEEN is utilized to filter data within a specified range.

10. What is the opposite of between in SQL?

Ans: The opposite of the between statement in SQL is achieved by utilizing the NOT BETWEEN operator in a WHERE clause, returning rows where the value falls outside the specified range.