Different approach of using IN clause in MySql

Question

Today I have posted an answer with a query like this ``` SELECT * FROM table_name where column_name IN (val1,val2,...) ``` Some another user has posted the answer a query like this ``` SELECT * FROM table_name where val1 IN (column_name) ``` As you can see here the position of the column_name and values are interchanged. From Mysql Docs expr IN (value,...) Returns 1 if expr is equal to any of the values in the IN list, else returns 0. If all values are constants, they are evaluated according to the type of expr and sorted. The search for the item then is done using a binary search. This means IN is very quick if the IN value list consists entirely of constants. ``` mysql> SELECT 2 IN (0,3,5,7); -> 0 mysql> SELECT 'wefwf' IN ('wee','wefwf','weg'); -> 1 ``` As it clearly says that the above one(my query) is correct. but both the above queries produce the same output. Also why not the other approach in listed in Mysql Documentation? This question serves as a canonical information source…

Accepted Answer

you raised a question that is connected with my answer here.

In a simple explanation using this statements below,

```
SELECT * FROM TableName WHERE column1 IN (1, 2, 3, 4)
-- versus
SELECT * FROM TableName WHERE 1 IN (column1, column2, column3, column4)
```

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

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

while the second statement is A VALUE that is compared to multiple columns.

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

which is a bit different from one another.

UPDATE 1

Here's the third form of `IN` clause:

- Multi-Column IN clause – Unexpected MySQL Issue

Different approach of using IN clause in MySql

Solution

Problem

Related problems