NIE IN vs NIE ISTNIEJE

Które z tych zapytań jest szybsze?

NIE ISTNIEJE:

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE NOT EXISTS (
    SELECT 1 
    FROM Northwind..[Order Details] od 
    WHERE p.ProductId = od.ProductId)

Lub NIE W:

SELECT ProductID, ProductName 
FROM Northwind..Products p
WHERE p.ProductID NOT IN (
    SELECT ProductID 
    FROM Northwind..[Order Details])

Plan wykonania zapytania mówi, że oba robią to samo. Jeśli tak jest, jaka jest zalecana forma?

Jest to oparte na bazie danych NorthWind.

[Edytować]

Właśnie znalazłem ten pomocny artykuł: http://weblogs.sqlteam.com/mladenp/archive/2007/05/18/60210.aspx

Myślę, że pozostanę przy NOT NOTIST.

Zawsze domyślnie NOT EXISTS.

Plany wykonania mogą być w tej chwili takie same, ale jeśli którakolwiek kolumna zostanie zmieniona w przyszłości, aby umożliwić NULLs, NOT INwersja będzie musiała wykonać więcej pracy (nawet jeśli NULLw danych faktycznie nie ma żadnych s) i semantykę NOT INjeśli NULLs są obecne i tak prawdopodobnie nie będą tymi, których chcesz.

Kiedy ani Products.ProductIDczy [Order Details].ProductIDzezwolić NULLS NOT INbędą traktowane identycznie z poniższym zapytaniu.

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId) 

Dokładny plan może się różnić, ale dla moich przykładowych danych otrzymuję następujące informacje.

Wydaje się, że dość powszechnym nieporozumieniem jest to, że skorelowane zapytania są zawsze „złe” w porównaniu do złączeń. Z pewnością mogą się zdarzyć, gdy wymuszą plan zagnieżdżonych pętli (zapytanie podrzędne oceniane wiersz po rzędzie), ale ten plan zawiera operator logiczny anty semi-join. Sprzężenia anty semi nie są ograniczone do zagnieżdżonych pętli, ale mogą również używać sprzężenia mieszającego lub scalania (jak w tym przykładzie).

/*Not valid syntax but better reflects the plan*/ 
SELECT p.ProductID,
       p.ProductName
FROM   Products p
       LEFT ANTI SEMI JOIN [Order Details] od
         ON p.ProductId = od.ProductId 

Jeśli [Order Details].ProductIDjest możliwe, NULLzapytanie staje się

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL) 

Powodem tego jest to, że poprawna semantyka, jeśli [Order Details]zawiera jakieś NULL ProductIds, nie zwraca żadnych wyników. Zobacz dodatkową szpulę anty semi-join i szpulę zliczania wierszy, aby sprawdzić, czy została dodana do planu.

Jeśli Products.ProductIDzostanie również zmieniony, aby stał NULLsię dostępny, zapytanie zostanie zmienione

SELECT ProductID,
       ProductName
FROM   Products p
WHERE  NOT EXISTS (SELECT *
                   FROM   [Order Details] od
                   WHERE  p.ProductId = od.ProductId)
       AND NOT EXISTS (SELECT *
                       FROM   [Order Details]
                       WHERE  ProductId IS NULL)
       AND NOT EXISTS (SELECT *
                       FROM   (SELECT TOP 1 *
                               FROM   [Order Details]) S
                       WHERE  p.ProductID IS NULL) 

Powodem tego jest to, że a NULL Products.ProductIdnie powinno być zwracane w wynikach, chyba że NOT INzapytanie podrzędne nie zwróci w ogóle żadnych wyników (tj. [Order Details]Tabela jest pusta). W takim przypadku powinien. W planie dla moich przykładowych danych jest to realizowane poprzez dodanie kolejnego anty-semi-join jak poniżej.

Efekt tego jest pokazany w poście na blogu, do którego już linkował Buckley . W tym przykładzie liczba odczytów logicznych wzrasta z około 400 do 500 000.

Dodatkowo fakt, że jeden NULLmoże zmniejszyć liczbę wierszy do zera, bardzo utrudnia oszacowanie liczności. Jeśli SQL Server zakłada, że ​​tak się stanie, ale w rzeczywistości nie ma NULLwierszy w danych, reszta planu wykonania może być katastrofalnie gorsza, jeśli jest to tylko część większego zapytania, z niewłaściwymi zagnieżdżonymi pętlami powodującymi powtarzające się wykonywanie drogiego podrzędnego drzewo na przykład .

Nie jest to jedyny możliwy plan wykonania dla NOT INna NULLkolumnie -able jednak. W tym artykule pokazano kolejne zapytanie dotyczące AdventureWorks2008bazy danych.

Albowiem NOT INna NOT NULLkolumnie lub NOT EXISTSna jednej z wartości pustych lub braku wartości pustych kolumnie daje następujący plan.

Kiedy kolumna zmieni się na NULL-able, NOT INplan wygląda teraz

Dodaje dodatkowy wewnętrzny operator łączenia do planu. To urządzenie jest wyjaśnione tutaj . Wszystko po to, aby przekonwertować poprzednie wyszukiwanie pojedynczego skorelowanego indeksu na Sales.SalesOrderDetail.ProductID = <correlated_product_id>dwa wyszukiwania na zewnętrzny wiersz. Dodatkowy jest włączony WHERE Sales.SalesOrderDetail.ProductID IS NULL.

Ponieważ jest to pod łączeniem anty semi, jeśli ten zwróci jakiekolwiek wiersze, drugie wyszukiwanie nie nastąpi. Jeśli jednak Sales.SalesOrderDetailnie zawiera żadnych NULL ProductID, podwoi liczbę wymaganych operacji wyszukiwania.

Należy również pamiętać, że wartość NOT IN nie jest równoważna wartości NOT EXISTS, jeśli chodzi o wartość null.

Ten post wyjaśnia to bardzo dobrze

http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Gdy podzapytanie zwróci choćby jeden null, NOT IN nie będzie pasował do żadnych wierszy.

Przyczynę tego można znaleźć, przyglądając się szczegółom tego, co tak naprawdę oznacza operacja NOT IN.

Powiedzmy, dla celów ilustracji, że w tabeli są 4 wiersze o nazwie t, istnieje kolumna o nazwie ID o wartościach 1..4

WHERE SomeValue NOT IN (SELECT AVal FROM t)

jest równa

WHERE SomeValue != (SELECT AVal FROM t WHERE ID=1)
AND SomeValue != (SELECT AVal FROM t WHERE ID=2)
AND SomeValue != (SELECT AVal FROM t WHERE ID=3)
AND SomeValue != (SELECT AVal FROM t WHERE ID=4)

Powiedzmy dalej, że AVal ma wartość NULL, gdzie ID = 4. Stąd, że! = Porównanie zwraca wartość NIEZNANE. Logiczna tabela prawdy dla AND wskazuje, że NIEZNANY i PRAWDA to NIEZNANY, NIEZNANY, a FAŁSZ jest FAŁSZ. Nie ma wartości, która mogłaby być AND z UNKNOWN, aby uzyskać wynik PRAWDA

Dlatego jeśli jakikolwiek wiersz tego podzapytania zwróci NULL, cały operator NOT IN oceni na FALSE lub NULL i żadne rekordy nie zostaną zwrócone

Jeśli planista wykonania powie, że są tacy sami, są tacy sami. Użyj tego, który sprawi, że twoja intencja stanie się bardziej oczywista - w tym przypadku drugiego.

Właściwie uważam, że byłby to najszybszy:

SELECT ProductID, ProductName 
    FROM Northwind..Products p  
          outer join Northwind..[Order Details] od on p.ProductId = od.ProductId)
WHERE od.ProductId is null

Mam tabelę, która ma około 120 000 rekordów i muszę wybrać tylko te, które nie istnieją (dopasowane z kolumną varchar) w czterech innych tabelach z liczbą wierszy około 1500, 4000, 40000, 200. Wszystkie zaangażowane tabele mają unikalny indeks w odpowiedniej Varcharkolumnie.

NOT INzajęło około 10 minut, NOT EXISTSzajęło 4 sekundy.

Mam zapytanie rekurencyjne, które mogło mieć jakąś niezostrojoną sekcję, która mogła przyczynić się do 10 minut, ale inna opcja trwająca 4 sekundy wyjaśnia, przynajmniej dla mnie, że NOT EXISTSjest o wiele lepsza lub przynajmniej takaIN i EXISTSnie jest dokładnie taka sama i zawsze warta sprawdź przed użyciem kodu.

W twoim przykładzie są one takie same, ponieważ optymalizator stwierdził, że to, co próbujesz zrobić, jest takie samo w obu przykładach. Ale możliwe jest, że w nietrywialnych przykładach optymalizator może tego nie zrobić, a w takim przypadku istnieją powody, aby czasami preferować jeden od drugiego.

NOT INpowinno być preferowane, jeśli testujesz wiele wierszy w zewnętrznym zaznaczeniu. Podzapytanie wewnątrz NOT INinstrukcji można ocenić na początku wykonywania, a tabelę tymczasową można porównać z każdą wartością w zewnętrznym zaznaczeniu, zamiast ponownie uruchamiać podselekcję za każdym razem, jak byłoby to wymagane w przypadkuNOT EXISTS instrukcji.

Jeśli podkwerenda musi być skorelowana z zewnętrznym wyborem, NOT EXISTSmoże być preferowane, ponieważ optymalizator może odkryć uproszczenie, które zapobiega tworzeniu jakichkolwiek tabel tymczasowych w celu wykonania tej samej funkcji.

Używałem

SELECT * from TABLE1 WHERE Col1 NOT IN (SELECT Col1 FROM TABLE2)

i okazało się, że daje złe wyniki (Przez zło mam na myśli brak wyników). Ponieważ w TABLE2.Col1 była wartość NULL.

Podczas zmiany zapytania na

SELECT * from TABLE1 T1 WHERE NOT EXISTS (SELECT Col1 FROM TABLE2 T2 WHERE T1.Col1 = T2.Col2)

dało mi prawidłowe wyniki.

Od tego czasu zacząłem używać NOT EXISTS wszędzie.

Są bardzo podobne, ale tak naprawdę nie są takie same.

Pod względem wydajności stwierdziłem, że lewe sprzężenie jest bardziej efektywne w przypadku wyrażenia zerowego (kiedy należy wybrać dużą liczbę wierszy)

Jeśli optymalizator twierdzi, że są takie same, rozważ czynnik ludzki. Wolę zobaczyć NIE ISTNIEJE :)

To bardzo dobre pytanie, dlatego postanowiłem napisać bardzo szczegółowy artykuł na ten temat na moim blogu.

Model tabeli bazy danych

Załóżmy, że mamy w naszej bazie danych następujące dwie tabele, które tworzą relację jeden do wielu.

studentStół jest rodzicem, astudent_grade to tabela dziecko, ponieważ ma to student_id kolumny klucz obcy odniesienia do identyfikatora kolumny klucza podstawowego w tabeli studentów.

student tableZawiera następujące dwa rekordy:

| id | first_name | last_name | admission_score |
|----|------------|-----------|-----------------|
| 1  | Alice      | Smith     | 8.95            |
| 2  | Bob        | Johnson   | 8.75            |

, A student_gradetabela przechowuje klas otrzymali uczniowie:

| id | class_name | grade | student_id |
|----|------------|-------|------------|
| 1  | Math       | 10    | 1          |
| 2  | Math       | 9.5   | 1          |
| 3  | Math       | 9.75  | 1          |
| 4  | Science    | 9.5   | 1          |
| 5  | Science    | 9     | 1          |
| 6  | Science    | 9.25  | 1          |
| 7  | Math       | 8.5   | 2          |
| 8  | Math       | 9.5   | 2          |
| 9  | Math       | 9     | 2          |
| 10 | Science    | 10    | 2          |
| 11 | Science    | 9.4   | 2          |

ISTNIEJE SQL

Powiedzmy, że chcemy, aby wszyscy uczniowie, którzy otrzymali 10 klas z matematyki.

Jeśli interesuje nas tylko identyfikator studenta, możemy uruchomić zapytanie takie jak to:

SELECT
    student_grade.student_id
FROM
    student_grade
WHERE
    student_grade.grade = 10 AND
    student_grade.class_name = 'Math'
ORDER BY
    student_grade.student_id

Ale aplikacja jest zainteresowana wyświetlaniem pełnej nazwy, a studentnie tylko identyfikatora, dlatego potrzebujemy informacji zstudent tabeli.

Aby filtrować studentrekordy, które mają 10 stopni z matematyki, możemy użyć operatora EXISTS SQL, takiego jak to:

SELECT
    id, first_name, last_name
FROM
    student
WHERE EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade = 10 AND
        student_grade.class_name = 'Math'
)
ORDER BY id

Po uruchomieniu powyższego zapytania widzimy, że wybrany jest tylko wiersz Alice:

| id | first_name | last_name |
|----|------------|-----------|
| 1  | Alice      | Smith     |

Zewnętrzne zapytanie wybiera student kolumny wierszy, które chcemy powrócić do klienta. Jednak klauzula WHERE korzysta z operatora EXISTS z powiązanym wewnętrznym podzapytaniem.

Operator EXISTS zwraca wartość true, jeśli podzapytanie zwraca co najmniej jeden rekord, a wartość false, jeśli nie wybrano żadnego wiersza. Aparat bazy danych nie musi całkowicie uruchamiać podkwerendy. Jeśli zostanie dopasowany pojedynczy rekord, operator EXISTS zwróci wartość true i wybrany zostanie powiązany inny wiersz zapytania.

Wewnętrzne podzapytanie jest skorelowane, ponieważ kolumna student_id w student_grade tabeli jest dopasowana do kolumny id zewnętrznej tabeli studenta.

SQL NIE ISTNIEJE

Rozważmy, że chcemy wybrać wszystkich uczniów, którzy nie mają ocen niższych niż 9. W tym celu możemy użyć NOT EXISTS, co neguje logikę operatora EXISTS.

Dlatego operator NOT EXISTS zwraca true, jeśli bazowe podzapytanie nie zwraca rekordu. Jeśli jednak pojedynczy rekord zostanie dopasowany do wewnętrznego podzapytania, operator NOT EXISTS zwróci false, a wykonanie podzapytania można zatrzymać.

Aby dopasować wszystkie rekordy studentów, które nie mają skojarzonego student_grade z wartością mniejszą niż 9, możemy uruchomić następujące zapytanie SQL:

SELECT
    id, first_name, last_name
FROM
    student
WHERE NOT EXISTS (
    SELECT 1
    FROM
        student_grade
    WHERE
        student_grade.student_id = student.id AND
        student_grade.grade < 9
)
ORDER BY id

Po uruchomieniu powyższego zapytania widzimy, że dopasowany jest tylko rekord Alice:

| id | first_name | last_name |
|----|------------|-----------|
| 1  | Alice      | Smith     |

Zaletą korzystania z operatorów SQL EXISTS i NOT EXISTS jest to, że wykonywanie wewnętrznego podzapytania można zatrzymać, dopóki znaleziony zostanie pasujący rekord.

To zależy..

SELECT x.col
FROM big_table x
WHERE x.key IN( SELECT key FROM really_big_table );

nie byłoby stosunkowo powolne, nie ma zbyt wiele, aby ograniczyć rozmiar sprawdzania zapytania, aby sprawdzić, czy klucz jest włączony. W tym przypadku preferowane byłoby EXISTS.

Ale w zależności od optymalizatora DBMS nie może być inaczej.

Jako przykład, kiedy EXISTS jest lepszy

SELECT x.col
FROM big_table x
WHERE EXISTS( SELECT key FROM really_big_table WHERE key = x.key);
  AND id = very_limiting_criteria