Сортировка NULL-значений в конец таблицы в PostgreSQL

Быстрый ответ

Чтобы значения NULL находились в конце таблицы после сортировки в SQL, пользуйтесь ORDER BY совместно с условием CASE или оператором IS NULL. Вот примеры такого подхода:

В большинстве SQL-диалектов применяют такую конструкцию:

SELECT * FROM tableName
ORDER BY CASE WHEN columnName IS NULL THEN 1 ELSE 0 END, columnName;

В PostgreSQL, MySQL и SQLite подойдет следующий вариант:

SELECT * FROM tableName
ORDER BY columnName IS NULL, columnName;

В обоих случаях сначала отображаются значения, не равные NULL, а тогда все строки с NULL, что соответствует сортировке по возрастанию при размещении NULL в конце списка.

Особенности работы с NULL в SQL

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

Адаптация под разные диалекты SQL

Различия между системами управления базами данных могут повлиять на обработку NULL при сортировке:

• PostgreSQL поддерживает встроенную опцию NULLS LAST в условии ORDER BY, позволяющую разместить NULL в конце списка, независимо от типа сортировки.

• MySQL и SQLite не предлагают специфического синтаксиса для сортировки с NULL в конце списка, но этот результат можно достичь вышеуказанным методом.

Обработка NULL в PostgreSQL

С версии PostgreSQL 8.3 доступен следующий синтаксис сортировки:

SELECT * FROM tableName
ORDER BY columnName DESC NULLS LAST;

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

Решения для предыдущих версий SQL

В старых версиях SQL для сортировки с NULL в конце нужно воспользоваться условием CASE:

SELECT * FROM tableName
ORDER BY (CASE WHEN columnName IS NULL THEN 1 ELSE 0 END), columnName;

Взаимодействие с булевыми значениями

В PostgreSQL FALSE располагается перед TRUE. Поэтому при убывающей сортировке NULL, подобно FALSE, окажется в конце списка.

Визуализация

Возьмем в качестве примера группу людей разного роста, которых надо упорядочить. Рост некоторых из них не известен (NULL), и они не хотят его измерять:

Перед сортировкой: [👦🏻, NULL, 👨🏽, NULL, 👩🏼‍🦰]

Сортировка выполняется SQL-запросом, где рост людей с неопределенными данными рассматривается как бесконечно большой:

SELECT * FROM people
ORDER BY height IS NULL, height;

Результат сортировки выглядит так:

После сортировки: [👦🏻, 👨🏽, 👩🏼‍🦰, NULL, NULL]

Таким образом, люди с неизвестным ростом оказываются в конце списка, независимо от их истинного роста.

Профессиональные советы и распространенные ошибки

Чтобы эффективно работать с NULL, важно обращать внимание на детали и понимать природу этих значений. Вот несколько советов по сортировке NULL в SQL:

Профессиональные советы

• Ясность в SQL всегда важна. Обрабатывайте NULL отдельно в условии ORDER BY.
• Не бойтесь экспериментировать. Протестируйте ваши запросы на данных с NULL.
• Документируйте ваши запросы. Подробные комментарии помогут другим разработчикам понять ваши намерения при специальной сортировке NULL.

Распространенные ошибки

• Неверное понимание NULL как фактического значения, а не состояния неопределенности или отсутствия значения.
• Неучет того, что по умолчанию базы данных могут располагать NULL как наименьшим или наибольшим значением.
• Путаница при сравнении, когда NULL = NULL возвращает FALSE, что может привести к непредсказуемым результатам при сортировке.

Полезные материалы

1. MySQL Orderby a number, Nulls last – Stack Overflow — дискуссия о тонкостях размещения NULL в конце списка при сортировке в MySQL.
2. MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY Optimization — официальное руководство по оптимизации сортировки в MySQL.
3. PostgreSQL: Documentation: 16: 7.5. Sorting Rows (ORDER BY) — все о сортировке строк, в том числе с применением NULL, в PostgreSQL.
4. ORDER BY Clause (Transact-SQL) – SQL Server | Microsoft Learn — Microsoft рассказывает о вариантах сортировки в Transact-SQL.
5. SQL NULL Values – IS NULL and IS NOT NULL — методичное руководство по работе с NULL в SQL.