WebDevelopersNotes logo

home-icon Home / MySQL / Tutorial MySQL – IN and BETWEEN

Tutorial MySQL – IN and BETWEEN

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)

Sponsored Links

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)

Sponsored Links

Your comments
Star icon IMPORTANT Have a question / problem? Click here to ask an expert.

Don't believe everything you read on the Internet

Sponsored Links

Tips

How do I view a deleted web page?
Is there a way to view a deleted web page - one that is no longer available? Yes there is and the solution is quite simple. [more...]

Fact

Hard disk storage capacities have been increasing over the years. The world's first hard disk, IBM 350 Disk File, could store 5MB of data was announced on 4th September 1956. The first 1TB drive appeared 51 years later in 2007. It took just a couple of years to double the capacity and the first 2GB drive came into the market in 2009. [more...]

We use cookies to give you the best possible website experience. By using WebDevelopersNotes.com, you agree to our Privacy Policy