Using SQL IN operator

SQL IN operator is used to specify multiple values in WHERE clause. Instead of using multiple OR operators we can use IN operator.

mysql> SELECT * FROM CUSTOMER;
+----+--------+---------+------+-----------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY   |
+----+--------+---------+------+-----------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA     |
|  2 | JONTY  | RHODES  |   40 | CHINA     |
|  3 | JOHNY  | DEPP    |   45 | INDIA     |
|  4 | JESSIE | PINKMAN |   25 | AUSTRALIA |
|  5 | JESSIE | JACKSON |   22 | CANADA    |
|  6 | NICOLE | KIDMAN  |   50 | USA       |
+----+--------+---------+------+-----------+
6 rows in set (0.00 sec)

SQL IN operator syntax with example -

Suppose we want to get the rows where country is INDIA, USA and CANADA, we can use it in two ways -
1. Using multiple OR with WHERE clause

SELECT * FROM CUSTOMER WHERE COUNTRY = 'INDIA' OR COUNTRY = 'USA' OR COUNTRY = 'CANADA';

1. Using IN operator with WHERE clause

SELECT * FROM CUSTOMER WHERE COUNTRY IN ('INDIA', 'USA', 'CANADA');

And we will get the same results, but IN operator is a little handy in such scenarios.

+----+--------+---------+------+---------+
| ID | FNAME  | LNAME   | AGE  | COUNTRY |
+----+--------+---------+------+---------+
|  1 | AKSHAY | KUMAR   |   12 | INDIA   |
|  3 | JOHNY  | DEPP    |   45 | INDIA   |
|  5 | JESSIE | JACKSON |   22 | CANADA  |
|  6 | NICOLE | KIDMAN  |   50 | USA     |
+----+--------+---------+------+---------+
4 rows in set (0.00 sec)

Also remember that using IN operator we can set restriction only for one column with one IN for setting restrictions on some other columns we will have to use OR operator again.