The SQL BETWEEN Operator displays the records (or rows) whose values are between the given values. It allows specifying the lower and upper boundary of a range. The BETWEEN Operator checks whether the column value is within this range (lower and upper boundary), and if it is, include the row in the result set.
In the Server, we use this BETWEEN Operator within the WHERE clause to filter the result set returned by the SELECT statement. This article will explain the SQL Server BETWEEN and Not Between operators with multiple examples of Numbers, strings, and Date columns.
SQL Server Between Operator example: If you want to find Sales from 18 May 2015 to 19 June 2015. Or, If you want the Amazon website to display the products whose price range from 1000 to 2500, then internally, we have to use this one.
SQL Server BETWEEN Operator Syntax
The basic syntax of the BETWEEN operator to get the records within a particular range is as follows:
SELECT Column(s) FROM [Source] WHERE [Column Name] BETWEEN Value1 AND Value2
In the above syntax, column(s) means the required column names from the Source or table you want to display in the result set. The [Column Name] represents the column you want to filter the above result set. Next, Value1 and Value2 represent the lower and upper boundaries of the range. Here, the SQL BETWEEN operator checks whether the row value of the [Column Name] is within the Value1 and Value2. If True, the record will be added to the result set.
NOTE: The data type of the [Column Name] must match the data type of the range values (Value1 and Value2) to avoid unexpected results.
We can also write the above BETWEEN Operator statement using the Comparison Operators. As you can see, Value1 and Value2 are included within the query result by adding >= and <=operators.
SELECT Column(s) FROM [Source] WHERE [Column Name] >= Value1 AND [Column Name] <= Value2
SQL BETWEEN Operator Examples
The BETWEEN operator result set includes the range’s lower (Value1) and upper (Value2) boundaries. Suppose a record of the [Column Name] equals Value1 or Value2. In that case, the BETWEEN operator will consider it within the range and include it in the result. For example, Value1 = 1 and Value2 = 5; the result includes 1 and 5.
For this BETWEEN operator and NOT example, we will use the below-shown data.
SQL BETWEEN Operator On Numbers or integers
You can use this operator on numbers or integer columns to filter and extract the records within a numeric range. Sometimes, we must find the sales or orders within a specific range. In another case, we need the customers to fall in a specific range, employees between a and b. To handle these situations, we can use the BETWEEN operator.
The following SQL BETWEEN operator query finds all the employees in the Employee table whose [Yearly Income] is between 50000 and 70000.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [YearlyIncome] BETWEEN 50000 AND 70000
Floating-point or Decimal Values Example
The below example returns all the employees whose sales are between 600.00 and 3500.50.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE Sales BETWEEN 600.00 AND 3500.50
SQL BETWEEN Operator On Strings
It also allows you to use the BETWEEN operator on string columns to filter the result set using the text data. For example, this operator is beneficial to extract the customers or products within a specific alphabetic range.
The following between operator query will find all the existing employees whose Last Name is in the middle of Carlson and Ruiz. Here, the operator follows the English alphabet order and looks for the first letter of the Last Name columns. If the first letter is between C and R, that particular row will add to the result set.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [LastName] BETWEEN 'Carlson' AND 'Ruiz'
ORDER BY [LastName]
We can use a single character in the Server BETWEEN operators instead of writing the complete name. For instance, by placing the first letter, you can write the above query without mentioning the complete names Carlson and Ruiz.
The below example checks whether the employee’s First Name starts with B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, and R returns the records that satisfy the condition.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [FirstName] BETWEEN 'B' AND 'R'
ORDER BY [FirstName]
TIP: You can also use wildcards instead of a single character in WHERE Clause—for instance, C% and R%.
String Length Example
Instead of working on string text as the filtering condition, you can also use the combination of string functions and the SQL BETWEEN operator. For instance, if you want to retrieve the product names or descriptions of a particular length, you can use the LEN() function.
The following query returns all the employees whose length (total characters) of the Education column values is between 6 and 14.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE LEN([Education]) BETWEEN 6 AND 14
SQL BETWEEN Operator with Dates and Times
The real power of the Between operator is its capability to work with individual Dates and Times and the combination of Dates and times. The most common scenario is finding the sales between the Christmas period by placing the start and end dates. The other example is analyzing events within a particular time frame.
The following query returns all the employees whose Hire Date is between ‘2009-08-12’ and ‘2013-01-15’.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [HireDate] BETWEEN '2009-08-12' AND '2013-01-15'
In the above example, we used the exact replication of the Date format. However, you can also try the standard format. The following query returns all the employees whose Hire Date is between ‘20090101’ and ‘20131231’, and the format is YYYYMMDD.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE [HireDate] BETWEEN '20090101' AND '20131231'
SQL Server NOT BETWEEN Operator
We can also use the NOT Keyword and the BETWEEN operator to exclude the specific range of records from the result set. The syntax of the NOT BETWEEN operator is as follows.
SELECT column(s) FROM Table WHERE col_name NOT BETWEEN Value1 AND Value2
To understand it better, let me show you a few examples of the NOT BETWEEN operator.
Example of Numbers
The following SQL operator query will find all the employees in a table whose [Yearly Income] is not between 60000 and 90000. Removing the NOT keyword from the query will return the employees with income between the 60000 and 90000 range.
SELECT * FROM [Employee] WHERE [YearlyIncome] NOT BETWEEN 60000 AND 90000
String Column Example
The below query returns the employees whose Last Name is not between ‘C’ AND ‘R’ English alphabet characters.
SELECT * FROM [Employee] WHERE [LastName] NOT BETWEEN 'C' AND 'R'
Date and Time Example
The below query uses the YEAR() function to extract year numbers from the HireDate column. Next, the NOT BETWEEN operator will return all the employees whose year of hire date does not fall between 2006 and 2010. Removing NOT keywords returns employees hired from 2006 to 2010, regardless of month and day.
SELECT * FROM [Employee] WHERE YEAR([HireDate]) NOT BETWEEN 2006 AND 2010
Combining SQL BETWEEN Operator with Other Operators
To create complex conditions or apply precise filtering, you have to use this BETWEEN operator with the combination of one or more operators. For example, we need the Holiday season sales of a particular product color. In that case, you have to use the Holiday season start and end dates inside the between operator and use logical AND to join another condition. Next, the equal to the operator (product = ‘Red’) will further filter the products belonging to the Red color.
The below query returns all the employees hired from 2009 to 2014, and their sales value should be more than 1000.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE YEAR([HireDate]) BETWEEN '2009' AND '2014'
AND Sales > 1000
Suppose you replace the AND with the OR operator. In that case, it returns employees if they hire between 2009 and 2014 or if their sales value is greater than 10000. If either condition is true, it adds the record to the result set.
SQL BETWEEN operator and IN Operator
The BETWEEN operator returns records within a range, and the IN operator returns the records belonging to multiple given values. So, combining Between and IN operators gives more flexibility to filter data in a complex way, and the server allows you to do so.
Suppose the table has 100 products with sales happening every day. And you want to see a few products (Chairs, Tables, Sofas) and Sales information in a particular time frame (2018 to 2023). In such a case, you can use the YEAR(Date) to get the year, and the BETWEEN operator extracts product sales from 2018 to 2023. Next, use the AND operator to join the following condition,i.e., the IN operator with (Chairs, Tables, and Sofas) to bring only these products.
The below query returns all the employees whose Occupation is either Management or Professional, and their sales must be between 500.00 and 3500.00.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE Sales BETWEEN 500.00 AND 3500.00
AND Occupation IN('Management', 'Professional')
NOT BETWEEN and NOT IN Operators
The NOT Between operator excludes the values within a given range and the NOT IN operator filters values that do not match any given values within () parentheses.
The below query returns all the employees whose Education is neither Bachelor’s nor High School, and their sales should not be in the range of 500.00 and 3500.00.
SELECT [EmpID],[FirstName],[LastName],[Education]
,[Occupation],[YearlyIncome],[Sales],[HireDate]
FROM [Employee]
WHERE Sales NOT BETWEEN 500.00 AND 3500.00
AND Occupation NOT IN('Bachelors', 'High School')
Comments are closed.