SQL: Just like tables, IN clauses have no default order

In Stack Overflow, I saw a poster with a query like this:

select * from student where stud_id in (
'5',
'3',
'4'
)

And he was frustrated that he couldn’t reliably get an ordered output like this:

id|   name  |
5 |  John   |
3 |  Erik   |
4 | Michael |

The problem is that he was assuming that the IN clause had a specified order that was then obeyed by the SELECT statement.

It doesn’t.

If you want a SELECT statement to return rows in a particular order, you need to use an ORDER BY clause, and, in this case, you need something to order the rows on in the first place.

I think the easiest way to do that is with a table variable that has an IDENTITY column like this:

DECLARE TABLE @StudentIDs
(
    StudentIDKey int IDENTITY(1,1) PRIMARY KEY,
    StudentID int
);

INSERT @StudentIDs (StudentID)
VALUES 
(5),
(3),
(4);

SELECT *
FROM Student AS s
INNER JOIN @StudentIDs AS id
ON s.StudentID = id.StudentID
ORDER BY id.StudentIDKey;

I think that’s about the simplest way to do it, particularly if there are a lot of values that could be in that IN clause.

Hope that helps someone.

2019-10-31