How to use the BETWEEN operator in SQL
Structured Query Language (SQL) has a multitude of operators that allow for flexible queries. These ensure that the most accurate results are obtained.
The BETWEEN operator allows for the selection of values in a given range. For example, suppose you want data on your teenage customers. To do this, you could easily run an SQL query that uses the BETWEEN operator to fetch only those records in which the age lies between 13 and 19. BETWEEN is inclusive, which means that the beginning and end values are also included in the range.
Syntax
SELECT columnNameFROM tableNameWHERE columnName BETWEEN a AND b;
The beginning and end values can be numbers, text, or dates. Let’s look at an example.
Code
Demo table ‘Inventory’
p_ID | p_name | p_price | s_ID | p_number |
1 | Apple | 12 | 1 | 120 |
2 | Orange | 10 | 1 | 105 |
3 | Banana | 8 | 2 | 90 |
4 | Mango | 15 | 3 | 95 |
5 | Peach | 14 | 1 | 115 |
Run the code below to get products that have a price between 10 and 12 dollars.
CREATE TABLE Inventory (p_ID int,p_name varchar(255),p_price int,s_ID int,p_number int);INSERT INTO InventoryVALUES (1,'Apple',12,1,120);INSERT INTO InventoryVALUES (2,'Orange',10,1,105);INSERT INTO InventoryVALUES (3,'Banana',8,2,90);INSERT INTO InventoryVALUES (4,'Mango',15,3,95);INSERT INTO InventoryVALUES (5,'Peach',14,1,115);SELECT * FROM InventoryWHERE p_price BETWEEN 10 AND 12;