19

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 regarding the use of IN. Its purpose is to have detailed, high quality answers detailing the proper use on IN in queries.

Yogesh Suthar
  • 30,424
  • 18
  • 72
  • 100
  • 1
    10x i didn't knew there is another way of writing `IN`,in the 1st one you are searching for different values in column like or and in the second query searching for single value in the entire column .?? – Arun Killu Apr 20 '13 at 06:39
  • What is the "same output" . I see one number outputted which is different in both cases. Obviously this is a boolean value 0 or 1 depending on if operation is successfull. – Menelaos Apr 20 '13 at 06:43
  • @meewoK The queries output same result is upper-one...:) – Yogesh Suthar Apr 20 '13 at 06:45

2 Answers2

32

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:

Community
  • 1
  • 1
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Yes , but I want to know why it is not listed in documenation? Users like me don't know second approach. Even I haven't known second approach if I haven't seen your answer. – Yogesh Suthar Apr 20 '13 at 06:47
  • 3
    It is listed on the documentation. It just that you are confused when the values and columns are interchange but the idea is really the same. Maybe you have missed the *third* form of `IN` -- a multicolumn. Example `SELECT * FROM TableName WHERE (1, 2) IN ((2,4),(1,2),(5,6))` – John Woo Apr 20 '13 at 06:51
  • I didn't find the third form of `IN` in documentation. Can you link it. – Yogesh Suthar Apr 20 '13 at 06:53
  • I can't find it also, I just found a blog that is using it a long time ago. – John Woo Apr 20 '13 at 06:54
  • [Multi-Column IN clause](http://www.mysqlperformanceblog.com/2008/04/04/multi-column-in-clause-unexpected-mysql-issue/) – John Woo Apr 20 '13 at 06:58
  • @JW Thanks...this link will be useful for other users if you paste it in your answer...:) – Yogesh Suthar Apr 20 '13 at 07:00
  • 2
    @JW: the third form ("tuple comparison") –  Apr 20 '13 at 07:15
  • 1
    @a_horse_with_no_name Oh thanks for the correct term. But I think it is supported, http://www.sqlfiddle.com/#!2/d41d8/10967 – John Woo Apr 20 '13 at 07:16
  • 1
    @JW: a thanks. I mixed that up with the possibility to *update* two columns at a time (`update foo set (a,b) = (select ...)`) –  Apr 20 '13 at 07:18
5

As noted in the question, the MySQL documentation gives the form of the IN clause as:

expr IN (value,...)

expr is an expression that can only have a single value for a given row - this value can be a constant (such as 2), the name of a column (such as column_name from the question) which will have a specific value on a given row, or any expression including functions, operators, constants and column_names that produces a single value for a given row.

The contents of the parentheses - value,... - contains an expression that can be evaluated as supplying a list of values (potentially including an empty list of values, in which case the in condition would be evaluated as false). This list of values could be:

  • in the form of an explicit series of one or more constants (such as val1,val2,...) and/or one or more single-valued column names for a given row (such as column_name) and/or a series of expressions that each produce a single value for a given row; or
  • in the form of a select clause that returns a set of values (such as select column_name from table where ...) that can then be compared with the value of expr (immediately preceding the IN operator).

To summarise: expr must evaluate to a single value (for a given row), while the parenthesised set of values to be matched can be evaluated to 0, 1 or more values.

In this respect, MySQL operates the same way as any other variant of SQL that I have used.

Where MySQL does vary is that the entire expr IN (value,...) clause itself evaluates to 0 or 1, which MySQL treats as false or true respectively - in most variants of SQL, the entire clause would evaluate to a boolean FALSE or TRUE.