IN and BETWEEN

This section of the tutorial MySQL looks at the In and BETWEEN operators.

To list employees who are Web Designers and System Administrators, we use a SELECT statement as

SELECT f_name, l_name, title from
    -> employee_data where
    -> title = 'Web Designer' OR
    -> title = 'System Administrator';

+---------+--------+----------------------+
| f_name  | l_name | title                |
+---------+--------+----------------------+
| Anamika | Pandit | Web Designer         |
| Mary    | Anchor | Web Designer         |
| Roger   | Lewis  | System Administrator |
| Danny   | Gibson | System Administrator |
+---------+--------+----------------------+
4 rows in set (0.01 sec)

SQL also provides an easier method with IN. Its usage is quite simple.

SELECT f_name, l_name, title from
    -> employee_data where title
    -> IN ('Web Designer', 'System Administrator');

+---------+--------+----------------------+
| f_name  | l_name | title                |
+---------+--------+----------------------+
| Anamika | Pandit | Web Designer         |
| Mary    | Anchor | Web Designer         |
| Roger   | Lewis  | System Administrator |
| Danny   | Gibson | System Administrator |
+---------+--------+----------------------+
4 rows in set (0.00 sec)

Suffixing NOT to IN will display data that is NOT found IN the condition. The following lists employees who hold titles other than Programmer and Marketing Executive.

SELECT f_name, l_name, title from
    -> employee_data where title NOT IN
    -> ('Programmer', 'Marketing Executive');

+---------+----------+----------------------------+
| f_name  | l_name   | title                      |
+---------+----------+----------------------------+
| Manish  | Sharma   | CEO                        |
| John    | Hagan    | Senior Programmer          |
| Ganesh  | Pillai   | Senior Programmer          |
| Anamika | Pandit   | Web Designer               |
| Mary    | Anchor   | Web Designer               |
| Hassan  | Rajabi   | Multimedia Programmer      |
| Paul    | Simon    | Multimedia Programmer      |
| Arthur  | Hoopla   | Multimedia Programmer      |
| Kim     | Hunter   | Senior Web Designer        |
| Roger   | Lewis    | System Administrator       |
| Danny   | Gibson   | System Administrator       |
| Mike    | Harper   | Senior Marketing Executive |
| Shahida | Ali      | Customer Service Manager   |
| Peter   | Champion | Finance Manager            |
+---------+----------+----------------------------+
14 rows in set (0.00 sec)

BETWEEN is employed to specify integer ranges. Thus instead of age >= 32 AND age <= 40, we can use age BETWEEN 32 and 40.

select f_name, l_name, age from
    -> employee_data where age BETWEEN
    -> 32 AND 40;

+---------+------------+------+
| f_name  | l_name     | age  |
+---------+------------+------+
| John    | Hagan      |   32 |
| Ganesh  | Pillai     |   32 |
| John    | MacFarland |   34 |
| Alok    | Nanda      |   32 |
| Hassan  | Rajabi     |   33 |
| Arthur  | Hoopla     |   32 |
| Kim     | Hunter     |   32 |
| Roger   | Lewis      |   35 |
| Danny   | Gibson     |   34 |
| Mike    | Harper     |   36 |
| Shahida | Ali        |   32 |
| Peter   | Champion   |   36 |
+---------+------------+------+
12 rows in set (0.00 sec)

You can use NOT with BETWEEN as in the following statement that lists employees who draw salaries less than $90000 and more than $150000.

select f_name, l_name, salary
    -> from employee_data where salary
    -> NOT BETWEEN
    -> 90000 AND 150000;

+---------+------------+--------+
| f_name  | l_name     | salary |
+---------+------------+--------+
| Manish  | Sharma     | 200000 |
| Mary    | Anchor     |  85000 |
| Fred    | Kruger     |  75000 |
| John    | MacFarland |  80000 |
| Edward  | Sakamuro   |  75000 |
| Alok    | Nanda      |  70000 |
| Paul    | Simon      |  85000 |
| Arthur  | Hoopla     |  75000 |
| Hal     | Simlai     |  70000 |
| Joseph  | Irvine     |  72000 |
| Shahida | Ali        |  70000 |
+---------+------------+--------+
11 rows in set (0.00 sec)