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
SQL

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;
SQL

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;
SQL

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 ;
SQL

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';
SQL

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);
SQL

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
SQL

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
SQL

Output 

Summary

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


View All Comments