MySQL BETWEEN OPERATOR

MySQL BETWEEN operator is used to filter records by checking if a value falls within a specific range. It is commonly used in the WHERE clause to select rows where a column's value lies between two specified values.

Syntax

SELECT column1,column2,column3... 
FROM table_name where column_name BETWEEN value1 AND value2;

Demo Database:

Table Name : products

product_id	product_name	product_code	price	unit	category_id
1	The Psychology	7550	250.00	Piece	1
2	Stories for Children	3098	350.00	Piece	1
3	Harry Potter	8472	275.00	Piece	1
4	Tecno Spark	9468	8000.00	Nos	2
5	Samsung Galaxy	7188	10000.00	Nos	2
6	Panasonic Eluga	3433	7000.00	Nos	2
7	Lenovo IdeaPad	6708	45000.00	Nos	3
8	ASUS Celeron Dual Core	3583	43000.00	Nos	3

MySQL BETWEEN Operators IN Examples

1) The statement selects all products with a price range between 100 to 1000.

Example

SELECT * FROM products where price BETWEEN 100 AND 1000;

Try it Yourself

2) The statement selects all products with a price Not Between 100 to 1000.

Example

SELECT * FROM products where price NOT BETWEEN 100 AND 1000;

Try it Yourself

3) The given SQL statement retrieves all products from the products table with a price range between 100 to 1000 and where unit IN Piece.

Example

SELECT * FROM products 
where price BETWEEN 100 AND 1000 AND unit IN ('Piece');

Try it Yourself

Demo Database:

Table Name : orders

order_no	order_date	customer_id	total_amount	status
2021001	2021-05-13	1	1545.00	Delivered
2021002	2021-05-13	2	18000.00	Delivered
2021003	2021-05-14	3	10000.00	Pending
2021004	2021-05-15	4	1450.00	Delivered
2021005	2021-05-16	5	4680.00	Pending
2021006	2021-05-17	6	10000.00	Pending
2021007	2021-05-18	7	5475.00	Delivered
2021008	2021-05-19	8	4337.00	Pending

BETWEEN DATES Examples

1) The statement selects all orders from the date range between '2021-05-21' AND '2021-05-31'.

Example

SELECT * FROM orders where order_date BETWEEN '2021-05-21' AND '2021-05-31';

Try it Yourself