Размещение NULL значений для ORDER BY с nullable столбцами

MySQL и PostgreSQL совершенно по-разному сортируют NULL-значения в nullable столбцах. В MySQL они будут располагаться перед всем, а в PostgreSQL — последними. Однако по замыслу приложения или UX может потребоваться другой порядок сортировки. В таких случаях порядок сортировки NULL-значений можно легко изменить.

Использование

MySQL

-- Поведение по умолчанию: NULL-значения помещаются первыми
SELECT * FROM customers ORDER BY country ASC;

-- NULL-значения помещаются первыми по правилу
SELECT * FROM customers ORDER BY country IS NOT NULL, country ASC;

-- NULL-значения, размещаемые последними по правилу
SELECT * FROM customers ORDER BY country IS NULL, country ASC;

PostgreSQL

-- Поведение по умолчанию: Значения NULL размещаются последними
SELECT * FROM customers ORDER BY country ASC;

-- NULL-значения помещаются первыми по правилу
SELECT * FROM customers ORDER BY country ASC NULLS FIRST;

-- NULL-значения, размещаемые последними по правилу
SELECT * FROM customers ORDER BY country ASC NULLS LAST;

Подробное объяснение

В SQL значение NULL означает неизвестное значение — значение для столбца просто неизвестно. Такие отсутствующие значения представляют собой проблему при сравнении с другим значением и при упорядочивании строк в таблице. С математической точки зрения несуществующее значение нельзя сравнить, например, с каким-либо числом. Поэтому математически обоснованное упорядочение невозможно. Поскольку в стандарте SQL не указано, как следует относиться к значениям NULL при сортировке результатов, правильное упорядочение не определено. Каждая база данных должна была придумать свой собственный порядок. В MySQL при сортировке по возрастанию любое NULL-значение будет располагаться перед всеми, а в PostgreSQL — последним. Оба подхода представляют собой различные решения для определения детерминированного упорядочения, которое может использовать база данных.

Однако эти направления сортировки, характерные для конкретной базы данных, могут не удовлетворять требованиям приложения. Для улучшения пользовательского опыта благоприятно размещение пустых значений в начале или в конце. Пользователь, сортирующий список фамилий по возрастанию или убыванию, не будет заинтересован в том, чтобы при поиске конкретной записи сначала отображались пустые значения. К отображению списка статей блога в бэкенде, напротив, будут предъявляться другие требования. Сначала должны быть перечислены неопубликованные статьи, затем все опубликованные статьи в порядке убывания, чтобы первыми были видны самые новые.

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

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

Дополнительные ресурсы

• MySQL Documentation: How to Work with NULL Values and the ordering implications.
• PostgreSQL Documentation: How to Sort Rows (ORDER BY) and handle NULL values.