Home » SQL Between Operator

SQL Between Operator

SQL Between Operator is a logical operator that selects the records whose values are in between the given values.




SYNTAX

SELECT * FROM table_name

WHERE 

test_expression [ NOT ] BETWEEN begin_expression AND end_expression;

test_expression is the expression or column on which we need to define a range. test_expression must be the same data type as both begin_expression and end_expression.

NOT  is an optional, which specifies that the result of the predicate be negated.

begin_expression is any valid expression. begin_expression must be the same data type as both test_expression and end_expression.

end_expression Is any valid expression. end_expression must be the same data type as both test_expressionand begin_expression.

AND is an operator that indicates test_expression should be within the range indicated by begin_expression and end_expression.

 

  • 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.
  • NOT BETWEEN returns TRUE, if the value of test_expression is less than the value of begin_expression or greater than the value of end_expression.

 

Lets look at an example using BETWEEN in SQL.

First we prepare a sample table named as SalesDetails and then insert some records into table.

 

CREATE TABLE dbo.SalesDetails
(
SalesId INT NOT NULL,
SalesPerson VARCHAR(50),
Product VARCHAR(150),
Quantity INT,
Price NUMERIC(9,2),
[Date] DATE
)

INSERT INTO dbo.SalesDetails
VALUES (1,'Raj Kishor', 'Samsung Tablet', 5, 75000, '12-09-2019'),
(2,'Raj Kishor', 'Samsung Tablet', 3, 41000, '08-12-2019'),
(3,'Raj Kishor', 'Iphone 5S', 2, 36000, '05-01-2020'),
(4,'Suraj Singh', 'Samsung Tablet', 2, 20000, '07-01-2020'),
(5,'Suraj Singh', 'Iphone 7', 2, 55000, '12-24-2019'),
(6,'Suraj Singh', 'Samsung NoteBook', 2, 35000, '01-25-2020'),
(7,'Raj Kishor', 'Samsung Tablet', 6, 85000, '02-11-2020'),
(8,'Mahesh Kumar', 'Iphone 6', 2, 49000, '02-21-2020'),
(9,'Mahesh Kumar', 'Iphone 5S', 3, 45000, '03-03-2020'),
(10,'Mahesh Kumar', 'Iphone 6', 2, 49000, '03-21-2020'),
(11,'Raj Kishor', 'Iphone 6', 3, 65000, '03-25-2020'),
(12,'Raj Kishor', 'Iphone 5S', 2, 36000, '03-27-2020'),
(13,'Mohit Singh', 'Samsung Tablet', 3, 48000, '02-21-2020')

Now we have a SalesDetails table in database as shown below.

 

SELECT * FROM dbo.SalesDetails

 

Using Between Operator with Numeric values

Following statement, returns all records from salesdetails table for quantity value between 3 and 6.

SELECT * FROM dbo.SalesDetails

WHERE Quantity BETWEEN 3 AND 6

ORDER BY Quantity

 

You can see the result, it returns all Sales Records for which Quantity between 3 and 6.

Also you might have noticed that Between operator contains inclusive or exclusive values also.

In the result, you can see that both Quantity 3 and 6 are included in the result of Between.

 

 

Using Between Operator with Date Range

You can use SQL Between operator to get data for a specific date range.

Following statement, returns sales data in the range of 1st Jan 2020 and 30th May 2020.

 

SELECT * FROM dbo.SalesDetails

WHERE Date BETWEEN '2020-01-01' AND '2020-05-30'

ORDER BY Date

 

 

Using NOT Between Operator with NUMERIC

Using Not Between Operator you can exclude records between given range, Suppose you want to see sales records but do not want to see those records for quantity between 3 and 5 then you can use NOT BETWEEN operator as you can see in following statement.

 

SELECT * FROM dbo.SalesDetails

WHERE Quantity NOT BETWEEN 3 AND 5

ORDER BY Quantity

 

You can see, It returns all records from sales table but do no include records for quantity values between 3 and 5.

 

 





SQL Exists Operator








Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.