Jaka jest różnica między NOT EXISTS vs. NOT IN vs. LEFT JOIN gdzie jest NULL?

Wydaje mi się, że możesz zrobić to samo w zapytaniu SQL używając NOT EXISTS, NOT IN lub LEFT JOIN gdzie jest 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 mam poprawną składnię, ale to są ogólne techniki, które widziałem. Dlaczego miałbym używać jednego nad drugim? Czy Wydajność się różni...? Który z nich jest najszybszy / najskuteczniejszy? (Jeśli zależy to od implementacji, to kiedy miałbym użyć każdej z nich?)

 152
sql
Author: froadie, 2010-02-11
Source

5 answers

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

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

  • NOT in vs. NOT EXISTS vs. LEFT JOIN / IS NULL: Oracle

  • NOT in vs. NOT EXISTS vs. LEFT JOIN / IS NULL: MySQL

W skrócie:

NOT IN jest trochę inny: nigdy nie pasuje, jeśli tam jest tylko jednym NULL na liście.

  • W MySQL, NOT EXISTS jest trochę mniej wydajny

  • W SQL Server, LEFT JOIN / IS NULL jest mniej wydajny

  • W PostgreSQL, NOT IN jest mniej wydajny

  • W Oracle wszystkie trzy metody są takie same.

 142
Author: Quassnoi,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-02-11 18:42:38

Jeśli baza danych jest dobra w optymalizacji zapytania, dwa pierwsze zostaną przekształcone w coś podobnego do trzeciego.

W prostych sytuacjach, takich jak te w twoim pytaniu, nie powinno być żadnej różnicy, ponieważ wszystkie będą wykonywane jako łączniki. W bardziej złożonych zapytaniach baza danych może nie być w stanie wykonać połączenia z not in i not exists zapytań. W takim przypadku zapytania będą znacznie wolniejsze. Z drugiej strony, join może również działać źle, jeśli nie ma indeksu, który może być używany, więc tylko dlatego, że używasz join nie oznacza, że jesteś bezpieczny. Będziesz musiał sprawdzić plan wykonania zapytania, aby stwierdzić, czy mogą wystąpić problemy z wydajnością.

 5
Author: Guffa,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-02-11 18:52:15

Zakładając, że unikasz null, są to wszystkie sposoby pisaniaanty-join przy użyciu standardowego SQL.

Oczywistym pominięciem jest odpowiednik EXCEPT: 

SELECT a FROM table1
EXCEPT
SELECT a FROM table2

Uwaga W Oracle musisz użyć operatora MINUS (prawdopodobnie lepsza nazwa): 

SELECT a FROM table1
MINUS
SELECT a FROM table2

Mówiąc o składni własnościowej, mogą istnieć również niestandardowe odpowiedniki warte zbadania w zależności od używanego produktu, np. OUTER APPLY w SQL serverze (coś w tym stylu): 

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;
 2
Author: onedaywhen,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-07-07 10:12:33

Kiedy trzeba wstawić dane do tabeli za pomocą klucza podstawowego wielolodowego, należy wziąć pod uwagę, że będzie znacznie szybciej (próbowałem w Access, ale myślę, że w jakiejkolwiek bazie danych) nie sprawdzać, czy "nie ma rekordów z 'takimi' wartościami w tabeli",-raczej po prostu wstawić do tabeli, a nadmiar rekordów (za pomocą klucza) nie zostanie wstawiony dwa razy.

 0
Author: baleks,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-01-23 12:46:17

Perspektywa wydajności zawsze unikaj używania odwrotnych słów kluczowych, takich jak NOT in, NOT EXISTS,... Ponieważ, aby sprawdzić elementy odwrotne, DBMS musi przejść przez wszystkie dostępne i upuścić odwrotne zaznaczenie.

 0
Author: Lahiru Cooray,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-03-12 10:16:42