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);
