If I use the following selection
SELECT * FROM users WHERE id IN (3,1,2)
Can I expect the result to be in the same order like the provided ids?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
Short answer: No.
The only way to guarantee the order of rows returned is through the "order by" clause.
You have little or no control over how the database chooses to "get at" any of your data, and, therefore, the order in which it gets to those records in doing so.
You might get lucky and things might come out in the order you want ... but don't rely on it.
As the others have correctly written: no, you can not rely on any order unless you use an `order by
However, if for some reason, you need to get the result in the order how the parameters are specified, you need to use a different query:
SELECT u.* FROM users u JOIN unnest(array[3,1,2]) with ordinality as l(id, idx) ON u.id = l.id ORDER BY l.idx;
unnest() with ordinality creates a column that indicates the original position of the value in the array and that can then be used in an
Note that this will behave differently than the
IN operator if duplicate values can occur in that list. The join will return those rows twice from the
users table, the
IN would not.