BETWEEN SQL Operator Overview | How to Use them with Example

BETWEEN SQL operator is a Logical operator in SQL, which allows you to select values that are within a specific range. the values can be text, numbers, or dates. furthermore, it has to be paired with the AND operator.
The SQL BETWEEN operator will be showing a different result in different databases. therefore, the operator always returns TRUE values when the search value present within the range otherwise it’s returns FALSE. The results are NULL if any of the range values are NULL.

Note: BETWEEN SQL operator is inclusive: the begin and end values are included.
WHERE BETWEEN returns the values that fall within a given Specific range.
WHERE BETWEEN is a shorthand for >= AND <=

BETWEEN SQL General Syntax:

SELECT [ColumnName,.. | Expression1 ] FROM [TableName] WHERE Expression2 [NOT] BETWEEN starting_value AND ending_value;

Parameters:

Name	Description
ColumnName	The Name of the column of the table
Expression1	The Expression made up of a single, variable, scalar function, column name or constant, it can also be pieces of the SQL query, which compare values against to the other values or perform arithmetic calculations
TableName	The Name of the table
WHERE Expression2	Compares the scalar expression, like as a column, to the range of values bounded by starting_value and ending_value
starting_value, ending_value	value from and value to

Between SQL:- Sample Demo Table

There are given below demo table name "orders"

Product_Code	Product_Name	Delever_city	Quantity	Vendor_ID	Price
P0011	Automatic washing machine	Chennai	10	V3588	25
P0012	Front loading washing Machine	Mumbai	12	V2512	30
P0013	stand mixers	Delhi	8	V0450	15
P0014	Convection Microwave Oven	Lucknow	15	V1710	20
P0015	Solo Microwave	New York	14	V1685	15
P0016	Electric iron Press	Orlando	30	V4344	10
P0017	Electric Chimney	Los Angeles	25	V4418	40
P0018	Modular kitchen Chimney	Honolulu	13	V2414	35
P0019	Air purifier	Shanghai	45	V3765	55
P0020	Dry Vacuum Cleaner	Beijing	25	V1848	35
P0021	Mini Vacum Cleaner	Washington	30	V0228	18
P0022	Undercounter Dishwasher	Shenzhen	14	V9919	12
P0023	Bajaj Food processor	Zhuhai	26	V1114	16
P0024	Bajaj Sandwich makers	Jaipur	22	V0043	11
P0025	Bajaj Electric Cooker	Noida	45	V0409	26

BETWEEN Example

The following SQL statement selects all products with a Quantity BETWEEN 10 and 15

SQL Code:

SELECT * FROM orders 
WHERE Quantity BETWEEN 10 AND 15;

1 2	SELECT * FROM orders WHERE Quantity BETWEEN 10 AND 15;

Product_Code	Product_Name	Delever_city	Quantity	Vendor_ID	Price
P0011	Automatic washing machine	Chennai	10	V3588	25
P0012	Front loading washing Machine	Mumbai	12	V2512	30
P0014	Convection Microwave Oven	Lucknow	15	V1710	20
P0015	Solo Microwave	New York	14	V1685	15
P0018	Modular kitchen Chimney	Honolulu	13	V2414	35
P0022	Undercounter Dishwasher	Shenzhen	14	V9919	12

NOT BETWEEN Example

To display the orders outside the range of the previous example, therefore, here we use NOT BETWEEN:

SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 15;

1 2	SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 15;

not between sql output

BETWEEN with IN Example

The following SQL statement selects all orders with a price BETWEEN 15 and 40.
In addition; do not show orders with a Quantity of 8,10:

SELECT * FROM orders
WHERE Price BETWEEN 15 AND 40
AND Quantity NOT IN (8,10);

SELECT * FROM orders

WHERE Price BETWEEN 15 AND 40

AND Quantity NOT IN (8,10);

BETWEEN SQL with IN out

BETWEEN Text Values Example

The following SQL statement selects all orders with a ProductName BETWEEN Convection Microwave Oven and Undercounter Dishwasher:

SELECT * FROM orders
WHERE Product_Name BETWEEN 'Convection Microwave Oven' AND 'Undercounter Dishwasher'
ORDER BY Product_Name;

SELECT * FROM orders

WHERE Product_Name BETWEEN 'Convection Microwave Oven' AND 'Undercounter Dishwasher'

ORDER BY Product_Name;

BETWEEN SQL Text Values
The following SQL statement selects all orders with a ProductName BETWEEN Electric Chimney and Bajaj Electric Cooker:

SELECT * FROM orders
WHERE Product_Name BETWEEN 'Electric Chimney' AND 'Undercounter Bajaj Electric Cooker'
ORDER BY Product_Name;

SELECT * FROM orders

WHERE Product_Name BETWEEN 'Electric Chimney' AND 'Undercounter Bajaj Electric Cooker'

ORDER BY Product_Name;

between sql text values result

NOT BETWEEN Text Values Example

The following SQL statement selects all orders with a ProductName NOT BETWEEN Front loading washing Machine and Electric Chimney:

SELECT * FROM orders
WHERE Product_Name NOT BETWEEN 'Front loading washing Machine' AND 'Electric Chimney'
ORDER BY Product_Name;

SELECT * FROM orders

WHERE Product_Name NOT BETWEEN 'Front loading washing Machine' AND 'Electric Chimney'

ORDER BY Product_Name;

NOT BETWEEN Text values output

Demo Database Table

OrderID	CompanyID	CompanyName	OrderDate	DeleverID
11121	101	Altrus Industries LLP	2017-1-10	5
11023	108	Riyansh Industries LLP	2017-1-21	8
11029	111	Microtec enterprises	2018-1-27	17
10188	211	Narayan Industries Pvt. Ltd.	2018-6-11	34
11107	123	Shyam enterprises	2018-9-27	23
12109	131	Balaji Industries PVt. Ltd.	2018-10-29	32
12145	142	Jaypee Group Pvt. Ltd.	2018-12-18	51
12341	211	Omax Electronics enterprises	2018-12-23	28
13421	321	Karma Industries Pvt. Ltd.	2019-1-08	41
11778	301	Kanha Electronics Enterprises	2019-2-11	77
11887	188	Ganesh Enterprises	2019-2-21	87
32122	223	Jonshon Communication enterprise	2019-3-13	31
11412	149	Kamla industries LLP	2019-3-26	26
11418	147	sai Electronics Enterpises	2019-3-29	56
11515	315	Jopling Communication	2019-4-10	71
11616	161	Next Genration Electronics	2019-4-17	44
21154	253	Astha Industries Pvt. Ltd.	2019-4-21	67
31378	312	Nilanchal enterpises	2019-5-09	38
31276	279	Himalaya Electrical Enterpises	2019-5-15	76
32211	221	Tricona industries Pvt. Ltd.	2019-6-27	62
21895	189	Mahaveer Interpries	2019-7-11	47

BETWEEN Dates Example

The following SQL statement selects all orders with an OrderDate BETWEEN ’11-Jun-2018′ and ’29-March-2019′:

SELECT * FROM Dispatch_details
WHERE OrderDate BETWEEN '2018-10-29' AND '2019-04-21';

1 2	SELECT * FROM Dispatch_details WHERE OrderDate BETWEEN '2018-10-29' AND '2019-04-21';

BETWEEN Dates outputs

SELECT *
FROM Dispatch_details
WHERE OrderDate BETWEEN '2018-1-27' AND '2019-2-11'
ORDER BY CompanyID;

SELECT *

FROM Dispatch_details

WHERE OrderDate BETWEEN '2018-1-27' AND '2019-2-11'

ORDER BY CompanyID;

In the following above sql query we will get Orderdate BETWEEN ‘2018-1-27’ AND ‘2019-2-11’ which is ORDER BY ComapnyID.

therefore, we can use the CAST function to convert the value in the desired data format explicitly. lets us see the example in the given below the following query. furthermore, here we use the CAST function to convert a string into a data date type.

SELECT *
FROM Dispatch_details
WHERE OrderDate BETWEEN CAST('20100127' as date) AND CAST('20190211' as date)
ORDER BY OrderDate;

SELECT *

FROM Dispatch_details

WHERE OrderDate BETWEEN CAST('20100127' as date) AND CAST('20190211' as date)

ORDER BY OrderDate;

BETWEEN Date CAST Function output

SQL Between operator with a string

In the given below example, we want to get data from Admission table having BranchCode between A and C. We also need to use a single quote for a string to get inclusive data.

Demo Database Table

#	StudentID	StudentName	BranchCode	AdmissionDate
1	1101	Deepak Srivastava	A	2017-03-05
2	1102	Raj Kumar Verma	A	2017-03-04
3	1103	Schin Mishra	B	2017-03-05
4	1104	Arvind Kumar Singh	A	2017-03-07
5	1105	Priyanshu Mishra	B	2017-03-07
6	1106	Saumya tripathi	A	2017-03-09
7	1107	Aditya Kumar Shukla	A	2017-03-09
8	1108	Nilesh Tiwari	B	2017-03-11
9	1109	Arti gupta	B	2017-03-13
10	1110	Divesh singh	B	2017-03-14
11	1111	Anand Pratap Singh	A	2017-03-15
12	1112	Anoop Kumar Dubey	B	2017-03-16
13	1113	Ankit Kumar Shukla	C	2017-03-19
14	1114	Adarsh pratp Singh	C	2017-03-21
15	1115	Ritu Tappa	C	2017-03-25
16	1116	Utkarsh Shukla	A	2017-03-27
17	1117	Neha Gupta	C	2017-03-30
18	1118	Manish Shukla	B	2017-04-02
19	1119	Anrag Awasthi	A	2017-04-02
20	1120	Aradhya Mittal	A	2017-04-02

SELECT *
FROM Admission
WHERE BranchCode BETWEEN 'A' AND 'C'
ORDER BY BranchCode;

SELECT *

FROM Admission

WHERE BranchCode BETWEEN 'A' AND 'C'

ORDER BY BranchCode;

SQL NOT Between operator with a string

therefore, We might also want to exclude a particular range of data in the output. furthermore, In this case, we can use SQL Between operator with Not statement.
Suppose, we want to get the top 10 records from the Admission table but do not want to include AdmissionDate 2017-03-05 and 2017-03-09
We can specify this condition using Not Between operator.

SELECT top 10 *
FROM Admission
WHERE AdmissionDate Not BETWEEN '2017-03-05' and '2017-03-09'
ORDER BY StudentID;

SELECT top 10 *

FROM Admission

WHERE AdmissionDate Not BETWEEN '2017-03-05' and '2017-03-09'

ORDER BY StudentID;

SQL NOT Between operator with a string
OR

SELECT * FROM Admission
WHERE StudentID  NOT BETWEEN 104 and 108
ORDER BY StudentID;

SELECT * FROM Admission

WHERE StudentID NOT BETWEEN 104 and 108

ORDER BY StudentID;

therefore, the output results comes, StudentID not icluded between 104 to 108, and ORDER BY StudentID.

Conclusion

In this article, We extract data from SQL Server tables along with various conditions, therefore, we have data in large amounts and Between SQL operators which will help to extract a range of data from this bulk or huge data.
The SQL BETWEEN operator is used along with the WHERE clause for providing a Specific range of values. The values can be the text value, numeric value, and date. furthermore, the SQL BETWEEN operator is almost like SQL IN operators which are used in a sequential manner.
The values that are defined as part of the BETWEEN range are inclusive such as the values that are mentioned in the range are included at the start (initial value) and end values.
we explored SQL Between operator along with its use in different cases. therefore, we must be familiar with the function to get a specific range of data. therefore, If you have any questions, feel free to leave them in the comments below.

BETWEEN SQL Operator Overview | How to Use them with Example

BETWEEN SQL General Syntax:

Parameters:

Between SQL:- Sample Demo Table

BETWEEN Example

SQL Code:

NOT BETWEEN Example

BETWEEN with IN Example

BETWEEN Text Values Example

NOT BETWEEN Text Values Example

Demo Database Table

BETWEEN Dates Example

SQL Between operator with a string

Demo Database Table

SQL NOT Between operator with a string

Conclusion

Related Posts

About Author

admin