MySQL using IN()

Question

IN() is usually applied like this:

SELECT eid FROM comments WHERE id IN (1,2,3,4,5,6)

Would this generate an error or is it just syntactically bad?

SELECT eid FROM comments WHERE id IN (6)

No, because you have `id IN (6)`, in that case it will just behave as if you wrote ` `WHERE id=6` — Kebab Programmer, Mar 05 '19 at 15:52

score 2 · Answer 1 · answered Mar 05 '19 at 15:52

2

It will work as expected. Most probably under the hood it will be optimised as WHERE id = 6 anyway.

answered Mar 05 '19 at 15:52

Antoniossss

31,590
6
57
99

The timing tho XD – Kebab Programmer Mar 05 '19 at 15:53
indeed it will be optimized under the hood, ive posted a answer that proves that.. – Raymond Nijland Mar 05 '19 at 16:05

score 2 · Answer 2 · answered Mar 05 '19 at 15:53

2

No, it won't generate error, it will work correctly,
because the point of IN clause - is to check whether value exists in defined list.
In your case this list contains only one value (6).

answered Mar 05 '19 at 15:53

cn007b

16,596
7
59
74

score 1 · Answer 3 · answered Mar 05 '19 at 16:01

No it will not error, the MySQL optimizer is smart in that because it knows id IN (6) is equal to id = 6 and will handle it like that.

SELECT eid FROM comments WHERE id IN (6)

Will be rewritten/handled after optimizing as

/* select#1 */ select test.comments.eid AS eid from test.comments where ( test.comments.id = 6)

see demo

score 1 · Answer 4 · answered Mar 05 '19 at 16:07

MySQL IN() function finds a match in the given arguments.

Syntax:

expr IN (value,...)

The function returns 1 if expr is equal to any of the values in the IN list, otherwise, 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. Otherwise, type conversion takes place according to the rules.

For your case, If you are conscious about the performance of IN() with one element vs =, actually there is no significant difference between the MySQL statements, and the MySQL optimizer will transform the IN to the = when IN is just one element.

Something like-

SELECT eid FROM comments WHERE id IN (6)

to

SELECT eid FROM comments WHERE id  = 6

It will arise performance issue if it contains multiple elements inside the IN(). You can try with EXPLAIN to see the difference. See HERE

A large `IN()` or `OR` list can be optimized with a `UNION ALL` query.. i believe Oracle optimizer does this `IN()` and `OR` conversion to a `UNION ALL` query automatic if the id column is indexed. — Raymond Nijland, Mar 05 '19 at 16:14

MySQL using IN()

4 Answers4