What is BETWEEN Logical Operator in SQL Server - SQL Server / TSQL Tutorial Part 124

What is BETWEEN Logical Operator : 


BETWEEN returns TRUE if the operand is within range. BETWEEN logical operator is used when we want to return the row if operand is within range.

Scenario: 

Let's say that we have dbo.Cutomer table and one of the column is Age. If we would like to return all the records from dbo.Customer table where age between 33 and 60. What Logical Operator we can use?

Solution:

We can use BETWEEN logical operator to test ranges. If True then row will be returned otherwise not.

Create table dbo.Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2),
  Age tinyint)
GO
insert into dbo.Customer
Values (
1,'Raza','M','PK',20),
(2,'Rita','John','US',12),
(3,'Sukhi','Singh',Null,25),
(4,'James','Smith','CA',60),
(5,'Robert','Ladson','US',54),
(6,'Alice','John','US',87),
(7,'Raza','M','US',33),
(8,'Dita','M','US',15),
(9,'Adita','M','US',29)




BETWEEN returns TRUE if the value of test_expression is greater than or equal to the value of begin_expression and less than or equal to the value of end_expression.

We can use below query to return all the rows if age is between 33 and 60.

Select * From dbo.Customer
where Age between 33 and 60

How to use BETWEEN Logical Operator to check range and return rows - SQL Server / TSQL Tutorial


We can also use NOT BETWEEN that will retrun TRUE if the value of test expression is less than the value of begin expression or greater than the value of end expression.
If we want to return all the records where Age is less than 33 or greater than 60 we can use NOT BETWEEN as shown below.

Select * From dbo.Customer
where Age NOT between 33 and 60
How to use NOT BETWEEN in SQL Server Query - SQL Server / TSQL Tutorial