SQL: Is results order mapped to values in 'IN' expression?

Question

I have a list of record IDs for which I want to retrieve a single value from a table in SQL Server 2008.

My query is:

SELECT TestResult.FailureDetails FROM TestResult WHERE TestResult.ID IN (1,2,3,...)

The 'IN' expression contains hundreds of IDs. The ID column is the primary key of the table.

If my table is like:

ID    Value
1     A
2     B
3     C

Will I always get results ordered by order of appearance in the 'IN' expression? If not, I am considering doing:

SELECT TestResult.ID, TestResult.FailureDetails FROM TestResult WHERE TestResult.ID IN (1,2,3,...)

Do you always want in order of `ID` as per your example or do you need to support arbitrary orders? — Martin Smith, Nov 08 '11 at 17:27
In order of the ID would have been fine. Per the answer below, I am now selecting TestResult.ID as well. — siger, Nov 08 '11 at 17:39

score 1 · Accepted Answer · answered Nov 08 '11 at 17:25

1

No, you will not always get results in order based on the IN.

Be explicit, and add an ORDER BY clause.

SELECT FailureDetails 
FROM TestResult 
WHERE ID IN (1,2,3,...) 
ORDER BY ID;

answered Nov 08 '11 at 17:25

p.campbell

98,673
67
256
322

score 0 · Answer 2 · answered Nov 08 '11 at 17:25

I would be surprised if they were ordered by the IN clause. As you've got no ORDER BY you'll probably get the results in PK order, but I wouldn't depend on that either.

If order is important you should set it explicitly:

SELECT TestResult.ID, TestResult.FailureDetails FROM TestResult WHERE TestResult.ID IN (1,2,3,...)
ORDER BY TestResult.ID

SQL: Is results order mapped to values in 'IN' expression?

2 Answers2

Linked