Usage of the BETWEEN clause in MySQL

Usage of the BETWEEN clause in MySQL

The IN clause can be used to replace the combined conditions of "greater than or equal to" and "less than or equal.

The EMPLOYEE_TBL table considered by the BETWEEN clause has the following records:

mysql> SELECT * FROM employee_tbl;+------+------+------------+--------------------+| id  | name | work_date | daily_typing_pages |+------+------+------------+--------------------+|  1 | John | 2007-01-24 |        250 ||  2 | Ram | 2007-05-27 |        220 ||  3 | Jack | 2007-05-06 |        170 ||  3 | Jack | 2007-04-06 |        100 ||  4 | Jill | 2007-04-06 |        220 ||  5 | Zara | 2007-06-06 |        300 ||  5 | Zara | 2007-02-06 |        350 |+------+------+------------+--------------------+7 rows in set (0.00 sec)

Now, assume that the daily_typing_pages value is greater than 170, equal to and less than 300, based on the above table. The following conditions can be used:> = and <=

mysql>SELECT * FROM employee_tbl   ->WHERE daily_typing_pages >= 170 AND  ->daily_typing_pages <= 300;+------+------+------------+--------------------+| id  | name | work_date | daily_typing_pages |+------+------+------------+--------------------+|  1 | John | 2007-01-24 |        250 ||  2 | Ram | 2007-05-27 |        220 ||  3 | Jack | 2007-05-06 |        170 ||  4 | Jill | 2007-04-06 |        220 ||  5 | Zara | 2007-06-06 |        300 |+------+------+------------+--------------------+5 rows in set (0.03 sec)

You can also use the BETWEEN clause as follows:

mysql> SELECT * FROM employee_tbl   -> WHERE daily_typing_pages BETWEEN 170 AND 300; +------+------+------------+--------------------+| id  | name | work_date | daily_typing_pages |+------+------+------------+--------------------+|  1 | John | 2007-01-24 |        250 ||  2 | Ram | 2007-05-27 |        220 ||  3 | Jack | 2007-05-06 |        170 ||  4 | Jill | 2007-04-06 |        220 ||  5 | Zara | 2007-06-06 |        300 |+------+------+------------+--------------------+5 rows in set (0.03 sec)