Tutorial MySQL - 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)

1. List all employees who hold the titles of "Senior Programmer" and "Multimedia Programmer".
2. List all employee names with salaries for employees who draw between $70000 and $90000.
3. What will the following statement display?

   SELECT f_name, l_name, title from
   employee_data where title NOT IN
   ('Programmer', 'Senior Programmer',
   'Multimedia Programmer');
   

4. Here is a more complex statement that combines both BETWEEN and IN. What will it display?

   SELECT f_name, l_name, title, age
   from employee_data where
   title NOT IN
   ('Programmer', 'Senior Programmer',
   'Multimedia Programmer') AND age
   NOT BETWEEN 28 and 32;