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?


No, SQL is a declarative language, not procedural (mostly) so you're just declaring what information that you want and then the Optimizer decides how to get it and what order to return unless you specify.

| improve this answer | |

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.

| improve this answer | |

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:

FROM users u
  JOIN unnest(array[3,1,2]) with ordinality as l(id, idx) ON u.id = l.id
ORDER BY l.idx;

The 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 order by.

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.