CODEWITHSUNDEEP
mysql
DroidOS
DroidOS

Reputation: 8890

SQL SELECT... IN result order

Here is the scenario - I have a table

CREATE TABLE IF NOT EXISTS `stylemaps` (
`zyid` varchar(9) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
`styid` varchar(9) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT 'zzzz0000Z',
UNIQUE KEY `zyid` (`zyid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

A typical set of entries in this table might be

zyid        styid
qrst1234    abcd1230
abcd9876    abcd1231
pqzx4569    abcd1232
bcde0000    abcd1233

i.e. the order of entries for zyid is fairly random.

Now suppose I issue

SELECT styid FROM `styles` WHERE zyid in ['abcd9876','bcde0000']

the result I get is

abcd1231
abcd1233

i.e. the rows are ordered in the same way as the IN clause my SQL statement.

My question is this - can I rely on this always being the case (so long as I order the IN clause entries correctly)?

If this ever fails and I end up mapping styles incorrectly the end results are liable to be completely garbled. The alternative would be to do 

SELECT zyid,styid

instead of 

SELECT styid

and then do some more work on the results to guarantee the right mapping.

Upvotes: 4

Views: 310

Answers (4)

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

No, you cannot depend on the results being in order of the IN contents. Here's why:

  • Each row is checked in an order based on the most efficient order that the query optimizer chooses. If you have no other condition in your WHERE clause and you are viewing all columns (or have no indexes other than PRIMARY), this means the optimizer chooses to do a table scan, so it reads all rows in the most efficient way. In your result, this happens to be the order of your first output.
  • For each row, it performs a binary search of the entries in the IN clause. This makes the search (for each row) quite efficient, but doesn't alter the order of the rows.

In short, the order of the rows you get when you use an IN clause is no different to the order you get without using the IN clause, but you can affect that order by changing the statement in other ways, such as by using ORDER BY, giving you the most control; or (if you have other indexes) by using other elements in your statement that changes the way the optimizer reads rows.

Upvotes: 2

hsz
hsz

Reputation: 152216

You can try with:

  SELECT styid
    FROM `styles` 
   WHERE zyid IN ['abcd9876','bcde0000']
ORDER BY FIELD(zyid, 'abcd9876','bcde0000')

Upvotes: 5

tuxmania
tuxmania

Reputation: 956

AFAIK you cannot rely because in the end the DBMS Optimizer decides what to display first.

Upvotes: 0

No'am Newman
No'am Newman

Reputation: 6477

Simply add an 'order by' clause at the end, eg

SELECT styid FROM `styles` 
WHERE zyid in ['abcd9876','bcde0000']
ORDER BY styid

Upvotes: -2

Related Questions