Краткий ответ

SELECT не гарантирует порядок строк без явного ORDER BY. Одинаковый фильтр может возвращать те же строки в разной последовательности, потому что порядок не является частью результата реляционного запроса без сортировки.

Исторический контекст

В реляционной модели таблица рассматривается как множество строк без естественного порядка. СУБД может хранить строки, читать индексы или объединять результаты в любом удобном для оптимизатора порядке.

ORDER BY нужен для преобразования неупорядоченного результата в последовательность, важную для отчётов, интерфейсов, выгрузок и постраничной навигации.

Постановка проблемы

Если приложение рассчитывает на порядок, случайно полученный из физического расположения строк, результат становится нестабильным. После создания индекса, изменения плана выполнения, обновления статистики или параллельного выполнения тот же запрос может вернуть строки иначе.

Особенно опасно это при пагинации. Без стабильной сортировки строки могут дублироваться между страницами или пропадать из них, а LIMIT без ORDER BY выбирает произвольный набор строк.

Подробное решение

Порядок задаётся предложением ORDER BY. Если несколько строк имеют одинаковое значение сортировки, их взаимный порядок всё ещё не определён, поэтому для детерминированного результата нужно добавить уникальный или достаточно специфичный ключ.

SELECT id, created_at
FROM orders
WHERE status = 'open'
ORDER BY created_at DESC, id DESC;

Здесь сначала выбираются открытые заказы, затем результат сортируется по дате, а id разрешает совпадения дат. Фильтрация и сортировка могут быть оптимизированы внутренним планом, но наблюдаемый порядок результата должен соответствовать ORDER BY.

Без сортировки СУБД вправе использовать порядок чтения таблицы или индекса, однако полагаться на это нельзя. Сортировка может потребовать памяти и дополнительного времени, но это необходимая цена за предсказуемый порядок; подходящий индекс иногда снижает её стоимость.

Сортировка во внутреннем подзапросе сама по себе обычно не гарантирует порядок внешнего результата. Гарантию следует задавать в том запросе, результат которого непосредственно потребляет клиент.

Ситуация из практики

API возвращал первые двадцать открытых заказов без сортировки. На тестовой базе результат казался стабильным, но после появления индекса часть заказов начала исчезать при переходе между страницами.

Рассматривались два варианта. Можно было оставить текущий запрос и надеяться на порядок чтения — это не требовало изменений, но не давало гарантии. Можно было сортировать только по дате — это улучшало предсказуемость, однако одинаковые даты оставляли неопределённый порядок.

Выбрали сортировку по дате и уникальному идентификатору в согласованных направлениях. Результат стал воспроизводимым, а пагинация — стабильной; дополнительная сортировка увеличила стоимость запроса, поэтому для неё проверили индекс и план выполнения.

Что кандидаты часто упускают

Достаточно ли ORDER BY по неуникальному столбцу для полного воспроизведения результата?

Нет. Если несколько строк имеют одинаковое значение сортировки, СУБД может переставлять их местами. Для полного порядка добавляют уникальный идентификатор или другой набор столбцов, однозначно определяющий последовательность.

Гарантирует ли порядок строк во внутреннем подзапросе порядок финального SELECT?

Нет, если внешний запрос сам не задаёт сортировку. Оптимизатор может изменить план, а внешний запрос может объединить, отфильтровать или иначе обработать строки. Надёжная гарантия нужна на внешнем уровне, который возвращает результат клиенту.

Почему пагинация через пропуск фиксированного числа строк особенно чувствительна к нестабильной сортировке?

При отсутствии детерминированного порядка разные выполнения могут по-разному распределить равноправные строки между страницами. Даже с ORDER BY по неуникальному столбцу это возможно при вставках и изменениях данных. Для более устойчивой навигации применяют сортировку с уникальным ключом, а при подходящих требованиях — пагинацию по последнему значению сортировки и ключу, то есть keyset pagination.