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

FilterInJoinOrWhere_06

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żą:

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

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:

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:

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:

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

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):

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

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.

Adrian Chodkowski
Follow me

Adrian Chodkowski

SQL geek, Data enthusiast, Consultant & Developer
Adrian Chodkowski
Follow me

2 Comments

  1. Gość

    W typowych tutorialach do SQL-a zwykle omawia się warunki filtrowania w WHERE. Rzadko kiedy trafiam na opisy filtrowania w JOIN ON. Tak z ciekawości sprawdziłem jak powyższe przykłady działają w Oracle. Podobnie, ale SQL Server ma niezaprzeczalną zaletę, a mianowicie czytelność planów zapytań.

    Reply
  2. Adrian Chodkowski (Post author)

    To prawda rzadko mówi się o filtrowaniu w ON – jednakże ono występuje. Plany zapytania są świetną wskazówką gdy chcemy zrozumieć jak zapytanie działa chociaż na pierwszy rzut oka nie musi to być takie oczywiste.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *