Dlaczego nie mogę używać wartości null w złączeniach?

Rozwiązałem problem zapytania za pomocą ... row_number() over (partition by... to jest bardziej ogólne pytanie, dlaczego nie możemy używać kolumn z wartościami null w złączeniach. Dlaczego null nie może być równy null ze względu na połączenie?

Dlaczego null nie może być równy null ze względu na połączenie?

Po prostu powiedz to Oracle:

select *
from one t1 
  join two t2 on coalesce(t1.id, -1) = coalesce(t2.id, -1);

(Zauważ, że w standardowym SQL można użyć, t1.id is not distinct from t2.idaby uzyskać zerowy bezpieczny operator równości, ale Oracle tego nie obsługuje)

Ale to zadziała tylko wtedy, gdy wartość zastępcza (-1 w powyższym przykładzie) nie pojawi się w tabeli. Znalezienie takiej „magicznej” wartości liczb może być możliwe, ale będzie bardzo trudne dla wartości postaci (zwłaszcza, że ​​Oracle traktuje również pusty ciąg znaków null)

Plus: nie idbędzie używany indeks w kolumnach (można jednak zdefiniować indeks oparty na funkcji za pomocą coalesce()wyrażenia).

Inna opcja, która działa dla wszystkich typów, bez wartości magicznych:

              on t1.id = t2.id or (t1.id is null and t2.id is null)

Ale prawdziwe pytanie brzmi: czy to ma sens?

Rozważ następujące przykładowe dane:

Tabela pierwsza

id
----
1
2
(null)
(null)

Tabela druga

id
----
1
2
(null)
(null)
(null)

Którą z kombinacji wartości zerowych należy wybrać w złączeniu? Mój powyższy przykład spowoduje coś w rodzaju połączenia krzyżowego dla wszystkich wartości zerowych.

T1_ID  | T2_ID 
-------+-------
     1 |      1
     2 |      2
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)
(null) | (null)

Alternatywnie możesz ustawić dwie wartości null pasujące do siebie za INTERSECTpomocą operatora równości:

SELECT
  *
FROM
  t1
  INNER JOIN t2
    ON EXISTS (SELECT t1.ID FROM DUAL INTERSECT SELECT t2.ID FROM DUAL)
;

Zobacz to demo DBFiddle jako ilustrację.

Oczywiście wygląda to dość kęsowo, chociaż tak naprawdę nie jest to dużo dłużej niż sugestia BriteSponge . Jednak na pewno nie pasuje, jeśli wybaczysz kalambur, zwięzłość wspomnianego wcześniej w komentarzach standardowego sposobu, który jest IS NOT DISTINCT FROMoperatorem, który nie jest jeszcze obsługiwany w Oracle.

Dla kompletności wspomnę, że tej funkcji SYS_OP_MAP_NONNULLmożna teraz bezpiecznie używać do porównywania wartości zerowych, ponieważ jest to teraz udokumentowane w dokumentacji 12c. Oznacza to, że Oracle nie usunie go losowo i nie zepsuje kodu.

SELECT *
FROM   one t1 
       JOIN two t2
         ON SYS_OP_MAP_NONNULL(t1.id) = SYS_OP_MAP_NONNULL(t2.id)

Zaletą jest to, że nie natrafisz na problem z „magiczną” liczbą.

Odniesienia w dokumentach Oracle znajdują się w Podstawowych widokach zmaterializowanych - Wybieranie indeksów dla widoków zmaterializowanych .

Możesz dołączyć wartości zerowe za pomocą dekodowania:

on decode(t1.id, t2.id, 1, 0) = 1

decodetraktuje wartości zerowe jako równe, więc działa to bez „magicznych” liczb. Dwie kolumny muszą mieć ten sam typ danych.

Nie stworzy najbardziej czytelnego kodu, ale prawdopodobnie nadal lepszy niż t1.id = t2.id or (t1.id is null and t2.id is null)

Dlaczego nie możesz użyć wartości null w złączeniach? W Oracle oba poniższe nie mają wartości true:

• NULL = NULL
• NULL <> NULL

Dlatego musimy IS NULL/ IS NOT NULLsprawdzać wartości zerowe.
Aby to przetestować, możesz po prostu:

SELECT * FROM table_name WHERE NULL = NULL

Złącza oceniają warunek logiczny i nie zaprogramowali ich do działania inaczej. Możesz wprowadzić znak „więcej niż” w warunku łączenia i dodać inne warunki; po prostu ocenia to jako wyrażenie boolowskie.

Wydaje mi się, że null nie może być równy null w połączeniach ze względu na spójność. Byłoby to sprzeczne ze zwykłym zachowaniem operatora porównania.

Wartość null w większości relacyjnych baz danych jest uważana za NIEZNANY. Nie należy go mylić ze wszystkimi zerami HEX. jeśli coś zawiera null (nieznane), nie można tego porównać.

Unknown = Known False
Unknown = Unknown False
Unknown >= Known False
Known >= Unknown False

Co oznacza, że ​​za każdym razem, gdy masz null jako operand w wyrażeniu logicznym, część else zawsze będzie prawdziwa.

W przeciwieństwie do ogólnej nienawiści deweloperów do wartości zerowej, null ma swoje miejsce. Jeśli coś jest nieznane, użyj null.