order while using IN

Question

I just wanted to know how IN works in My SQL query?

Let's say I have code as

select * from myTable WHERE id IN (5,9,2,4,6,3);

Will this be equivalent to

select * from myTable WHERE id = 5;
select * from myTable WHERE id = 9;
select * from myTable WHERE id = 2;
select * from myTable WHERE id = 4;
select * from myTable WHERE id = 6;
select * from myTable WHERE id = 3;

OR

order are random?

Edit 1

Here ORDER doesn't mean ORDER BY. I meant first 5, then 9, then 2 and so on

OR

random order means first 4, then 3, then 2, so on....

Please let me know.

I'm not quite sure what you are asking. Does the order you evaluate the IN clause matter? — Malkus, Nov 19 '12 at 17:24

Malkus · Accepted Answer · 2012-11-19T17:26:48.060

2

NO

In MySQL the IN clause is performed using a Binary Search. Reference

This means the search is optimized for performance and does not step through the list in order.

edited Nov 19 '12 at 17:26

answered Nov 19 '12 at 17:20

Malkus

3,686
2
24
39

score 0 · Answer 2 · answered Nov 19 '12 at 17:16

0

The order is random (well, not technically random, but you can't reliably predict it). The query you posted is equivalent to:

SELECT * FROM myTable WHERE id = 5 OR id = 9 OR id = 2 OR id = 4 OR id = 6 OR id = 3

If you want a specific order, you will need to use an ORDER BY clause. Doesn't matter what order you use for the values in the IN specification.

answered Nov 19 '12 at 17:16

Travesty3

14,351
6
61
98

1

And for an `ORDER BY` clause to use [this question](http://stackoverflow.com/q/396748/73226) may be useful. – Martin Smith Nov 19 '12 at 17:18
I don't think so. It's different query. – Micromega Nov 19 '12 at 17:21
You should answer with yes or no. Op seems to have an order. When you split the query into many smaller queries then you need to choose an order. – Micromega Nov 19 '12 at 21:09
The question was: are they equivalent **OR** is the order random? That question cannot be answered with yes or no. The answer is the latter, the order is "random." What other ordering is there in a SELECT statement besides ORDER BY? If the OP wants the results to come out with id 5, followed by id 9, followed by id 2, etc, then the link posted by @MartinSmith is exactly what the OP needs. – Travesty3 Nov 20 '12 at 14:30

score 0 · Answer 3 · answered Nov 19 '12 at 17:20

No. You need to be more specific. When you query the id in separate queries then the answer is no. If want an order you need to add the orderby clause. Also equal-to operator is always random hence when you mix IN operator and equal-to the order in the where clause is important.

order while using IN

OR

Edit 1

OR

3 Answers3