SQL - BETWEEN keyword used to check value in a range

<<Previous - NOT operator

Next - Like Operator>>

• SQL BETWEEN operator is used to check whether a data value lies in the given range of values.
• This checking requires three expressions in which the first expression indicates the value to be checked, second and third expressions indicate the two specified test range values to be checked.

Note:

The first expression  is usually  the column-name for our easy understanding.
The value can be any valid data type such as number, text, and date.

Syntax

SELECT
Column-name1, column-name2, column-name3.....Column-nameN
FROM Table-name
WHREE column-name BETWEEN value1 AND value2 ;

Example

Below is the demo Staff_table from the Collegedb database.

+-------+-----------+-------+--------+
| ID_No | Name      | Dept  | Salary |
+-------+-----------+-------+--------+
|   101 | Madhan    | IT    |  20000 |
|   102 | Kamini    | IT    |  25000 |
|   103 | Senthil   | CSE   |  30000 |
|   104 | Vani      | CSE   |  30000 |
|   105 | Aadhi     | MECH  |  35000 |
|   106 | Arun      | CIVIL |  35000 |
|   107 | Vasanthi  | CIVIL |  28000 |
|   108 | Balan     | CIVIL |  27500 |
|   109 | Devid     | MECH  |  23500 |
|   110 | Aruna     | ADMIN |  15000 |
|   111 | Boomi     | ADMIN |  17000 |
|   112 | Kathirvel | ADMIN |  17550 |
|   113 | Kathir    | ADMIN |  22000 |
+-------+-----------+-------+--------+

Following is the query statement to select the staff members whose salary lies between 20000 and 30000.

mysql> SELECT ID_No,Name,Dept,Salary FROM Staff_table WHERE Salary BETWEEN 20000 AND 30000;
+-------+----------+-------+--------+
| ID_No | Name     | Dept  | Salary |
+-------+----------+-------+--------+
|   101 | Madhan   | IT    |  20000 |
|   102 | Kamini   | IT    |  25000 |
|   103 | Senthil  | CSE   |  30000 |
|   104 | Vani     | CSE   |  30000 |
|   107 | Vasanthi | CIVIL |  28000 |
|   108 | Balan    | CIVIL |  27500 |
|   109 | Devid    | MECH  |  23500 |
|   113 | Kathir   | ADMIN |  22000 |
+-------+----------+-------+--------+
8 rows in set (0.13 sec)

NOT BETWEEN

Following query statement selects the faculty members who are not in the specified range.

mysql> SELECT ID_No,Name,Dept,Salary FROM Staff_table WHERE Salary NOT BETWEEN 20000 AND 30000;
+-------+-----------+-------+--------+
| ID_No | Name      | Dept  | Salary |
+-------+-----------+-------+--------+
|   105 | Aadhi     | MECH  |  35000 |
|   106 | Arun      | CIVIL |  35000 |
|   110 | Aruna     | ADMIN |  15000 |
|   111 | Boomi     | ADMIN |  17000 |
|   112 | Kathirvel | ADMIN |  17550 |
+-------+-----------+-------+--------+
5 rows in set (0.00 sec)

Another Example of BETWEEN

Following query statement selects the faculty members between Arun and devid .

mysql> SELECT ID_No,Name,Dept,Salary FROM Staff_table WHERE Name BETWEEN 'Arun' AND 'Devid' ORDER BY Name;
+-------+-------+-------+--------+
| ID_No | Name  | Dept  | Salary |
+-------+-------+-------+--------+
|   106 | Arun  | CIVIL |  35000 |
|   110 | Aruna | ADMIN |  15000 |
|   108 | Balan | CIVIL |  27500 |
|   111 | Boomi | ADMIN |  17000 |
|   109 | Devid | MECH  |  23500 |
+-------+-------+-------+--------+
5 rows in set (0.17 sec)

Another Example of BETWEEN

Following query statement selects the faculty members who are not between Arun and devid .

mysql> SELECT ID_No,Name,Dept,Salary FROM Staff_table WHERE Name  NOT BETWEEN 'Arun' AND 'Devid' ORDER BY Name;

+-------+-----------+-------+--------+
| ID_No | Name      | Dept  | Salary |
+-------+-----------+-------+--------+
|   105 | Aadhi     | MECH  |  35000 |
|   102 | Kamini    | IT    |  25000 |
|   113 | Kathir    | ADMIN |  22000 |
|   112 | Kathirvel | ADMIN |  17550 |
|   101 | Madhan    | IT    |  20000 |
|   103 | Senthil   | CSE   |  30000 |
|   104 | Vani      | CSE   |  30000 |
|   107 | Vasanthi  | CIVIL |  28000 |
+-------+-----------+-------+--------+
8 rows in set (0.09 sec)

<<Previous - NOT operator

Next - Like Operator>>