MySQL Between AND Clause


MySQL Between Clause is used with WHERE clause to extract records that fall in the range defined with between clause. Both values of the range are included in the result.
The general syntax of BETWEEN clause in the select statement.
Syntax

SELECT column1,column2,........
WHERE columnName BETWEEN value11 AND value2.

Sample Database Table to demonstrate the use of BETWEEN AND Clause

mysql> select * from student;
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | comm   | 89.00 | A     | 9871812332  | M      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 103   | mohit   | bio    | 46.50 | C     | 0000000001  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 96787466551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
+-------+---------+--------+-------+-------+-------------+--------+
11 rows in set (0.00 sec)

Example Queries using BETWEEN AND Clause

Suppose you want to find out the records of all those students whose marks are between 60.0 and 80.0.

mysql> select * from student
    -> where marks between 60.0 and 80.0;
+-------+--------+--------+-------+-------+-------------+--------+
| admno | name   | stream | marks | grade | phone       | gender |
+-------+--------+--------+-------+-------+-------------+--------+
| 104   | mannat | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj | sci    | 79.50 | A     | 96787766551 | M      |
| 109   | mohit  | comm   | 64.80 | B     | 9673784651  | M      |
+-------+--------+--------+-------+-------+-------------+--------+
4 rows in set (0.00 sec)

Since we are dealing with numeric data only the same can be achieved using the AND operator but this is useful only in few situations only.

mysql> select * from student
    -> where marks>=60.0 AND marks<=80.0;
+-------+--------+--------+-------+-------+-------------+--------+
| admno | name   | stream | marks | grade | phone       | gender |
+-------+--------+--------+-------+-------+-------------+--------+
| 104   | mannat | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj | sci    | 79.50 | A     | 96787766551 | M      |
| 109   | mohit  | comm   | 64.80 | B     | 9673784651  | M      |
+-------+--------+--------+-------+-------+-------------+--------+
4 rows in set (0.00 sec)

BETWEEN and Clause include all the values that fall between these two ranges of values.

mysql> select * from student
    -> where grade between 'A' and 'B';
+-------+---------+--------+-------+-------+-------------+--------+
| admno | name    | stream | marks | grade | phone       | gender |
+-------+---------+--------+-------+-------+-------------+--------+
| 101   | rakesh  | comm   | 89.00 | A     | 9871812332  | M      |
| 102   | udit    | sci    | 56.50 | B     | 0000000000  | M      |
| 104   | mannat  | human  | 67.50 | A     | 99887766551 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 96887766551 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 96787766551 | M      |
| 108   | pratham | comm   | 84.80 | A     | 9678784651  | M      |
| 109   | mohit   | comm   | 64.80 | B     | 9673784651  | M      |
| 110   | urshavi | bio    | 96.80 | A     | 92673784651 | M      |
| 111   | punya   | comm   | 86.56 | B     | 0000000004  | F      |
+-------+---------+--------+-------+-------+-------------+--------+
9 rows in set (0.00 sec)

IN the last example we compared character values, when character values are passed then MySQL automatically uses ASCII code to generate a range of value internally.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.