Primer – 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)

Assignment:

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;

Possible Answers:

1.mysql> select l_name, f_name, title
-> from employee_data where
-> title IN
-> ('Senior Programmer',
-> 'Multimedia Programmer');

+——–+——–+———————–+
| l_name | f_name | title |
+——–+——–+———————–+
| Hagan | John | Senior Programmer |
| Pillai | Ganesh | Senior Programmer |
| Rajabi | Hassan | Multimedia Programmer |
| Simon | Paul | Multimedia Programmer |
| Hoopla | Arthur | Multimedia Programmer |
+——–+——–+———————–+
5 rows in set (0.00 sec)

2.mysql> select f_name, l_name, salary from
-> employee_data where salary BETWEEN
-> 70000 AND 90000;

+———+————+——–+
| f_name | l_name | salary |
+———+————+——–+
| Anamika | Pandit | 90000 |
| Mary | Anchor | 85000 |
| Fred | Kruger | 75000 |
| John | MacFarland | 80000 |
| Edward | Sakamuro | 75000 |
| Alok | Nanda | 70000 |
| Hassan | Rajabi | 90000 |
| Paul | Simon | 85000 |
| Arthur | Hoopla | 75000 |
| Danny | Gibson | 90000 |
| Monica | Sehgal | 90000 |
| Hal | Simlai | 70000 |
| Joseph | Irvine | 72000 |
| Shahida | Ali | 70000 |
+———+————+——–+
14 rows in set (0.00 sec)

3.The names and titles of all non programmers are displayed.
mysql> SELECT f_name, l_name, title from
-> employee_data where title NOT IN
-> ('Programmer', 'Senior Programmer',
-> 'Multimedia Programmer');

+———+———-+—————————-+
| f_name | l_name | title |
+———+———-+—————————-+
| Manish | Sharma | CEO |
| Anamika | Pandit | Web Designer |
| Mary | Anchor | Web Designer |
| Kim | Hunter | Senior Web Designer |
| Roger | Lewis | System Administrator |
| Danny | Gibson | System Administrator |
| Mike | Harper | Senior Marketing Executive |
| Monica | Sehgal | Marketing Executive |
| Hal | Simlai | Marketing Executive |
| Joseph | Irvine | Marketing Executive |
| Shahida | Ali | Customer Service Manager |
| Peter | Champion | Finance Manager |
+———+———-+—————————-+
12 rows in set (0.00 sec)

4.The statement displays all non programmers who are not between the ages of 28 and 32.
mysql> 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;

+———+———-+—————————-+——+
| f_name | l_name | title | age |
+———+———-+—————————-+——+
| Anamika | Pandit | Web Designer | 27 |
| Mary | Anchor | Web Designer | 26 |
| Roger | Lewis | System Administrator | 35 |
| Danny | Gibson | System Administrator | 34 |
| Mike | Harper | Senior Marketing Executive | 36 |
| Hal | Simlai | Marketing Executive | 27 |
| Joseph | Irvine | Marketing Executive | 27 |
| Peter | Champion | Finance Manager | 36 |
+———+———-+—————————-+——+
8 rows in set (0.00 sec)