Najlepsza praktyka między używaniem LEWEGO DOŁĄCZENIA lub NIE ISTNIENIA

Czy istnieje najlepsza praktyka między użyciem formatu LEFT JOIN lub NOT EXISTS?

Jakie są korzyści z używania jednego nad drugim?

Jeśli nie, które powinny być preferowane?

SELECT *
FROM tableA A
LEFT JOIN tableB B
     ON A.idx = B.idx
WHERE B.idx IS NULL

SELECT *
FROM tableA A
WHERE NOT EXISTS
(SELECT idx FROM tableB B WHERE B.idx = A.idx)

Korzystam z zapytań w programie Access przeciwko bazie danych SQL Server.

Największą różnicą jest to, że łączenie nie istnieje, to (jak napisano) SELECT *.

W pierwszym przykładzie, można uzyskać wszystkie kolumny z obu A i B, podczas gdy w drugim przykładzie, można uzyskać tylko z kolumn A.

W SQL Server drugi wariant jest nieco szybszy w bardzo prostym wymyślonym przykładzie:

Utwórz dwie przykładowe tabele:

CREATE TABLE dbo.A
(
    A_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);

CREATE TABLE dbo.B
(
    B_ID INT NOT NULL
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
);
GO

Wstaw 10 000 wierszy do każdej tabeli:

INSERT INTO dbo.A DEFAULT VALUES;
GO 10000

INSERT INTO dbo.B DEFAULT VALUES;
GO 10000

Usuń co 5 rząd z drugiej tabeli:

DELETE 
FROM dbo.B 
WHERE B_ID % 5 = 1;

SELECT COUNT(*) -- shows 10,000
FROM dbo.A;

SELECT COUNT(*) -- shows  8,000
FROM dbo.B;

Wykonaj dwa SELECTwarianty instrukcji testowych :

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

Plany realizacji:

Drugi wariant nie musi wykonywać operacji filtrowania, ponieważ może korzystać z lewego operatora anty-semi join.

Logicznie są identyczne, ale NOT EXISTSsą bliżej AntiSemiJoin, o który prosisz, i są ogólnie preferowane. Podkreśla także lepiej, że nie można uzyskać dostępu do kolumn w B, ponieważ jest on używany tylko jako filtr (w przeciwieństwie do udostępniania ich z wartościami NULL).

Wiele lat temu (SQL Server 6.0 ish) LEFT JOINbyło szybsze, ale od dawna tak nie było. Te dni NOT EXISTSsą nieznacznie szybsze.

Największy wpływ w programie Access polega na tym, że JOINmetoda musi zakończyć sprzężenie przed filtrowaniem go, tworząc połączony zestaw w pamięci. Użycie NOT EXISTSgo sprawdza wiersz, ale nie przydziela miejsca na kolumny. Ponadto przestaje szukać, gdy znajdzie wiersz. Wydajność różni się nieco bardziej w programie Access, ale ogólna ogólna zasada jest taka, że NOT EXISTSjest nieco szybsza. Byłbym mniej skłonny powiedzieć, że to „najlepsza praktyka”, ponieważ wiąże się z tym więcej czynników.

Wyjątkiem, który zauważyłem, NOT EXISTSbędąc lepszym (choć marginalnym), LEFT JOIN ... WHERE IS NULLjest używanie serwerów połączonych .

Z analizy planów wykonania wynika, że NOT EXISTSoperator jest wykonywany w sposób zagnieżdżony. Przy czym jest on wykonywany dla poszczególnych wierszy (co, jak sądzę, ma sens).

Przykładowy plan wykonania przedstawiający to zachowanie:

Ogólnie silnik utworzy plan wykonania w oparciu o:

1. Liczba wierszy w A i B.
2. Czy istnieje indeks na A i / lub B.
3. Oczekiwana liczba wierszy wyników (i wierszy pośrednich)
4. Forma zapytania wejściowego (tj. Twoje pytanie)

Dla 4):

Plan „nie istnieje” zachęca do opracowania planu opartego na wyszukiwaniu w tabeli B. Jest to dobry wybór, gdy tabela A jest mała, a tabela B jest duża (a indeks istnieje na B).

Plan „antijoin” jest dobrym wyborem, gdy tabela A jest bardzo duża lub tabela B jest bardzo mała lub nie ma indeksu na B i zwraca duży zestaw wyników.

Jest to jednak „zachęta”, jak ważony wkład. Silny (1), (2), (3) często dokonuje wyboru dla (4) dyskusji.

(Ignorując efekt twojego przykładu zwracania różnych kolumn ze względu na *, adresowany przez odpowiedź @MaxVernon.).