Skip to content
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.


SELECT * FROM table_name


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


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'



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


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

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window


Leave a Reply

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