Dlaczego wartości NULL są najpierw sortowane?


20

Dlaczego jest tak, że gdy mamy wartość NULL w kolumnie i sortujemy według wartości rosnąco, wartości NULL są najpierw sortowane?

select 1 as test
union all
select 2
union all
select NULL
union all
select 3
union all
select 4
order by test

prowadzi do

NULL
1
2
3
4

Ciągle myślę, że NULL oznacza „Nieokreślony” lub możliwy „Nieznany”. Jeśli to prawda, czy nie posortują na końcu, ponieważ wartość może być większa niż wszystkie inne wartości? (Czy jest to gdzieś opcja sortowania?)

Korzystam z SQL Server 2008R2, ale podejrzewam, że dotyczy to wszystkich serwerów SQL i prawdopodobnie wszystkich RDBMS.


1
Oracle wymienia to na końcu. To mnie raz spieprzyło, uważając, że powinien zachowywać się jak SQL Server.
Andrei Rînea

2
„Jeśli to prawda, czy nie posortują na końcu, ponieważ wartość może być większa niż wszystkie inne wartości”. Wartość może być mniejsza niż wszystkie inne wartości. Dla mnie intuicyjne jest to, że wartość falsey taka jak null powinna znajdować się na dolnym końcu. I praktyczne, ponieważ w praktyce często chcesz użyć desckolejności, aby pokazać największe lub najnowsze rzeczy, w takim przypadku byłbym zadowolony, gdyby rzeczy zerowe były ostatnie.
mahemoff,

Baza danych robi to, co jej nakazujesz. Jeśli wiesz, że Twoje dane zawierają wartości null i masz jakiś biznesowy powód, aby sortować dane w określony sposób, musisz to określić w zapytaniu lub w kodzie / widoku, który przetwarza / wyświetla dane. Nigdy nie pozostawiaj sortowania domyślnego zachowania bazy danych.
nic nie jest potrzebne

Odpowiedzi:


19

BOL : Wartość NULL wskazuje, że wartość jest nieznana. Wartość NULL różni się od wartości pustej lub zerowej. Żadne dwie wartości zerowe nie są równe. Porównania między dwiema wartościami null lub między wartością NULL a dowolną inną wartością zwracają wartość nieznaną, ponieważ wartość każdej wartości NULL jest nieznana.

NULL oznacza nieznany. Żadna inna interpretacja nie jest ważna.

Jeśli to prawda, czy nie posortują na końcu, ponieważ wartość może być większa niż wszystkie inne wartości?

Nie może być . Nie ma żadnej potencjalnej wartości. Nieznany jest nieznany jest nieznany.

Jeśli chodzi o to, dlaczego pojawia się jako pierwsza, a nie ostatnia, nie jest to uwzględnione w opublikowanych standardach SQL i niestety jest pozostawione do uznania dostawcy RDBMS:

Wikipedia : Standard SQL nie definiuje jawnie domyślnej kolejności sortowania dla wartości Null. Zamiast tego w systemach zgodnych wartości Null można sortować przed lub po wszystkich wartościach danych, używając odpowiednio klauzul NULLS FIRST lub NULLS LAST z listy ORDER BY. Jednak nie wszyscy dostawcy DBMS wdrażają tę funkcję. Dostawcy, którzy nie implementują tej funkcji, mogą określić różne sposoby sortowania zerowego w DBMS.


To jest wyrok sądu. To ma sens. Dzięki!
Richard

6

Masz rację, co NULLmoże oznaczać „Nieokreślony” lub „Nieznany” lub „Jeszcze nie wiadomo” lub „Nie składać wniosku”. Ale nie ma powodu, aby stawiać Nulls na pierwszym miejscu lub na końcu. Jeśli nie znamy rzeczywistych wartości, to może być mały lub duży.

Myślę, że standardem określającym pożądane zachowanie wartości Null podczas sortowania jest:

ORDER BY 
    test NULLS LAST                      --- or NULLS FIRST for the opposite

Niestety SQL Server nie przyjął jeszcze tej składni. Jeśli się nie mylę, PostgreSQL i Oracle mają to.

Jedno rozwiązanie:

ORDER BY 
     CASE WHEN test IS NOT NULL 
            THEN 0 
          ELSE 1 
     END 
   , test

Kolejne rozwiązanie, które wymaga korekty w zależności od typu danych - ale nie będzie dobrze działać, ponieważ nie można użyć indeksu na (test):

ORDER BY 
    COALESCE(test, 2147483647)               --- if it's a 4-byte signed integer

W ten sposób ORDER BY COALESCE (test, 2147483647) Serwer SQL nie może korzystać z indeksu.
Ardalan Shahgholi

3

Nie wiem, dlaczego tak się dzieje, ale z definicji NULLS nie może być porównywany z nie-NULLS, więc albo muszą iść na początku, albo na końcu (odpowiedź Marka opisuje to bardziej szczegółowo).

Aby uzyskać pożądane zachowanie - O ile mi wiadomo, nie ma opcji sortowania, aby pozostawić wartości zerowe na końcu, więc musisz je wyeliminować za pomocą kolumny obliczeniowej, aby wymusić je na końcu. Jednak w SQL Server nie można sortować według kolumny obliczeniowej ( CASE WHEN ...), gdy dane zawierają operator ustawiania ( UNION ALL). Więc:

CREATE TABLE #sorttest(test int)
INSERT INTO #sorttest values(1)
INSERT INTO #sorttest values(5)
INSERT INTO #sorttest values(4)
INSERT INTO #sorttest values(NULL)
INSERT INTO #sorttest values(3)
INSERT INTO #sorttest values(2)
SELECT test
FROM #sorttest
ORDER BY CASE WHEN test IS NULL THEN 1 ELSE 0 END, test

DROP TABLE #sorttest

Działa do sortowania wartości zerowych na końcu. Jeśli musisz użyć UNION(lub EXCEPTlub INTERSECTS) do wygenerowania zestawu danych, zrzuć dane do tymczasowej tabeli, jak wyżej.


... lub użyj danych wyjściowych UNIONed jako tabeli pochodnej.
Andriy M

0

Jeśli masz do czynienia z liczbami, możesz także użyć

ORDER BY -test DESC

NULLsą najniższymi możliwymi wartościami, dlatego DESCumieszcza je na końcu. W międzyczasie wartości niepuste mają odwrócony znak, więc w DESCrzeczywistości jest to ASCwartość rzeczywista. Powinno to być szybsze niż CASEi przypuszczam, że optymalizator zapytań może również używać indeksów w testkolumnie.


3
Nie, nie można użyć indeksu do sortowania. Chyba że masz indeks obliczonego wyrażenia (- test).
ypercubeᵀᴹ

1
Sprytne, choć ograniczone tylko do danych liczbowych (i tak odpowiednie dla przykładu OP). Nie jestem pewien, czy rzeczywiście byłoby to szybsze niż użycie CASE, ale jestem pewien, że nie użyłby indeksu (chyba że tak mówi @ ypercubeᵀᴹ - ale wtedy wyrażenie CASE mogłoby być indeksowane dokładnie w ten sam sposób).
Andriy M
Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.