CODEWITHSUNDEEP
mysql
Matt Elhotiby
Matt Elhotiby

Reputation: 44066

will an in clause always return the same order in mysql

ok so i have this query 

select price from product where product_id in (49, 50, 51)

and I want to know if the three records returned will be in the same order as my in clause. SO for example

100.0000
166.0000
55.0000

will 100.0000 belong to 49, 166.0000 to 50 and 55.0000 to 51

Upvotes: 1

Views: 615

Answers (3)

Johan
Johan

Reputation: 76557

No

An IN clause does not sort your rows. Also IN clauses suck, if you can avoid them do.

SELECT price from product where product_id BETWEEN 49 AND 51

is a better query. If you want your query in a particular order do order by somefield at the end.

More on IN
In your use case you don't need IN, because you can trade 3 comparisons with 2, which is faster.
If your query is:

select price from product where product_id in (51547, 417853, 879823)

Then using IN is fine. This is the only case where IN doesn't suck

Especially never use IN with a subselect

enter image description herecoding horror 

SELECT price FROM product WHERE product_id IN 
    (SELECT product_id FROM tableX)

Replace this with

SELECT price FROM product 
INNER JOIN tableX ON (product.product_id = tableX.product_id)

Which is much faster.

Upvotes: 2

vcsjones
vcsjones

Reputation: 141638

No. They will be returned in the order which they are encountered when selecting from the product table. Ordering in a database is an expensive operation, so it won't sort anything unless you explicitly tell it to. You should have an order by clause if order is important.

Upvotes: 3

Nicola Cossu
Nicola Cossu

Reputation: 56357

If you want to preserve the same order you have specified within in clause you have to use field() function.

select price from product where product_id in (49, 50, 51)
order by field(product_id,49,50,51)

Upvotes: 1

Related Questions