Jaka jest różnica między NOT EXISTS a NOT IN a LEFT JOIN WHERE IS NULL?

Wydaje mi się, że możesz zrobić to samo w zapytaniu SQL, używając opcji NOT EXISTS, NOT IN lub LEFT JOIN WHERE IS NULL. Na przykład:

SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)

SELECT a FROM table1 WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.a = table2.a)

SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL

Nie jestem pewien, czy cała składnia jest poprawna, ale są to ogólne techniki, które widziałem. Dlaczego miałbym wybrać jeden nad drugim? Czy wydajność się różni ...? Który z nich jest najszybszy / najbardziej wydajny? (Jeśli to zależy od implementacji, kiedy użyłbym każdego z nich?)

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

• NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: PostgreSQL

• NIE W kontra NIE ISTNIEJE kontra LEFT JOIN / IS NULL: Oracle

• NIE W kontra NIE ISTNIEJE kontra LEFT JOIN / IS NULL: MySQL

W skrócie:

NOT INjest trochę inna: nigdy nie pasuje, jeśli jest tylko jeden NULLna liście.

• W MySQL, NOT EXISTSjest trochę mniej wydajny

• W SQL Server, LEFT JOIN / IS NULLjest mniej wydajny

• W PostgreSQL, NOT INjest mniej wydajny

• W programie Oraclewszystkie trzy metody są takie same.

Jeśli baza danych dobrze optymalizuje zapytanie, dwie pierwsze zostaną przekształcone w coś zbliżonego do trzeciej.

W przypadku prostych sytuacji, takich jak te, o których mowa, różnica powinna być niewielka lub żadna, ponieważ wszystkie zostaną wykonane jako łączenia. W przypadku bardziej złożonych zapytań baza danych może nie być w stanie wykonać sprzężenia z zapytań not ini not exists. W takim przypadku zapytania będą znacznie wolniejsze. Z drugiej strony, złączenie może również źle działać, jeśli nie ma indeksu, którego można by użyć, więc samo użycie złączenia nie oznacza, że ​​jesteś bezpieczny. Będziesz musiał zbadać plan wykonania zapytania, aby stwierdzić, czy mogą wystąpić problemy z wydajnością.

Zakładając, że unikasz wartości null, wszystkie one są sposobami zapisywania anty-złączeń przy użyciu standardowego języka SQL.

Oczywistym pominięciem jest użycie EXCEPT:

SELECT a FROM table1
EXCEPT
SELECT a FROM table2

Uwaga w Oracle musisz użyć MINUSoperatora (prawdopodobnie lepszej nazwy):

SELECT a FROM table1
MINUS
SELECT a FROM table2

Mówiąc o zastrzeżonej składni, mogą istnieć również niestandardowe odpowiedniki, które warto zbadać w zależności od produktu, z którego korzystasz, np. OUTER APPLYW SQL Server (coś takiego):

SELECT t1.a
  FROM table1 t1
       OUTER APPLY 
       (
        SELECT t2.a
          FROM table2 t2
         WHERE t2.a = t1.a
       ) AS dt1
 WHERE dt1.a IS NULL;

Kiedy trzeba wstawić dane do tabeli z kluczem podstawowym z wieloma polami, weź pod uwagę, że znacznie szybciej (próbowałem w programie Access, ale myślę, że w dowolnej bazie danych) nie będzie sprawdzać, czy „nie istnieje rekordy z„ takimi ”wartościami w tabeli”, - raczej po prostu wstaw do tabeli, a nadmiar rekordów (wg klucza) nie zostanie wstawiony dwukrotnie.

Perspektywa wydajności zawsze unikaj używania odwrotnych słów kluczowych, takich jak NIE W, NIE ISTNIEJE, ... Ponieważ aby sprawdzić odwrotne elementy, DBMS musi przejść przez wszystkie dostępne i porzucić odwrotny wybór.