BETWEEN operator is used to getting the value within a given range. It is applicable in CRUD (create/read/update/delete) operation.
It is used between numeric range value
It is used between date range value
It is used between string range value
For SELECT Statement
Syntax:-
SELECT column_name(s) FROM table_name WHERE column_name range_value1 BETWEEN value2
Example:- Suppose we have employees table which has 7 records
+----+------------+-----------+----------------+-----------+ | id | first_name | last_name | email | country | +----+------------+-----------+----------------+-----------+ | 1 | John | Tailor | john@abc.com | USA | | 2 | Rom | Tailor | rom@abc.com | USA | | 3 | Andrew | Symonds | andrew@abc.com | Australia | | 4 | Miacle | Tailor | miacle@abc.com | Australia | | 5 | Sachin | Tendulkar | sachin@abc.com | India | | 6 | Virat | Kohli | virat@abc.com | India | | 7 | rohit | NULL | rohit@abc.com | India |
Find the employees details that have id value between 4 to 6.
Query:-
SELECT * FROM employees WHERE id between 4 AND 6;
Output:-
+----+------------+-----------+----------------+-----------+ | id | first_name | last_name | email | country | +----+------------+-----------+----------------+-----------+ | 4 | Miacle | Tailor | miacle@abc.com | Australia | | 5 | Sachin | Tendulkar | sachin@abc.com | India | | 6 | Virat | Kohli | virat@abc.com | India | +----+------------+-----------+----------------+-----------+ 3 rows in set (0.00 sec)