MySQL IN??? MySQL OR??? MySQL AND???
When I use one and when I use 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:
AND – we use AND 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.
OR – we use OR 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 .
IN – we use in the same way which we use OR 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');
This both queries will return the same result, all cities which are in USA, BRA or IRL.