Maintaining order in MySQL "IN" query

Variable-length arguments in FIELD()

Digging deeper, when you have dynamic lists of random lengths, leverage the flexibility of the FIELD() function:

SELECT * FROM products WHERE id IN (4, 7, 1) ORDER BY FIELD(id, 4, 7, 1);  -- Yes, FIELD() is that generous!


Taking control of the unpredictable IN predicate

By design, the IN predicate for values acts more like a wild party—everyone's invited, but there's no guarantee where they end up. So, the results can land in a random order. The ORDER BY clause calms this chaos, ensuring your results turn up sorted in your intended order.

A stringy approach: FIND_IN_SET()

If your data leans towards strings or comma-separated values more than integers, FIND_IN_SET() is a viable alternative to FIELD():

SELECT * FROM products WHERE FIND_IN_SET(id, '4,7,1') ORDER BY FIND_IN_SET(id, '4,7,1');  -- Comma Lovers, rejoice!


Be mindful though; it treats your precious list as a singular string or set.

Performance considerations

Performance remains elusive to pin down between FIELD() and FIND_IN_SET(). They perform on par, wearing the index on the sorted column like an unnecessary accessory. If you have large datasets, it's worth running some tests to see if one method edges out the other.

Top-hand practical tips

Don't rely on SQL's good mood

Without ORDER BY, a SQL query won't guarantee your results in any specific order. An ORDER BY not only saves your day but your sanity too; don't leave your order to fate, or worse — SQL's mood.

Suit up your database — Index!

If performance keeps you up at night, consider creating an index on the column(s). It's a bit like dressing up your database for an event - it may not sing any better, but it sure looks more efficient.

Thinking out of the (in) box: CASE WHEN

For absolute control over order, CASE WHEN stands undefeated. While it requires more keystrokes, its power and flexibility can't be ignored:

SELECT * FROM products
WHERE id IN (4, 7, 1)
ORDER BY CASE id      -- Switching gears, one case at a time!
  WHEN 4 THEN 1
  WHEN 7 THEN 2
  WHEN 1 THEN 3
  ELSE 4
END;
