Jaka jest różnica między klauzulą EXISTSi INw SQL?

Kiedy powinniśmy używać EXISTS, a kiedy powinniśmy używać IN?

Słowa existskluczowego można używać w ten sposób, ale tak naprawdę ma on na celu uniknięcie liczenia:

--this statement needs to check the entire table
select count(*) from [table] where ...

--this statement is true as soon as one match is found
exists ( select * from [table] where ... )

Jest to najbardziej przydatne, gdy masz ifinstrukcje warunkowe, ponieważ existsmoże być znacznie szybsze niż count.

inNajlepiej wykorzystane gdzie masz listę statycznych do przekazania:

 select * from [table]
 where [field] in (1, 2, 3)

Kiedy masz tabelę w ininstrukcji, sensowniejsze jest użycie join, ale w większości nie powinno to mieć znaczenia. Optymalizator zapytań powinien w obu przypadkach zwrócić ten sam plan. W niektórych implementacjach (głównie starszych, takich jak Microsoft SQL Server 2000), inzapytania zawsze otrzymają zagnieżdżony plan łączenia , podczas gdy joinzapytania będą używać odpowiednio zagnieżdżenia, scalenia lub skrótu . Bardziej nowoczesne wdrożenia są inteligentniejsze i mogą dostosowywać plan, nawet gdy injest używany.

EXISTSpowie ci, czy zapytanie zwróciło jakieś wyniki. na przykład:

SELECT * 
FROM Orders o 
WHERE EXISTS (
    SELECT * 
    FROM Products p 
    WHERE p.ProductNumber = o.ProductNumber)

IN służy do porównywania jednej wartości z kilkoma i może używać wartości literalnych, takich jak to:

SELECT * 
FROM Orders 
WHERE ProductNumber IN (1, 10, 100)

Możesz również użyć wyników zapytania z INklauzulą, takich jak:

SELECT * 
FROM Orders 
WHERE ProductNumber IN (
    SELECT ProductNumber 
    FROM Products 
    WHERE ProductInventoryQuantity > 0)

W oparciu o optymalizator reguł :

• EXISTSjest znacznie szybszy niż IN, gdy wyniki zapytania są bardzo duże.
• INjest szybszy niż EXISTS, gdy wyniki zapytania podrzędnego są bardzo małe.

W oparciu o optymalizator kosztów :

• Nie ma różnicy.

Zakładam, że wiesz, co oni robią, a zatem są używane w inny sposób, więc zrozumiem twoje pytanie jako: Kiedy dobrze byłoby przepisać SQL na IN zamiast zamiast ISTNIEĆ lub odwrotnie.

Czy to uczciwe założenie?

Edycja : Powodem, dla którego pytam, jest to, że w wielu przypadkach możesz przepisać SQL oparty na IN, aby zamiast tego użyć EXISTS i odwrotnie, a dla niektórych silników baz danych optymalizator zapytań będzie traktował je inaczej.

Na przykład:

SELECT *
FROM Customers
WHERE EXISTS (
    SELECT *
    FROM Orders
    WHERE Orders.CustomerID = Customers.ID
)

można przepisać do:

SELECT *
FROM Customers
WHERE ID IN (
    SELECT CustomerID
    FROM Orders
)

lub z dołączeniem:

SELECT Customers.*
FROM Customers
    INNER JOIN Orders ON Customers.ID = Orders.CustomerID

Więc moje pytanie wciąż pozostaje aktualne: czy oryginalny plakat zastanawia się, co robi IN i EXISTS, a zatem jak go używać, czy też prosi, aby przepisanie SQL za pomocą IN zamiast EXISTS zamiast tego, czy odwrotnie, będzie dobrym pomysłem?

1. EXISTS jest znacznie szybszy niż IN wtedy, gdy wyniki podkwerendy są bardzo duże.
   INjest szybszy niż EXISTSwtedy, gdy wyniki podkwerendy są bardzo małe.

   CREATE TABLE t1 (id INT, title VARCHAR(20), someIntCol INT)
   GO
   CREATE TABLE t2 (id INT, t1Id INT, someData VARCHAR(20))
   GO
   
   INSERT INTO t1
   SELECT 1, 'title 1', 5 UNION ALL
   SELECT 2, 'title 2', 5 UNION ALL
   SELECT 3, 'title 3', 5 UNION ALL
   SELECT 4, 'title 4', 5 UNION ALL
   SELECT null, 'title 5', 5 UNION ALL
   SELECT null, 'title 6', 5
   
   INSERT INTO t2
   SELECT 1, 1, 'data 1' UNION ALL
   SELECT 2, 1, 'data 2' UNION ALL
   SELECT 3, 2, 'data 3' UNION ALL
   SELECT 4, 3, 'data 4' UNION ALL
   SELECT 5, 3, 'data 5' UNION ALL
   SELECT 6, 3, 'data 6' UNION ALL
   SELECT 7, 4, 'data 7' UNION ALL
   SELECT 8, null, 'data 8' UNION ALL
   SELECT 9, 6, 'data 9' UNION ALL
   SELECT 10, 6, 'data 10' UNION ALL
   SELECT 11, 8, 'data 11'

2. Zapytanie 1

   SELECT
   FROM    t1 
   WHERE   not  EXISTS (SELECT * FROM t2 WHERE t1.id = t2.t1id)

   Zapytanie 2

   SELECT t1.* 
   FROM   t1 
   WHERE  t1.id not in (SELECT  t2.t1id FROM t2 )

   Jeśli w t1twoim id ma wartość zerową, wówczas Zapytanie 1 je znajdzie, ale Zapytanie 2 nie może znaleźć parametrów zerowych.

   Mam na myśli, że INnie można porównać niczego z wartością null, więc nie ma żadnego wyniku dla wartości null, ale EXISTSmożna porównać wszystko z wartością null.

Jeśli używasz INoperatora, silnik SQL skanuje wszystkie rekordy pobrane z wewnętrznego zapytania. Z drugiej strony, jeśli korzystamy EXISTS, silnik SQL zatrzyma proces skanowania, gdy tylko znajdzie pasujące.

IN obsługuje tylko relacje równości (lub nierówności, jeśli poprzedza je NOT ).
Jest to synonim = any / = some , np

select    * 
from      t1 
where     x in (select x from t2)
;

EXISTS obsługuje różne typy relacji, których nie można wyrazić za pomocą IN , np. -

select    * 
from      t1 
where     exists (select    null 
                  from      t2 
                  where     t2.x=t1.x 
                        and t2.y>t1.y 
                        and t2.z like '℅' || t1.z || '℅'
                  )
;

I z innej nuty -

Rzekoma wydajność i różnice techniczne między EXISTS a IN mogą wynikać z implementacji / ograniczeń / błędów określonych dostawców, ale wiele razy są one niczym więcej niż mitami stworzonymi z powodu niezrozumienia wewnętrznych elementów baz danych.

Definicja tabel, dokładność statystyk, konfiguracja bazy danych i wersja optymalizatora mają wpływ na plan wykonania, a tym samym na wskaźniki wydajności.

Słowo Existskluczowe ocenia wartość prawda lub fałsz, ale INsłowo kluczowe porównuje całą wartość w odpowiedniej kolumnie zapytania podrzędnego. Kolejnego Select 1można użyć z Existspoleceniem. Przykład:

SELECT * FROM Temp1 where exists(select 1 from Temp2 where conditions...)

Ale INjest mniej wydajny, więc Existsszybszy.

Myślę,

• EXISTSwystępuje, gdy trzeba dopasować wyniki zapytania do innego podzapytania. Wyniki zapytania nr 1 muszą zostać pobrane tam, gdzie pasują wyniki SubQuery. Rodzaj przyłączenia. Np. Wybierz tabelę klientów nr 1, którzy również złożyli tabelę zamówień nr 2

• IN ma pobrać, jeśli wartość konkretnej kolumny znajduje się INna liście (1,2,3,4,5) Np. Wybierz klientów, którzy znajdują się w następujących kodach pocztowych, tj. Wartości kodów pocztowych znajdują się na liście (....).

Kiedy stosować jeden na drugim ... kiedy czujesz, że jest on odpowiednio odczytywany (lepiej komunikuje się celowo).

Różnica leży tutaj:

select * 
from abcTable
where exists (select null)

Powyższe zapytanie zwróci wszystkie rekordy, a poniżej jedno zwróci puste.

select *
from abcTable
where abcTable_ID in (select null)

Spróbuj i obserwuj wynik.

Zgodnie z moją wiedzą, gdy podzapytanie zwraca NULLwartość, wówczas cała instrukcja staje się NULL. W takich przypadkach używamy EXITSsłowa kluczowego. Jeśli chcemy porównać poszczególne wartości w podzapytaniach, używamy INsłowa kluczowego.

To, które jest szybsze, zależy od liczby zapytań pobranych przez zapytanie wewnętrzne:

• Gdy twoje wewnętrzne zapytanie pobierające tysiące wierszy, EXIST byłoby lepszym wyborem
• Kiedy twoje wewnętrzne zapytanie pobiera kilka wierszy, wtedy IN będzie szybsze

ISTNIEJ ocenia na prawda lub fałsz, ale IN porównuje wiele wartości. Jeśli nie wiesz, czy dany rekord istnieje, czy nie, wybierz EXIST

Powodem jest to, że operator EXISTS działa w oparciu o zasadę „przynajmniej znaleziono”. Zwraca wartość true i zatrzymuje skanowanie tabeli po znalezieniu co najmniej jednego pasującego wiersza.

Z drugiej strony, gdy operator IN jest połączony z podkwerendą, MySQL musi najpierw przetworzyć subkwerendę, a następnie wykorzystuje wynik kwerendy do przetworzenia całego zapytania.

Ogólna zasada jest taka, że ​​jeśli podkwerenda zawiera dużą ilość danych, operator EXISTS zapewnia lepszą wydajność.

Jednak zapytanie korzystające z operatora IN będzie działać szybciej, jeśli zestaw wyników zwrócony z podzapytania jest bardzo mały.

Rozumiem, że oba powinny być takie same, o ile nie mamy do czynienia z wartościami NULL.

Ten sam powód, dla którego zapytanie nie zwraca wartości = NULL vs, to NULL. http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

Jeśli chodzi o argument boolean vs komparator, aby wygenerować wartość logiczną, należy porównać obie wartości i tak działa dowolny warunek if, więc nie rozumiem, jak IN i EXISTS zachowują się inaczej.

In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, then use IN. If the selective predicate is in the parent query, then use EXISTS.

https://docs.oracle.com/cd/B19306_01/server.102/b14211/sql_1016.htm#i28403

Jeśli podzapytanie zwraca więcej niż jedną wartość, może być konieczne wykonanie zapytania zewnętrznego - jeśli wartości w kolumnie określonej w warunku są zgodne z dowolną wartością w zestawie wyników podzapytania. Aby wykonać to zadanie, musisz użyć insłowa kluczowego.

Za pomocą podzapytania można sprawdzić, czy istnieje zestaw rekordów. W tym celu należy użyć existsklauzuli z podzapytaniem. Słowo existskluczowe zawsze zwraca wartość true lub false.

Uważam, że ma to prostą odpowiedź. Dlaczego nie sprawdzisz tego od osób, które opracowały tę funkcję w swoich systemach?

Jeśli jesteś programistą MS SQL, oto odpowiedź bezpośrednio od Microsoft.

IN:

Określa, czy określona wartość pasuje do dowolnej wartości w podzapytaniu lub liście.

EXISTS:

Określa podzapytanie do przetestowania pod kątem istnienia wierszy.

Odkryłem, że użycie słowa kluczowego EXISTS jest często bardzo powolne (jest to bardzo prawdziwe w programie Microsoft Access). Zamiast tego używam operatora łączyć w ten sposób: powinien-i-użyć-słowa kluczowego-istnieje-w-sql

EXISTS działa szybciej niż IN. Jeśli większość kryteriów filtrowania znajduje się w podzapytaniu, lepiej użyć IN, a jeśli większość kryteriów filtrowania znajduje się w głównym zapytaniu, lepiej użyć EXISTS.

Jeśli używasz operatora IN, silnik SQL skanuje wszystkie rekordy pobrane z wewnętrznego zapytania. Z drugiej strony, jeśli korzystamy z EXISTS, silnik SQL zatrzyma proces skanowania, gdy tylko znajdzie pasujące.