How to work with AND, OR, and IN operators in MySQL

MySQL has a large number of operators that offer us many options to build our queries. In this article, we will focus on learning how the AND, OR,  and IN operators work.

First, we will create an Employee table on which we will build our queries. The table will have the following structure and content:

CREATE TABLE `employee` ( `id` int(11) NOT NULL, `birth_date` date NOT NULL, `first_name` varchar(20) NOT NULL, `last_name` varchar(20) NOT NULL, `gender` varchar(10) NOT NULL, `hire_date` date NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `employee` (`id`, `birth_date`, `first_name`, `last_name`, `gender`, `hire_date`) VALUES (10001, ‘1963-09-02’, ‘Dorris’, ‘Bodhi’, ‘M’, ‘1996-06-26’), (10002, ‘1974-06-02’, ‘Bryanna’, ‘Kolleen’, ‘F’, ‘1995-11-21’), (10003, ‘1969-12-03’, ‘Rayner’, ‘Colt’, ‘M’, ‘1996-08-28’), (10004, ‘1964-05-01’, ‘Tanner’, ‘Clifford’, ‘M’, ‘1996-12-01’), (10005, ‘1965-01-21’, ‘Trent’, ‘Dolph’, ‘M’, ‘1999-09-12’), (10006, ‘1963-04-20’, ‘Ashlee’, ‘Cristen’, ‘F’, ‘1999-06-02’), (10007, ‘1967-05-23’, ‘Irene’, ‘Yazmin’, ‘F’, ‘1999-02-10’), (10008, ‘1968-02-19’, ‘Terence’, ‘Dalton’, ‘M’, ‘2004-09-15’), (10009, ‘1962-04-19’, ‘Regina’, ‘Wayland’, ‘F’, ‘1995-02-18’), (10010, ‘1973-06-01’, ‘Shanon’, ‘Mortimer’, ‘F’, ‘1999-08-24’); ALTER TABLE `employee` ADD PRIMARY KEY (`id`);

Script 1. Employee table creation

We can execute the following query to verify the Employee table content:

SELECT * FROM `employee`

Script 2. Query to retrieve Employee table rows

Result:

Table 1. Employee table rows

MySQL IN query operator

MySQL IN operator allows us to verify if a determined value matches a value in a list or subquery.

Example of use:

We are going to build a query that returns the employees whose ID is IN the following list: 10001, 10002, 10003.

SELECT * FROM `employee` WHERE id IN (10001,10002,10003)

Script 3. MySQL IN query operator example

Result:

Table 2. MySQL IN query operator example results

MySQL AND query operator

MySQL AND operator is a boolean kind operator. It is used to compare a couple of expressions. It will Return TRUE if both expressions are met. Otherwise, it will return FALSE.

Example of use:

Let’s build a query that returns the data of the women hired in 1999.

SELECT * FROM `employee` WHERE (YEAR(hire_date) = '1999')  AND (gender = 'F')

Script 4. MySQL AND query operator example

Result:

Table 3. MySQL AND query operator example results

MySQL OR query operator

MySQL OR query operator is also a boolean kind operator. It is used to compare a couple of expressions. It will return TRUE if at least one expression is met. It will return FALSE Otherwise.

Example of use:

Let’s build a query that returns the data of the employees hired in 1996 or 1999.

SELECT * FROM `employee` WHERE (YEAR(hire_date) = '1996') or (YEAR(hire_date) = '1999')

Script 5. MySQL OR query operator example

Result:

Table 4. MySQL OR query operator example results