Operator APPLY w TSQL

Do napisania dzisiejszego artykułu skłonił mnie fakt, iż naprawdę niewiele osób piszących zapytania TSQL używa tzw. zaawansowanych elementów języka. Dlaczego tak się dzieje? Nie jestem pewien ale wydaje mi się, że słowo “zaawansowanych” już dostatecznie odstrasza potencjalnych zainteresowanych – w moim mniemaniu całkowicie niesłusznie. Jednym ze “straszniejszych” elementów TSQL jest bez wątpienia APPLY – część z was z pewnością  go używa, inni słyszeli ale nie widzą zastosowania, dla niektórych to zupełna nowość. Poniżej postaram się krótko przedstawić gdzie i w jaki sposób możemy niniejszego narzędzia użyć, a jest to narzędzie naprawdę potężne. Zaczynajmy!

Aby zrozumieć specyfikę działania naszego dzisiejszego bohatera musimy przypomnieć sobie teorię z zakresu operatorów złączenia czyli popularnych JOINów.  W teorii relacyjnych baz danych mamy do czynienia z tak zwanymi złączeniami wewnętrznymi (ang. INNER JOIN) oraz zewnętrznymi (ang. OUTER JOIN). W zależności od rodzaju złączenia dwa zestawy danych są ze sobą w określony sposób porównywane i zwracany jest rezultat. To co jest niezmierne istotne w działaniu złączeń to fakt, iż oba zestawy danych (na obrazku nazwane Table 1 oraz Table 2) muszą być predefiniowane – silnik musi mieć dane na temat obu stron operatora:rest

Co natomiast jeśli jedna ze stron, które chcemy dołączać nie zawiera predefiniowanego zbioru danych i nie możemy go zapisać przy pomocy znanych nam metod? Możemy użyć APPLY! Poniższy rysunek w pełni oddaje istotę działania APPLY.

rest

Jak można zauważyć APPLY oczekuje predefiniowanego zestawu po lewej stronie złączenia. Tak więc jeśli chcemy dla przykładu użyć funkcji tabelarycznej wiersz po wierszu nie możemy tego zrobić przy użyciu tradycyjnych złączeń. W tym momencie może Wam przyjść na myśl użycie kursorów – myślę, że możecie zapomnieć o tej myśli tak szybko jak przyszła Wam do głowy. Jak to działa sprawdźmy to na przykładzie! W naszych przykładach standardowo będziemy używać bazy Adventure Works. Jako nasz problem biznesowy weźmiemy sobie typową sytuację: potrzebujemy zapytania, które w dynamiczny sposób zwróci nam N ostatnich dat zamówień dla określonego klienta.

Po pierwsze potrzebujemy funkcji tabelarycznej, która nam coś takie zwróci – nie skupijamy się konkretnie jak ona działa – przyjmijmy ją jako czarną skrzynkę:

Przetestujmy jej działanie w pierwszym parametrze podając identyfikator wybranego klienta, w drugim natomiast liczbę dat jakie chcemy otrzymać.

Jak widać poniżej funkcja działa zgodnie z przeznaczeniem.

Wreszcie nadszedł czas na przetestowanie naszego APPLY. Złączmy tabelę z klientami tak aby pokazać dwie ostatnie daty zakupów dla każdego z klientów:

Rezultat jest taki jak się spodziewaliśmy – powinniśmy pamiętać o tym, że konkretnie CROSS APPLY logicznie działa bardzo podobnie do złączenia wewnętrznego czyli zwraca tylko te wiersze, które znajdują się w obu zestawach.

Wygląda to świetnie, co jednak gdy chcemy poznać klientów, którzy w ogóle nie złożyli żadnego zamówienia? Jak już wspomniałem CROSS APPLY działa jak INNER JOIN – tak więc czy jest coś podobnego do OUTER JOIN? Oczywiście! Mamy do dyspozycji OUTER APPLY. Sprawdźmy to na przykładzie. W bazie Adventureworks2012DW nie ma klienta, który nigdy nie złożył zamówienia, więc możemy sobie to zasymulować np. poprzez dodanie wiersza do tabeli DimCustomer. W zapytaniu dodamy również klauzulę WHERE gdzie pokażemy sobie tylko tych klientów, którzy nie mają połączenia do tabeli z zamówieniami.

Jak widać poniżej w rezultacie otrzymaliśmy jednego klienta, który nigdy nie złożył żadnego zamówienia.

rest

Tak więc użycie operatorów APPLY jest naprawdę proste i przyjemne w użyciu. Ponadto rozwiązują one całą masę problemów w naszej codziennej pracy. Co jednak z kwestą wydajności? Jak możecie się domyślać nie jest najlepiej ze względu na fakt, iż funkcje tabelaryczne nie są najwydajniejszą strukturą – jednak mimo wszystko warto znać te funkcjonalności języka TSQL gdyż w wielu przypadkach naprawdę się sprawdzają.

Adrian Chodkowski
Follow me

Adrian Chodkowski

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

2 Comments

  1. Piotr Ziuziański

    Przydatne i faktycznie mało kto korzysta z tego operatora 🙂 Dzięki za podzielenie się wiedzą!

    Reply
    1. Adrian ChodkowskiAdrian Chodkowski (Post author)

      Polecam testowanie w swoich procedurach – w moim przypadku plan zapytania z użyciem cross apply w porównaniu do inline sql był o wiele bardziej wydajny 🙂

      Reply

Leave a Comment

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