TSQL – warunki filtrujące w WHERE czy w JOIN ON?

Na przestrzeni ostatnich lat było mi dane oglądać dużą ilość kodu TSQL napisanego przez kogoś innego. W dużej ilości przypadków spotykałem się z dosyć ciekawymi konstrukcjami, które raz działały lepiej, a raz gorzej. Jedną z takich konstrukcji było umieszczanie warunków filtrujących w sekcji złączenia ON. Czy ma to jakiekolwiek znaczenie? Czy ma to wpływ na wydajność i/lub otrzymane rezultaty w porównaniu do umieszczenia warunków filtrujących w WHERE? Na te i kilka innych pytań postaram się odpowiedzieć w ramach niniejszego artykułu – zapraszam do lektury.

Na potrzeby naszej demonstracji stworzymy sobie w bazie systemowej tempdb dwie tabele tymczasowe- jedna z nich zawiera słownikowe dane produktów druga z kolei zawiera grupy produktowe do których dane produkty należą:

USE tempdb
GO

CREATE TABLE #Product
(
ProductId INT,
Name varchar(20),
Price INT,
ProductGroupID INT
)
GO

CREATE TABLE #ProductGroup
(
ProductGroupID INT,
ProductGroupName VARCHAR(20)
)
GO

Następnie wstawmy do tych tabel przykładowe dane – samych danych nie ma zbyt wiele gdyż nie jest to dla naszych potrzeb konieczne:

INSERT INTO #ProductGroup
(ProductGroupID,ProductGroupName)
VALUES
(1,'Owoce'),
(2,'Warzywa'),
(3,'Inne')

GO

INSERT INTO #Product
(ProductId,Name,Price,ProductGroupID)
VALUES 
(1,'Jabłko',2,1),
(2,'Pomidor',3,2),
(3,'Gruszka',4,1),
(4,'Banan',4,1)
GO

Z powyższego zapytania można wywnioskować, że w tabeli #ProductGroup znajduje się grupa “Inne”, do której nie jest przypisany żaden produkt – pamiętajmy o tym gdyż będzie to istota naszych kolejnych testów. Zacznijmy nasz test – na pierwszy ogień weźmy złączenie wewnętrzne. W pierwszym z dwóch zapytań umieściłem warunek filtrujący w sekcji WHERE, w drugim z kolei warunek znajduje się w sekcji JOIN ON:

SELECT *
FROM 
#ProductGroup AS PG
JOIN #Product AS P
ON PG.ProductGroupID=P.ProductGroupID
WHERE P.Price>2

SELECT *
FROM 
#ProductGroup AS PG
JOIN #Product AS P
ON PG.ProductGroupID=P.ProductGroupID
AND P.Price>2

Rezultat obu zapytań jest dokładnie taki sam, czyli zwrócone zostały po 3 pasujące wiersze:

W przypadku tego typu zapytań optymalizator rozpozna sytuację i wygeneruje również dokładnie taki sam plan wykonania. Niepasujące wiersze zostaną “odrzucone” w ramach operatora Hash Match:

Tak więc mamy już pierwsze podejrzenie tj. w przypadku złączeń wewnętrznych różnic w rezultacie i wydajnościowych nie ma. Sytuacja staje się nieco ciekawsza gdy użyjemy złączeń zewnętrznych:

SELECT *
FROM 
#ProductGroup AS PG
LEFT JOIN #Product AS P
ON PG.ProductGroupID=P.ProductGroupID
WHERE P.Price>2

SELECT *
FROM 
#ProductGroup AS PG
LEFT JOIN #Product AS P
ON PG.ProductGroupID=P.ProductGroupID
AND P.Price>2

W tym przypadku rezultat jest już całkowicie inny:

Pierwsze zapytanie zawierało wyrażenie filtrujące w sekcji WHERE, a więc filtracja została dokonana dopiero po złączeniu obu tabel. W drugim przypadku filtracja na tabeli #Product została dokonana jeszcze przed złączeniem dlatego zwrócone zostały wszystkie wiersze. Póki co wszystko było w miarę przejrzyste prawda?

Spróbujmy zatem zbudować zapytanie, które zwróci nam wszystkie grupy produktów i tylko te produkty, które mają przypisaną grupę produktową i ich cena jest większa niż 2. Na początku zapiszmy nasze warunki w JOIN ON:

SELECT *
FROM 
#ProductGroup AS PG
LEFT JOIN #Product AS P
ON PG.ProductGroupID=P.ProductGroupID
AND P.Price>2 OR P.Price IS NULL

Wszystko wygląda w porządku. To teraz spróbujmy przenieść nasz warunek do sekcji WHERE:

SELECT *
FROM 
#ProductGroup AS PG
LEFT JOIN #Product AS P
ON PG.ProductGroupID=P.ProductGroupID
WHERE P.Price>2 OR P.Price IS NULL

Rezultat pozostał taki sam i może się wydawać, że oba wyrażenia są substytutami – spójrzmy jednak w tym miejscu na plany wykonania obu zapytań:

Są one zdecydowanie inne, w przypadku zapytania z filtracją w JOIN wiersze wybierane są wewnątrz operatora Table Scan – czyli nastąpiło przesunięcie filtracji do jak najwcześniejszego elementu planu (pisałem o tym tutaj). W przypadku drugiego zapytania filtracja zgodnie z oczekiwaniami została wykonana dopiero po złączeniu obu tabel. Czy ma to dla nas jakieś większe znaczenie? Zaraz się przekonamy! Dodajmy do tabeli produktów wiersz, który będzie łączył się z grupą produktową o identyfikatorze 3 (to ta grupa, która nie miała żadnego przypisanego produktu):

INSERT INTO #Product
(ProductId,Name,Price,ProductGroupID)
VALUES
(5,'Piłka',1,3)

Następnie wykonajmy oba wcześniej testowane zapytania raz jeszcze:

SELECT *
FROM 
#ProductGroup AS PG
LEFT JOIN #Product AS P
ON PG.ProductGroupID=P.ProductGroupID
AND P.Price>2 OR P.Price IS NULL

SELECT *
FROM 
#ProductGroup AS PG
LEFT JOIN #Product AS P
ON PG.ProductGroupID=P.ProductGroupID
WHERE P.Price>2 OR P.Price IS NULL

Rezultaty są inne?! Jak to w ogóle możliwe? Odpowiedź mimo, iż na pierwszy rzut oka niewidoczna w rzeczywistości jest bardzo prosta – wszystko zależy od momentu w którym dane zostały przefiltrowane. W przypadku zapisu z warunkiem filtrującym w JOIN ON wyglądało to następująco:

1. Pobrano wiersze z tabeli #Product i od razu je przefiltrowano:

2. Dokonano złączenia zewnętrznego z tabelą #ProductGroup na już przefiltrowanych danych tabeli #Product:

W przypadku warunku filtrującego w WHERE wyglądało to następująco:

1. Złączono tabele na nieprzefiltrowanym zestawie danych:

2. Dokonano filtracji bazując na warunku zapisanym w WHERE:

Teraz już wiemy, że brak zrozumienia tego jakie konsekwencje niesie ze sobą filtrowanie w WHERE oraz JOIN ON może mieć skutki nie tylko w wydajności, ale również może prowadzić do błędnych rezultatów naszych zapytań.

Podsumowując można powiedzieć, że w przypadku złączeń wewnętrznych to gdzie umieścimy warunki filtrujące nie ma znaczenia. Gdy nasze zapytania zawierają złączenia zewnętrzne – wtedy należy wziąć pod uwagę powyższą sytuację aby uniknąć późniejszych problemów z tym, że raz coś zadziałało, a raz nie. Ogólnie rzecz biorąc tak jak zawsze warto wypracować sobie pewien sprawdzony standard odpowiadający poszczególnym scenariuszom i trzymać się go w każdej sytuacji. Tematyka ta mimo, że dosyć podstawowa to może powodować problemy i niezrozumienie bo jak wiemy diabeł zawsze tkwi w szczegółach.