How to use IN – AND – OR operators in MySQL

MySQL has 3 very useful operators named IN –  OR – AND. They behave very differently and is very common to misuse them.
When I use one and when I use the other?

we will use the city table to this tutorial:

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   | MUL |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+

Let’s see a simple rule to clarify this:

MySQL AND Operator

We use the AND operator to add a new condition which will need to be true:

SELECT * FROM City WHERE CountryCode = 'BRA' AND Population > 1000000 AND District LIKE 'São Paulo';

This query will return Cities which CountryCode is BRA and Population is bigger than 1000000 and Discrict is São Paulo, this three condition need to be true.

MySQL OR Operator

We use the OR operator to add a new condition which or one or other needs to be true:

SELECT * FROM City WHERE Population > 1000000 OR District LIKE 'São Paulo';

This query will return Cities which have a population bigger than 1000000 or have São Paulo as their district .

MySQL IN Operator

We use the IN operator the same way which we use the OR operator but for the same column:

SELECT * FROM City WHERE CountryCode = 'USA' OR CountryCode = 'BRA' OR CountryCode = 'IRL';
SELECT * FROM City WHERE CountryCode IN ('USA', 'BRA', 'IRL');

Both queries will return the same result, all cities which are in USA, BRA or IRL.

2 thoughts on “How to use IN – AND – OR operators in MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *