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.
