SQL BETWEEN operator allows you to retrieve records which has value in a range of values.
The syntax of SQL BETWEEN is as follows:
SELECT column_list
FROM table_name
WHERE column BETWEEN lower_value AND upper_value
Be noted that SQL BETWEEN operator gets all records which have column value is lower_value and upper_value.
For example you want to retrieve products which have unit price from 18$ to 19$, you can use SQL BETWEEN operator like the following query:
SELECT productName, unitPrice
FROM products
WHERE unitPrice BETWEEN 18 AND 19
productName unitPrice
---------------- ---------
Chai 18.0000
Chang 19.0000
Steeleye Stout 18.0000
Inlagd Sill 19.0000
Chartreuse verte 18.0000
Boston Crab Meat 18.4000
Lakkalikööri 18.0000
You can also reconstruct SQL BETWEEN by using less than or equal and greater than or equal operators like the following query:
SELECT productName, unitPrice
FROM products
WHERE unitPrice >= 18 AND unitPrice <= 19
To find all products which does not have unit price between that ranges of prices, you can combine SQL NOT operator with SQL BETWEEN operator. Here is the query:
SELECT productName, unitPrice
FROM products
WHERE unitPrice NOT BETWEEN 10 AND 80
productName unitPrice
------------------------------- ---------
Mishi Kobe Niku 97.0000
Konbu 6.0000
Teatime Chocolate Biscuits 9.2000
Sir Rodney's Marmalade 81.0000
Tunnbröd 9.0000
Guaraná Fantástica 4.5000
Thüringer Rostbratwurst 123.7900
Geitost 2.5000
Côte de Blaye 263.5000
Jack's New England Clam Chowder 9.6500
Rogede sild 9.5000
Zaanse koeken 9.5000
Filo Mix 7.0000
Tourtière 7.4500
Rhönbräu Klosterbier 7.7500
In this tutorial you’ve learned how to use SQL BETWEEN operator to find values which are in a range of values. In addition you’ve also learned how to combine SQL NOT operator and SQL BETWEEN operator to find all records which have value is not in a range of values.