MySQL Tutorial : MySQL IN statement (Advanced)

Last updated on September 11th, 2020 at 11:41 am

The basic use of MySQL IN statement deals with one column being compared to multiple values. There are also a couple of advanced uses of MySQL IN statement which make interesting use cases. Consider the sample table user_table where 5 users are assigned different permissions as admin, superuser, etc based values 1 or 0,

mysql> SELECT * FROM user_table;
+-----------+----------+--------------+----------+-----------+
| user_id   | is_admin | is_superuser | is_staff | is_active |
+-----------+----------+--------------+----------+-----------+
|    1      |    1     |       1      |     1    |     1     |
|    2      |    0     |       1      |     1    |     1     |
|    3      |    0     |       1      |     1    |     1     |
|    4      |    0     |       0      |     0    |     1     |
|    5      |    0     |       0      |     0    |     0     |
+-----------+----------+--------------+----------+-----------+

Use Case 1 : MySQL IN statement to compare a value with multiple columns

It’s syntax is:

SELECT * FROM TableName WHERE value IN (column1, column2, column3, column4)

E.g,

mysql> SELECT * FROM user_table WHERE 1 IN (is_admin, is_superuser, is_staff, is_active);

+-----------+----------+--------------+----------+-----------+
| user_id   | is_admin | is_superuser | is_staff | is_active |
+-----------+----------+--------------+----------+-----------+
|    1      |    1     |       1      |     1    |     1     |
|    2      |    0     |       1      |     1    |     1     |
|    3      |    0     |       1      |     1    |     1     |
|    4      |    0     |       0      |     0    |     1     |
+-----------+----------+--------------+----------+-----------+

The usual IN statement involves only ONE COLUMN that is being compared to multiple values.

SELECT * FROM TableName WHERE column1 IN (1,2,3,4)

It works as

SELECT  * FROM   TableName WHERE  column1 = 1 OR column1 = 2 OR column1 = 3 OR column1 = 4

This  statement is A VALUE that is compared to multiple columns

SELECT * FROM TableName WHERE 1 IN (column1, column2, column3, column4)

is same as

SELECT  * FROM   TableName WHERE  column1 = 1 OR column2 = 1 OR column3 = 1 OR column4 = 1

Use Case 2 : MySQL IN statement to compare multiple columns with multiple values

It’s syntax is:

SELECT * FROM TableName WHERE (column1,column2) in ((value1,value2),(value3,value4),..)

E.g,

mysql> SELECT * FROM user_table WHERE (is_staff,is_admin) in ((1,0),(1,1));

+-----------+----------+--------------+----------+-----------+
| user_id   | is_admin | is_superuser | is_staff | is_active |
+-----------+----------+--------------+----------+-----------+
|    1      |    1     |       1      |     1    |     1     |
|    2      |    0     |       1      |     1    |     1     |
+-----------+----------+--------------+----------+-----------+

It works as

SELECT * FROM user_table WHERE (is_staff=1 and is_admin=0) or (is_staff=1 and is_admin=1);