Сортировка и NULL-значения

Если столбец, по которому выполняется сортировка, содержит NULL-значения, то при использовании SQL Server следует иметь в виду, что при сортировке по возрастанию NULL-значения будут идти в начале списка, а при сортировке по убыванию - в конце.

Поскольку в доступных в учебнике базах NULL-значения отсутствуют в имеющихся данных (которые согласованы с данными в открытых базах, используемых на сайте sql-ex.ru), была создана копия таблицы PC с именем PC_, в которую добавлена строка, содержащая NULL в столбце price:

insert into PC_
values(13,2112,600,64,8,'24x',NULL);

Следует отметить, что это не противоречит схеме данных.

Теперь вы сами можете убедиться в сказанном, выполнив пару приведенных ниже запросов.

select * from PC_ order by price;

select * from PC_ order by price DESC;

Почему это важно? Дело в том, что при поиске экстремальных значений часто используют метод, основанный на сортировке. Рассмотрим, например, такую задачу.

Иногда эту задачу решают следующим образом:

select top 1 with ties model
from PC_
order by price;

Конструкция WITH TIES используется для того, чтобы вывести все модели с наименьшей ценой, если их окажется несколько. Однако в результате мы получим модель 2112, цена которой неизвестна, в то время как должны получить модели 1232 и 1260, имеющих действительно минимальные цены. Мы их и получим, если исключим из рассмотрения модели с неизвестными ценами:

select top 1 with ties model
from PC_
where price is not null
order by price;

Но тут появляется еще одна проблема, связанная с дубликатами. Поскольку есть два ПК модели 1232 с минимальной ценой, то обе они будут выводиться в результирующем наборе. DISTINCT без указания в списке столбцов предложения SELECT тех, по которым выполняется сортировка, использовать мы не можем, о чем и сообщает ошибка, если мы попытаемся это сделать

select DISTINCT top 1 with ties model
from PC_
where price is not null
order by price;

Чтобы получить решение в требуемом виде, мы можем добавить price в список выводимых столбцов, а потом использовать полученный запрос в качестве подзапроса. Итак,

select model 
from (select DISTINCT top 1 with ties model, price
      from PC_
      where price is not null
      order by price
     ) X;

select DISTINCT model 
from PC_
where price = (select min(price) from PC_);

Заметим также, что это стандартное решение будет работать под любыми СУБД, т.к. не использует специфических особенностей диалекта.

А как, кстати, обстоят дела с использованием метода на основе сортировки в других СУБД?

В MySQL мы можем использовать DISTINCT без обязательного указания в списке SELECT столбцов, по которым выполняется сортировка. Однако здесь нет аналога конструкции WITH TIES, чтобы решить задачу максимально просто.

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

select DISTINCT model 
from PC_
where price = (select price
               from PC_ 
               where price is not null 
               order by price limit 1
              );

Такое же решение будет работать и в PostgreSQL, однако оно имеет одну особенность, о которой полезно знать. А именно, при сортировке можно указать, где будут выводиться NULL-значения - в начале или в конце результирующего набора. Нам для решения задачи требуется, чтобы NULL выводились в конце отсортированного списка. Тогда не придется выполнять лишнюю операцию по отфильтровыванию NULL-значений:

select DISTINCT model 
from PC_
where price = (select price 
               from PC_ 
               order by price nulls last limit 1
              );

Кстати, при сортировке по возрастанию NULL-значения в PostgreSQL идут в конце результирующего рабора. Поэтому конструкция NULLS LAST, которую мы использовали выше, можно опустить при решении нашей задачи:

select DISTINCT model 
from PC_
where price = (select price 
               from PC_ 
               order by price limit 1
              );

Для того чтобы NULL-значения шли в начале результирующего набора при выполнении сортировки, нужно написать NULLS FIRST.

К слову, мы можем смоделировать в MySQL использование конструкций NULLS FIRST/LAST. Для этого воспользуемся тем фактом, что значения логического типа в этой СУБД представляют собой TINYINT(1). Конкретно это означает, что 0 соответствует истинностному значению FALSE (ложь), а ненулевое значение эквивалентно TRUE (истина). При этом логическое выражение, оцениваемое как TRUE будет представлено единицей, т.е.

select a IS NULL as a, b IS NULL as b 
from (select NULL as a, 1 as b) x;

Учитывая то, что 0 при сортировке по возрастанию идет раньше, чем 1, мы можем решение для PostgreSQL адаптировать для MySQL:

SELECT DISTINCT model 
FROM PC_
WHERE price = (SELECT price 
               FROM PC_ 
               ORDER BY price is NULL, price LIMIT 1
              );

Oracle, как и PostgreSQL, при сортировке по возрастанию помещает NULL-значения в конец результирующего набора. Здесь также имеют место конструкции NULLS FIRST/LAST, но отсутствует аналог LIMIT/TOP N для ограничения количества выводимых строк.

Чтобы смоделировать в Oracle использованный выше подход к решению задачи, можно воспользоваться встроенной функцией ROWNUM. Эта функция нумерует строки, но делает это она после выполнения предложений FROM и WHERE, т.е. перед предложениями SELECT и ORDER BY. Такое поведение иллюстрирует результат следующего запроса:

select code, model,price, ROWNUM rn 
from PC_ 
ORDER BY price;

Как видно, номер строки не соответствует порядку сортировки. Нетрудно убедиться в том, что нумерация выполнена в соответствии со столбцом code. Это объясняется тем, что оптимизатор использует индекс по этому столбцу при выполнении запроса.

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

SELECT price 
FROM (select model,price 
      from PC_ 
      ORDER BY price
     ) X
WHERE ROWNUM = 1;

Теперь, как и в случае MySQL и PostgreSQL, будем использовать этот запрос для получения моделей, которые продаются по цене, найденной с его помощью:

select DISTINCT model 
from PC_ 
where price = (SELECT price 
               FROM (select model,price 
                     from PC_ 
                      ORDER BY price
                     ) X
                WHERE ROWNUM = 1
               );

Как говорил царь Соломон, от многой мудрости много скорби, и умножающий знание умножает печаль.

Используйте стандартные решения, сказал бы я. :-)

В заключение не могу не сказать о способе, использующем ранжирующие функции.

Идея решения cостоит в ранжировании (функция RANK) строк по возрастанию цены и выборке (уникальных) строк, для которых ранг равен 1. Чтобы запрос работал под всеми СУБД, которые поддерживают оконные функции, этот алгоритм можно записать следующим образом:

SELECT DISTINCT model 
FROM (select model, Rank() OVER (ORDER BY price) rn 
      from PC_
      WHERE price IS NOT NULL
     ) X 
WHERE rn =1;

Тот факт, что при сортировке по возрастанию NULL-значения идут в начале (SQL Server), можно использовать в “полезных целях”.

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

Здесь весьма кстати пригодится функция NULLIF(town_from,‘Rostov’), которая будет возвращать NULL, если городом отправления является ‘Rostov’.

Задачу решает следующий запрос:

SELECT trip_no, town_from, town_to
FROM Trip
ORDER BY NULLIF(town_from,'Rostov'), trip_no;