MySQL Between Operator
In order to select data that is within a range of values, the BETWEEN operator is used. The BETWEEN operator allows the selection of rows that contain values with a specified lower and upper limit. The range coded after the word BETWEEN is inclusive.
The Lower value must be coded first. The two values in between the range must be linked with the keyword AND. The BETWEEN operator can be used with both character and numeric data types. However, the data types cannot be mixed i.e. the lower value of a range of values from a character column and other from a numeric column.
Syntax:
|
1 2 3 |
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; |
Ex: MySQL Table tblemployee
| id | Emp_Name | Emp_Code | Emp_JoiningDate |
| 1 | Anuj Kumar | 10806121 | 2018-05-01 |
| 2 | Sanjeev Kumar | 1908763 | 2018-08-10 |
| 3 | Jonh Doe | 10899781 | 2017-01-01 |
Ex1:
|
1 |
SELECT Emp_Name,Emp_Code from tblemployee WHERE id BETWEEN 2 and 3; |
OUTPUT:
| id | Emp_Name | Emp_Code | Emp_JoiningDate |
| 2 | Sanjeev Kumar | 1908763 | 2018-08-10 |
| 3 | Jonh Doe | 10899781 | 2017-01-01 |
Ex2 :
|
1 |
SELECT Emp_Name,Emp_Code from tblemployee WHERE Emp_JoiningDate BETWEEN '2016-12-01' and '2018-07-01'; |
Output :
| id | Emp_Name | Emp_Code | Emp_JoiningDate |
| 1 | Anuj Kumar | 10806121 | 2018-05-01 |
| 3 | Jonh Doe | 10899781 | 2017-01-01 |
To find those employees whose id not between 1 and 2 , you can use BETWEEN operator with the NOT Operators as follows :
|
1 |
SELECT Emp_Name,Emp_Code from tblemployee WHERE id NOT BETWEEN 2 and 3; |
Output:
| id | Emp_Name | Emp_Code | Emp_JoiningDate |
| 1 | Anuj Kumar | 10806121 | 2018-05-01 |
