SQL BETWEEN Statement

In this article, you will learn how to use a SQL Between statement in SQL

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,
 
Syntax
  1. SELECT * FROM EmployeeDetail        
  2. WHERE EmpId BETWEEN 1 AND 4   
Example

 

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.
 
Syntax 
  1. SELECT principal_id, name         
  2. FROM sys.database_principals        
  3. WHERE type = 'R'  
  4. SELECT principal_id, name         
  5. FROM sys.database_principals        
  6. WHERE  
  7. AND principal_id BETWEEN  16385 AND 16390;        
  8. GO    
Example
 
 

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
 
Syntax
  1. SELECT EmpId,EmpName       
  2. FROM EmployeeDetail e         
  3. JOIN EmployeeDetails ep         
  4.     ON e.EmpId = ep.EmployeeID        
  5. WHERE ep.EmployeeID > 4 >AND ep.EmployeeID < 30        
  6. ORDER BY ep.EmployeeName;        
  7. GO    
Example
 
 

Using not Between values

 
The following example finds all rows outside a specified range of 27 through 30.
 
Syntax 
  1. SELECT EmpId,EmpName         
  2. FROM EmployeeDetail e           
  3. JOIN EmployeeDetails ep           
  4.     ON e.EmpId = ep.EmployeeID          
  5. WHERE e.EmpId NOT BETWEEN 27 AND 30          
  6. ORDER BY EmpAddress ;          
  7. GO    
Example
 
 

Using Between with datetime values

 
The following SQL statement retrieves rows in which datetime values are Between '2019/04/11' and '2019/07/11' inclusive
 
Syntax
  1. SELECT OrderName, orderAddress, OrderDate           
  2. FROM OrderDetails          
  3. WHERE  OrderDate BETWEEN '20190411'  class="op">AND '20190711';  
Example 
 
 

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 1,2, or 3
 
Syntax
  1. SELECTFROM OrderDetails          
  2. WHERE  OrderId BETWEEN 4 AND 5        
  3. AND NOT  OrderId IN (1,2,3);     
Example
 
 

Using Between text values

 
The following example selects orderDetails with a orderName Between apple and banana.
 
Syntax  
  1. SELECT *   
  2. WHERE OrderName   BETWEEN 'Apple' AND 'Banna'      
  3. ORDER BY orderAddress   
Example 
 
 

Using not Between text values

 
The following SQL statement selects all products with an orderName BETWEEN apple and banana.
 
Syntax 
  1. SELECT * FROM OrderDetails          
  2. WHERE OrderName NOT BETWEEN 'Apple' AND 'Banna'        
  3. ORDER BY orderAddress  
Example
 
 

Summary

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