ORDER BY и сортировка

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

Как спрашивают

Как отсортировать по нескольким колонкам? Где окажутся NULL? Почему LIMIT 10 без ORDER BY возвращает разные строки? Чем плоха пагинация через OFFSET?

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

ORDER BY сортирует по первой колонке, при равенстве по второй и так далее, направление задаётся для каждой колонки отдельно. В PostgreSQL NULL считается больше любого значения: при ASC он в конце, при DESC в начале, а NULLS FIRST и NULLS LAST меняют это явно. Без ORDER BY порядок строк не гарантирован, а при равных значениях он тоже не гарантирован, поэтому для стабильного результата в конец сортировки добавляют уникальную колонку. Глубокие страницы через OFFSET медленные, их заменяют курсорной пагинацией.

Разбор: картотека

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

SELECT name, department_id, salary
FROM employees
ORDER BY department_id, salary DESC;

name  | department_id | salary
------+---------------+-------
Анна  |             1 | 300000
Борис |             1 | 250000
Вера  |             1 | 250000
Глеб  |             2 | 200000
Дина  |             2 | 150000
Егор  |             3 | 120000
Жанна |          NULL | 180000

Отделы идут по возрастанию, внутри отдела зарплаты по убыванию. DESC относится только к salary, а не ко всему списку: частая ошибка думать, что одно DESC в конце разворачивает всю сортировку.

Где окажется NULL

Жанна без отдела стоит последней. В PostgreSQL NULL при сортировке ведёт себя как значение больше всех остальных:

SELECT name, city FROM customers ORDER BY city;       -- Света в конце
SELECT name, city FROM customers ORDER BY city DESC;  -- Света в начале

Если нужен другой порядок, скажи его явно:

SELECT name, city FROM customers ORDER BY city NULLS FIRST;

name  | city
------+-------
Света | NULL
Ольга | Казань
Роман | Казань
Павел | Пермь

В других СУБД правило другое: в SQLite и MySQL при сортировке по возрастанию NULL идёт первым. На собеседовании уточни СУБД или пиши NULLS FIRST и NULLS LAST явно, тогда ответ не зависит от базы. В MySQL этих слов нет, там обходятся выражением ORDER BY city IS NULL, city.

Равные значения меняются местами

У Бориса и Веры одинаковая зарплата. Кто из них второй по зарплате?

SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 2;

На учебной базе PostgreSQL вернул Анну и Веру, хотя Борис стоит в таблице раньше. Порядок среди равных не определён: СУБД вправе вернуть их как угодно, и после обновления статистики, вставки строк или смены плана ответ может поменяться. Это недетерминированная сортировка.

Лечится добавлением уникальной колонки в конец сортировки, обычно первичного ключа:

SELECT name, salary FROM employees ORDER BY salary DESC, id LIMIT 3;

name  | salary
------+-------
Анна  | 300000
Борис | 250000
Вера  | 250000

Теперь порядок однозначен при любом плане.