SQL Server Between Operator

In SQL Server, BETWEEN Operator is used to filter data within a specified range. It allows you to retrieve records where a column’s value falls within a certain range of values. for example, let’s say you have a list of ages, and you want to find people who are between 20 and 30 years old. You can use the BETWEEN Operator to make Query very easy and get the result more precisely. Also, we can optimize our query with the help of BETWEEN Operator. After Reading this article you will get a deep understanding of BETWEEN Operator.

Prerequisites: Before understanding the BETWEEN Operator in SQL Server, it’s essential to have a foundational understanding of the following:

1. SQL Basics: Familiarize yourself with basic SQL syntax, including SELECT statements, WHERE clauses, and the general structure of SQL queries.
2. Data Types: Understand the data types of the columns you’ll be working with. This is crucial when specifying the range in the “BETWEEN” Operator.
3. Table Structure: Have knowledge about the structure of the tables you intend to query. Know the names of the columns and their data types.

How to Use BETWEEN Operator

The AND Operator is basically a logical operator which helps in filtering the result from the table. We use the AND Operator with the BETWEEN Operator to create more complex and precise conditions while filtering the result based on the range.

For example, you’re saying, “Show me ages that are between 20 AND 30.” The AND here connects the lower limit (20) and the upper limit (30) of the range you’re interested in. Then gives us the result based on the age between 20 and 30. We will understand with the help of examples.

So, in simple terms, “BETWEEN” helps you pick out things within a range, and “AND” connects the two ends of that range.

Syntax:

SELECT column1, column2, ...
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

• Select the Keyword to select which all column user need the data of.
• From keyword decides from which table you need the data from.
• Where Clause is used here for the special condition all rows of the data shown must follow.

We can understand with the help of a good example with real screenshots how to use the BETWEEN Operator to access different types of values.

Examples

For reference, we have created a database called Employee and in the Employee database, I have created a table called Information. We take the Information table as an example to explain queries. A screenshot of the table is attached below..

Inserting sample data into Information Table

Note: You can also do the operations in your own already-created database and table. By all these commands in sequence, it will create a database and a table and insert values in it.

Information Table

We have taken a Information table for perform Operations usinng BETWEEN Operator.

Id	Name	Age	DateOfBirth
1	Ankit	20	‘2003-08-11’
2	Pratham	25	‘2001-02-08’
3	Aditya	26	‘2000-01-25’
4	Alice	28	‘1995-07-12’
5	Michael	35	‘1988-09-30’
6	Emily	24	‘1999-04-18’
7	Virat	31	‘1992-12-05’

We will be doing operations on the Above Information Table.

Uses of BETWEEN Operator in Different Scenarios

1. BETWEEN Operator with Numerical Data

Suppose we have to find all employees from table Information whose ID is either 3 or 7 or even lie between 3 and 7. Let’s see how we do using the below query.

SELECT * from Information
Where Id BETWEEN 3 AND 7

The Result Looks Like:

Persons whose ID lie between 3 and 7

Explanation: In the above Query, we fetch all the employees information from table Information whose ID is either 3 or 7 or even lies between 3 and 7.

Note: Both the upper limit and lower limit are included in the result

2. BETWEEN Operator with String Data

Let’s find out all employees whose names lie between A and M range.

SELECT * FROM Information
WHERE Name BETWEEN 'A AND 'M'

The Result Looks Like:

Person whose name lie between A and B

Explanation: In the above Query, we fetch all the data of those employees from the Information table whose name characters lie in A and M range. Hence all the Names Starting with character in between ‘A‘ and ‘M‘

3. BETWEEN Operator with Date Values

Assume we need to find the data of all the employee whose data of birth lie in between ‘1999-01-01‘ and ‘2001-12-31‘.

SELECT * FROM Information
WHERE DateOfBirth BETWEEN '1999-01-01' AND '2001-12-31'

The Result Looks Like:

Person whose date of birth lies in 1999-01-01′ AND ‘2001-12-31

Explanation: In the above Query, we fetch all the data of those employees whose date of birth lies in the ‘1999-01-01‘ AND ‘2001-12-31‘ ranges. Here it means Those Employee Details will be displayed if their Date of birth is ‘1999-01-01‘ and ‘2001-12-31‘ or in between the range. As in the output All the employee’s details are displayed whose date of birth lies in the range ‘1999-01-01‘ and ‘2001-12-31‘.

Conclusion

The BETWEEN Operator in SQL Server is a powerful tool for simplifying range-based queries. Understanding its usage and incorporating it into your SQL repertoire enhances your ability to retrieve precisely the data you need. By following the step-by-step guide outlined above, you can confidently employ the BETWEEN Operator in various scenarios within SQL Server.