В отчёте объединяют текущие и архивные записи. К чему применится сортировка в этом запросе? пример с кодом

WITH current_data(id, created_at) AS (
    VALUES (1, DATE '2024-03-01'), (2, DATE '2024-01-10')
), archive_data(id, created_at) AS (
    VALUES (3, DATE '2023-12-20'), (4, DATE '2024-02-15')
)
SELECT id, created_at
FROM current_data
UNION ALL
SELECT id, created_at
FROM archive_data
ORDER BY created_at DESC;

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

ORDER BY created_at DESC сортирует итоговый набор, сформированный после выполнения UNION ALL, а не каждую выборку отдельно. Поэтому все четыре строки будут упорядочены по created_at от самой поздней даты к самой ранней.

Без внешнего ORDER BY SQL не обязан сохранять порядок строк ни в одном из источников, ни порядок, в котором они перечислены в операторах UNION ALL.

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

Реляционная модель рассматривает результат запроса как множество или мультимножество строк, но не как последовательность. Порядок становится частью результата только после явного применения ORDER BY.

Операции UNION, UNION ALL, INTERSECT и EXCEPT сначала формируют общий результат из нескольких выборок. Поэтому сортировка, относящаяся ко всему оператору объединения, логически применяется к уже объединённому набору.

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

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

Особенно опасно полагаться на порядок без финального ORDER BY в пагинации, выгрузках и тестах, где результат сравнивается построчно. Совпадение порядка на одной версии СУБД не является гарантией SQL-контракта.

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

В приведённом запросе сначала вычисляются две выборки, затем UNION ALL объединяет их без удаления дубликатов, после чего внешний ORDER BY сортирует весь результат. Логически строки будут расположены так: id = 1, id = 4, id = 2, id = 3.

WITH a(id, score) AS (
    VALUES (1, 70), (2, 95)
), b(id, score) AS (
    VALUES (3, 80), (4, 60)
)
SELECT id, score FROM a
UNION ALL
SELECT id, score FROM b
ORDER BY score DESC;

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

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

Если значения created_at совпадают, их взаимный порядок не определён полностью. Для стабильного порядка следует добавить уникальный детерминирующий ключ, например ORDER BY created_at DESC, id ASC.

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

Сервис формировал единую ленту из таблиц текущих и архивных событий. Разработчик добавил сортировку только к запросу текущих событий, поэтому архивные строки в зависимости от плана выполнения появлялись отдельным блоком или в произвольных местах.

Рассматривались два варианта. Сортировка каждой ветви могла уменьшить объём локальной работы в отдельных сценариях, но не гарантировала порядок общего результата. Финальная сортировка после UNION ALL давала требуемый глобальный порядок, хотя требовала сортировки объединённого набора и могла потребовать дополнительной памяти.

Выбрали внешний ORDER BY created_at DESC, event_id DESC, потому что отчёту нужен именно общий порядок и стабильное разрешение одинаковых дат. В результате пагинация стала воспроизводимой, а поведение перестало зависеть от плана выполнения.

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

1. Что изменится, если добавить LIMIT только после UNION ALL?

   LIMIT после объединения ограничит уже отсортированный или неотсортированный общий результат — в зависимости от наличия внешнего ORDER BY. Для получения, например, пяти строк из каждой таблицы нужно ограничивать каждую ветвь отдельно, а затем объединять результаты. Это неэквивалентные операции: «топ-5 из каждого источника» не равно «топ-5 из общего набора».

2. Можно ли использовать в финальном ORDER BY столбец, которого нет в результирующем списке?

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

3. Гарантирует ли ORDER BY created_at DESC единственный порядок строк?

   Нет. Он определяет порядок только по created_at; строки с одинаковыми значениями могут следовать в любом взаимном порядке. Для детерминированного результата добавляют уникальный ключ: ORDER BY created_at DESC, id ASC. Это важно для пагинации: без второго ключа строки между страницами могут перемещаться при разных планах или изменениях данных.