SQL BETWEEN Statement

Introduction

In this chapter, we will how to use a SQL Between statement with various options.

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

SELECT * FROM EmployeeDetail
WHERE EmpId BETWEEN 1 AND 4

Example

Using Between in SQL

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

Syntax

SELECT principal_id, name
FROM sys.database_principals
WHERE type = 'R'
SELECT principal_id, name
FROM sys.database_principals
WHERE
AND principal_id BETWEEN 16385 AND 16390;
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, return one rows instead of ten that were returned in the previous example

Syntax

SELECT EmpId,EmpName
FROM EmployeeDetail e
JOIN EmployeeDetails ep
ON e.EmpId = ep.EmployeeID
WHERE ep.EmployeeID > 4 >AND ep.EmployeeID < 30
ORDER BY ep.EmployeeName;
GO

Example

Using not Between values

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

Syntax

SELECT EmpId,EmpName
FROM EmployeeDetail e
JOIN EmployeeDetails ep
ON e.EmpId = ep.EmployeeID
WHERE e.EmpId NOT BETWEEN 27 AND 30
ORDER BY EmpAddress ;
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

SELECT OrderName, orderAddress, OrderDate
FROM OrderDetails
WHERE OrderDate BETWEEN '20190411' class="op">AND '20190711';

Example

Using Between with IN values

An 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 an orderId Between 4 and 5. In addition do not show pr with an orderId of 1,2, or 3.

Syntax