SQL BETWEEN Operator Query


The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Example

products Table

product_idproduct_nameprice
1Nike Shoes15
2Lenovo A850 Dual SIM Android Mobile Phone10
3ASUS X551CA-SX043D Laptop30
4MSP430G2955 MIXED SIGNAL MICROCONTROLLER40
5Nokia Lumia 530 10
6Axe Denim Deodorant Spray12
7Bilt Premium Executive B5 Notebook Wire Bound18
8ERD Portable Mobile Charger-Power Bank 11000mAh23
9Extra Clear Protector -Screen Guard For LG D315K F70 4G LTE Android9
10HP 32 GB Pen Drive - V239g 13
SELECT * FROM products
WHERE product_price BETWEEN 10 AND 20;
Output
product_idproduct_nameprice
1Nike Shoes15
2Lenovo A850 Dual SIM Android Mobile Phone10
5Nokia Lumia 530 10
6Axe Denim Deodorant Spray12
7Bilt Premium Executive B5 Notebook Wire Bound18
10HP 32 GB Pen Drive - V239g 13

NOT BETWEEN Operator

To display the products outside the range.

Syntax

SELECT column_name(s)
FROM table_name
WHERE column_name NOT BETWEEN value1 AND value2;

Example

product Table

SELECT * FROM products
WHERE product_price NOT BETWEEN 10 AND 20;
Output
product_idproduct_nameprice
3ASUS X551CA-SX043D Laptop30
4MSP430G2955 MIXED SIGNAL MICROCONTROLLER40
8ERD Portable Mobile Charger-Power Bank 11000mAh23
9Extra Clear Protector -Screen Guard For LG D315K F70 4G LTE Android9

BETWEEN Operator with IN

SQL statement selects all products with a price BETWEEN 10 and 20, but products with a category_id of 1,2, or 3 should not be displayed:

Example

products Table

product_idproduct_namepricecategory_id
1Nike Shoes152
2Lenovo A850 Dual SIM Android Mobile Phone103
3ASUS X551CA-SX043D Laptop306
4MSP430G2955 MIXED SIGNAL MICROCONTROLLER401
5Nokia Lumia 530 108
6Axe Denim Deodorant Spray125
7Bilt Premium Executive B5 Notebook Wire Bound181
8ERD Portable Mobile Charger-Power Bank 11000mAh232
9Extra Clear Protector -Screen Guard For LG D315K F70 4G LTE Android93
10HP 32 GB Pen Drive - V239g 132
SELECT * FROM products
WHERE (product_price NOT BETWEEN 10 AND 20)
AND NOT IN category_id (1,2) ;
Output
product_idproduct_namepricecategory_id
2Lenovo A850 Dual SIM Android Mobile Phone103
5Nokia Lumia 530 108
6Axe Denim Deodorant Spray125

OR

SQL statement selects all products with a price BETWEEN 10 and 20, but products with a category_id of 1,2, or 3 should be displayed:

SELECT * FROM products
WHERE (product_price NOT BETWEEN 10 AND 20)
AND IN category_id (1,2) ;
Output
product_idproduct_namepricecategory_id
1Nike Shoes152
7Bilt Premium Executive B5 Notebook Wire Bound181
10HP 32 GB Pen Drive - V239g 132

BETWEEN Operator with Text Value

SQL BETWEEN statement selects all products with a ProductName beginning with any of the letter BETWEEN 'M' and 'O':

Example

products Table

product_idproduct_nameprice
1Nike Shoes15
2Lenovo A850 Dual SIM Android Mobile Phone10
3ASUS X551CA-SX043D Laptop30
4MSP430G2955 MIXED SIGNAL MICROCONTROLLER40
5Nokia Lumia 530 10
6Axe Denim Deodorant Spray12
7Bilt Premium Executive B5 Notebook Wire Bound18
8ERD Portable Mobile Charger-Power Bank 11000mAh23
9Extra Clear Protector -Screen Guard For LG D315K F70 4G LTE Android9
10HP 32 GB Pen Drive - V239g 13
SELECT * FROM products
WHERE product_name BETWEEN 'M' AND 'O';
Output
product_idproduct_nameprice
1Nike Shoes15
4MSP430G2955 MIXED SIGNAL MICROCONTROLLER40
5Nokia Lumia 530 10

NOT BETWEEN Operator with Text

SQL NOT BETWEEN statement selects all products with a ProductName beginning with any of the letter NOT BETWEEN 'M' and 'N':

Example

products Table

product_idproduct_nameprice
1Nike Shoes15
2Lenovo A850 Dual SIM Android Mobile Phone10
3ASUS X551CA-SX043D Laptop30
4MSP430G2955 MIXED SIGNAL MICROCONTROLLER40
5Nokia Lumia 530 10
6Axe Denim Deodorant Spray12
7Bilt Premium Executive B5 Notebook Wire Bound18
8ERD Portable Mobile Charger-Power Bank 11000mAh23
9Extra Clear Protector -Screen Guard For LG D315K F70 4G LTE Android9
10HP 32 GB Pen Drive - V239g 13
SELECT * FROM products
WHERE product_name NOT BETWEEN 'M' AND 'O';
Output
product_idproduct_nameprice
2Lenovo A850 Dual SIM Android Mobile Phone10
3ASUS X551CA-SX043D Laptop30
6Axe Denim Deodorant Spray12
7Bilt Premium Executive B5 Notebook Wire Bound18
8ERD Portable Mobile Charger-Power Bank 11000mAh23
9Extra Clear Protector -Screen Guard For LG D315K F70 4G LTE Android9
10HP 32 GB Pen Drive - V239g 13

BETWEEN Operator with Date

SQL statement selects all orders with an order_date BETWEEN '04-June-2015' and '08-June-2015':

Example

order_idcustomer_idemployee_idorder_date
102412516/4/2015
102913626/5/2015
105014446/8/2015
125115356/9/2015
102216476/10/2015
SELECT * FROM orders
WHERE order_date BETWEEN #06/04/2015# AND #06/07/2015#;
Output
order_idcustomer_idemployee_idorder_date
102412516/4/2015
102913626/5/2015

Share this article on