SQL BETWEEN Statement

SQL BETWEEN operator

The SQL Between statement selects values within a given range. The values can be numbers, text, or dates.

It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

The SQL Between condition will return the records where expression is within the range of value1 and value2.

The Between statement is inclusive --  begin and end values are included.

SQL Between operator is almost like SQL IN  operators used in a sequential manner.

The values defined as part of the Between range are inclusive; i.e., the values that are mentioned in the range are included at the start and end values.

Let’s discuss in detail about the Between operator

SELECT * FROM EmployeeDetail        
WHERE Id BETWEEN 1 AND 4   

This select statement is select the data from EmployeeDetail

Output 

Using Between in SQL

The following example returns information about the database roles in a database. The first query returns all the roles, and the second example uses the  Between clause to limit the roles to the specified EmpId values.

SELECT principal_id, name         
FROM sys.database_principals        
WHERE type = 'R'  
SELECT principal_id, name         
FROM sys.database_principals        
WHERE principal_id BETWEEN  16385 AND 16390;        
 

Output 

Using  (>) and (<) instead of Between values

The following example uses greater than (>) and less than (<) operators and, because these operators are not inclusive, returns one rows instead of ten that were returned in the previous example

SELECT Id,EmpName       
FROM EmployeeDetail e         
JOIN EmployeeDetails ep         
    ON e.Id = ep.EmployeeID        
WHERE ep.EmployeeID > 2 and ep.EmployeeID < 4     
ORDER BY ep.EmployeeName; 

Output 

Using not Between values

The following example finds all rows outside a specified range of 27 through 30. 

SELECT Id,EmpName         
FROM EmployeeDetail e           
JOIN EmployeeDetails ep           
    ON e.Id = ep.EmployeeID          
WHERE e.Id NOT BETWEEN 27 AND 30          
ORDER BY EmpAddress ;              

Output 

Using Between with datetime values

The following SQL statement retrieves rows in which datetime values are Between '02/01/2022' and '04/10/2022' inclusive

SELECT OrderName, orderAddress, OrderDate           
FROM OrderDetail          
WHERE  OrderDate BETWEEN '20220201' AND '20221004';  

Output

Using Between with IN values

In operator allows you to easily test if the expression matches any value in the list of values. It can be used to remove the need for multiple OR conditions in SELECT, INSERT, UPDATE or DELETE. You can also use NOT IN to exclude the rows in your list.

The following SQL statement selects all OrderDetails with a orderId Between 4 and 5. In addition do not show pr with a orderId of 2,3, or 4

SELECT* FROM OrderDetails          
WHERE  OrderId BETWEEN 1 AND 4 
AND NOT  OrderId IN (2,3,4);     
  

Output 

Using Between text values

The following example selects orderDetails with a orderName Between apple and banana.

SELECT * from OrderDetails  
WHERE OrderName   BETWEEN 'Apple' AND 'Banna'      
ORDER BY orderAddress   

Output

Using not Between text values

The following SQL statement selects all products with an orderName BETWEEN apple and banana.

SELECT * FROM OrderDetails          
WHERE OrderName NOT BETWEEN 'Apple' AND 'Banna'        
ORDER BY orderAddress  

Output 

Summary

In this article, you learned how to use a SQL Between statement with various options.