mysql in子句,在MySQL中使用IN子句的不同方法

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

This question serves as a canonical information source regarding the use of IN. Its purpose is to have detailed, high quality answers detailing the proper use on IN in queries.

解决方案

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: