SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Podzapytania w SQL

Język SQL, pomimo ściśle ustalonego szyku bloków logicznych (SELECT, FROM, WHERE…. ) jest dość elastyczny. W rozdziałach dotyczących elementów składniowych zapytań, do znudzenia podkreślałem fundament na którym zbudowane są relacyjne bazy danych i SQL. Jest to matematyczna teoria zbiorów. Podzapytania idealnie obrazują te zasady w praktyce i są często stosowanymi konstrukcjami.

Podzapytania, jak sama nazwa wskazuje, są częścią podrzędną innego zapytania. Możemy podzielić je na dwie kategorie ze względu na powiązanie z kwerendą nadrzędną :

  • niezależne – funkcjonować mogą w całkowicie oderwanym kontekście. Można je uruchomić jako osobne kwerendy – o nich właśnie jest ten artykuł.
  • skorelowane – są bezpośrednio powiązane z zapytaniem nadrzędnym. Opisuję je w kolejnym rozdziale tego kursu.

Podzapytania niezależne

Przypomnijmy podstawowe zasady, które intuicyjnie nakierowują na właściwie tory jeśli chodzi o temat podzapytań. Jeśli przerabiasz ten kurs od początku, reguły te powinny być dla Ciebie już oczywiste.

Każde zapytanie SQL to operacja na zbiorze lub zbiorach elementów. Tabele i widoki do których zazwyczaj odwołujemy się w kwerendach to tylko przykłady zbiorów. Wynikiem działania dowolnej kwerendy jest także zbiór.

Rozważmy na przykład zapytanie do zbioru elementów (tabeli) dbo.Customers. Wybierzemy tylko takie elementy (rekordy), dla których wartość atrybutu (kolumny) City, jest równa ciągowi znaków ‘London’.

USE Northwind
GO
 
SELECT CompanyName, City, Country 
FROM dbo.Customers
WHERE City = 'London'

Podzapytania_SQL_01

To co chciałem na tym banalnym przykładzie podkreślić, to fakt że zapytanie, odwołuje się do zbioru (lub zbiorów) i zwraca jeden zbiór. Skoro więc zwraca zbiór, to możemy ten zbiór także odpytywać jak zwykłą tabelę. Będzie to pierwszy przykład z wykorzystaniem typowego podzapytania niezależnego we FROM :

SELECT *
FROM 
(
	-- wstępna, selekcja elementów i atrybutów zbioru dbo.Customers
	-- może tu być dowolna skomplikowana kwerenda.
	SELECT CompanyName, City, Country 	FROM dbo.Customers	where City = 'London' 
) AS MojePodzapytanie
WHERE CompanyName like '[A-C]%'

Podzapytania_SQL_02

Zauważ, że w każdej chwili możesz to podzapytanie uruchomić zaznaczając tylko jego zakres. Jest ono niezależne w stosunku do zapytania zewnętrznego. Wykonane zostanie raz, w trakcie całego procesu logicznego przetwarzania tej kwerendy.

Każdy zbiór do którego odnosimy się we FROM musi być nazwany i w pełni określony. Stąd konieczność stosowania aliasów oraz unikalnych nazw kolumn w ramach podzapytań.

Miejsca w których możemy stosować podzapytania

Podzapytania możemy stosować praktycznie w dowolnym bloku logicznym kwerendy. Jedynym ograniczeniem jest rodzaj zwracanego zbioru. Musi pasować do miejsca w którym chcemy go użyć. Na przykład we FROM, może to być dowolny zbiór (jednoelementowy, wieloelementowy itd), z kolei w SELECT musi to być wartość skalarna czyli zbiór jednoelementowy opisany jednym atrybutem.

W dalszych przykładach, będę wykorzystywał bazę testową AdventureWorks2008, aby zaprezentować typowe zastosowania podzapytań w różnych miejscach kwerendy.

Pobierzemy informacje o zleceniach z czerwca 2014, z rejonu Wielkiej Brytanii (CountryRegionCode = ‘GB’), dla których wartość (TotalDue), przekroczyła średnią liczoną dla wszystkich zleceń.

Zacznijmy od kwerendy, która zwróci nam informacje o średniej wartości dla wszystkich zamówień.

USE AdventureWorks2008
GO
 
SELECT AVG(TotalDue) as AVG_TotalDue
FROM [Sales].[SalesOrderHeader]

Podzapytania_SQL_03

Zwracany zbiór jest szczególny. Jednoelementowy, opisany jednym atrybutem (jedną kolumną) – czyli to zwykła wartość skalarna.

Taki zbiór możemy umieścić w każdym miejscu kwerendy – jako podzapytanie. Najczęściej będziemy go stosować w warunkach WHERE lub w SELECT. Może być też stosowany w innych miejscach gdzie tworzymy wyrażenia, filtracji grup w HAVING czy warunki złączeń w ON.

Wykorzystajmy teraz te informacje, aby odfiltrować rekordy w WHERE i dodatkowo wyświetlić ją w SELECT jako wartość dodatkowej kolumny.

SELECT SalesOrderID, OrderDate, TotalDue, st.Name AS TerritoryName,   
	(  
               -- podzapytanie w SELECT – średnia dla wszystkich zleceń
	     SELECT AVG(TotalDue)  	     FROM [Sales].[SalesOrderHeader]  	) AS AVG_TotalDue
FROM [Sales].[SalesOrderHeader] soh 
     inner join [Sales].[SalesTerritory] st ON soh.TerritoryID = st.TerritoryID
WHERE st.CountryRegionCode = 'GB' and OrderDate between '2004-06-01' and '2004-06-30'  
      and  TotalDue >= 
          (
             -- podzapytanie w filtracji w WHERE
             SELECT AVG(TotalDue) AS  AVG_TotalDue             FROM [Sales].[SalesOrderHeader]            )

Podzapytania_SQL_04

Warto podkreślić, że jeśli podzapytanie nie zwróciłoby tu żadnego rekordu, to wynikiem w kwerendzie zewnętrznej, będzie jeden element opisany NULLami. Trzeba mieć to na uwadze bo jeśli zdarzyłaby się taka sytuacja w podzapytaniu w WHERE – to otrzymamy pusty zbiór. Żaden z rekordów nie spełni przecież warunku TotalDue >= NULL. Każde porównanie z NULL to wartość nieznana, więc każdy rekord będzie odfiltrowany.

Usystematyzujmy dotychczasowe informacje. W SELECT może znaleźć się tylko takie podzapytanie, które zwraca wartość skalarną. We FROM możemy wykorzystać każde podzapytanie, definiujące jakikolwiek zbiór. Tworzenie warunków połączeń w ON , wyrażeń w WHERE oraz filtracji grup w HAVING, dopuszcza różne zbiorów w zależności od zastosowanych operatorów. Standardowo będą to operatory porównujące wartości skalarne ( =, <, >, <>, itd.) – wtedy tylko takie podzapytania, które zwracają skalar.

Są też specjalne operatory działające na zbiorach np. IN, ANY (SOME) , ALL. Operatory te, działają na wektorze wartości. Wektor to zbiór elementów opisanych jednym atrybutem (czyli wartości skalarnych). Zatem w tych przypadkach, podzapytania mogą zwracać wektor.

Pozostał jeszcze jeden specjalny operator – EXISTS / NOT EXISTS, który możemy stosować np. w WHERE. Za jego pomocą sprawdzamy tylko czy zbiór podzapytania jest pusty czy nie. W tym przypadku nie ma znaczenia jakiego typu są to elementy. Jeśli są, to zwracana jest wartość TRUE, jeśli nie – FALSE.


Podzapytania z operatorami IN, ANY (SOME), ALL

Weźmy za przykład kwerendę, która da nam informacje o wszystkich zleceniach, dla trzech najlepszych (pod względem generowania obrotów firmy) Klientów.

Najpierw skupmy się na podzapytaniu, które powinno zwrócić nam wektor 3-elementowy z identyfikatorami najlepszych Klientów. Trzech najdroższych nam Klientów otrzymamy za pomocą takiego zapytania :

SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales
FROM  [Sales].[SalesOrderHeader] soh 
GROUP BY CustomerID
ORDER BY TotalSales DESC

Podzapytania_SQL_05

To jeszcze nie jest wektor, ale już coś. Jeśli spróbujemy teraz zbudować kwerendę w oparciu o takie podzapytanie, filtrując w WHERE z wykorzystaniem operatora IN :

SELECT SalesOrderID, OrderDate, TotalDue, CustomerID from [Sales].[SalesOrderHeader] soh 
WHERE  CustomerID in (
 
	SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales	FROM  [Sales].[SalesOrderHeader] soh 	GROUP BY CustomerID	ORDER BY TotalSales DESC)

Otrzymamy komunikat o błędzie, ponieważ podzapytanie generuje niepoprawny zbiór (nie jest to wektor – posiada dwie kolumny).

Msg 116, Level 16, State 1, Line 9
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

No to zróbmy z niego wektor – podzapytanie z podzapytania :

SELECT SalesOrderID, OrderDate, CustomerID 
FROM [Sales].[SalesOrderHeader] soh 
WHERE CustomerID IN (
         -- podzapytanie z podzapytania w WHERE
	SELECT CustomerID	FROM (		SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales		FROM  [Sales].[SalesOrderHeader] soh 		GROUP BY CustomerID		ORDER BY TotalSales DESC 	) AS WektorIdentyfikatorowKlientow)

W powyższym przykładzie, widzimy inną ważną właściwość podzapytań – możliwość zagnieżdżania ich w sobie. Możemy tworzyć podzapytania z podzapytań do 32 poziomów.
Oczywiście można by było powyższą kwerendę zapisać inaczej, z zastosowaniem podzapytania we FROM.

SELECT SalesOrderID, OrderDate, soh.CustomerID 
FROM [Sales].[SalesOrderHeader] soh INNER JOIN (
          -- podzapytanie we FROM
	SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales	FROM  [Sales].[SalesOrderHeader] soh 	GROUP BY CustomerID	ORDER BY TotalSales DESC  
) a on soh.CustomerID = a.CustomerID

Wynik i nawet plan wykonania w tej sytuacji będzie identyczny. Często filtracja w jak najwcześniejszym kroku procesu przetwarzania kwerendy przynosi lepsze rezultaty, choć nie zawsze.

Rozważmy bardziej skomplikowany przykład. Chcemy wyświetlić trzy najdroższe zamówienia dla trzech naszych najlepszych Klientów. Zapytanie to zapiszemy z zastosowaniem funkcji szeregującej RANK oraz funkcji okna OVER.

SELECT * FROM
 (
 
	SELECT SalesOrderID, TotalDue, 
              RANK() OVER(Partition by soh.CustomerID order by TotalDue desc) as Majority, 
              soh.CustomerID
	FROM [Sales].[SalesOrderHeader] soh inner join 
	( 
			SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales
			FROM  [Sales].[SalesOrderHeader] soh 
			GROUP BY CustomerID
			ORDER BY TotalSales DESC
	) b on soh.CustomerID = b.CustomerID
) a 
WHERE Majority  <= 3

Podzapytania_SQL_06

Inny sposób na osiągnięcie tego samego celu, z filtracją Klientów w WHERE.

SELECT * 
FROM
 (
	-- wychwycenie tylko najdroższych zamówień per Klient
	SELECT SalesOrderID, TotalDue, 
               RANK() OVER(Partition by CustomerID order by TotalDue desc) as Majority,
               CustomerID
	FROM [Sales].[SalesOrderHeader] soh 
) a 
WHERE Majority <= 3 AND CustomerID IN (
 
	-- zrobienie wektora
	SELECT CustomerID
	FROM (
		-- wychwycenie 3 najdroższych nam Klientów
		SELECT TOP 3 CustomerID , SUM(TotalDue) as TotalSales
		FROM  [Sales].[SalesOrderHeader] soh 
		GROUP BY CustomerID
		order by TotalSales desc 
	) a 
)

To zapytanie generuje jednak bardziej kosztowny plan wykonania (w porównaniu do poprzedniego 31% : 69%).

Podzapytania_SQL_07


W SQL jest wiele sposobów na osiągnięcie tego samego rezultatu. Zagadnienia związane z wydajnością, poruszam w ostatnim rozdziale tego kursu.

Przetwarzanie zapytań SQL do wielu tabel

W rozdziale tym znajdziesz opis procesu przetwarzania zapytań SQL, skoncentrowany na klauzuli FROM. Jest to kontynuacja zagadnień, poruszonych w artykule – logiczne przetwarzanie zapytań. Temat ten podzieliłem na dwie części, ze względu na zakres i konieczność omówienia wcześniej zasad łączenia tabel w SQL. Polecam uwadze te dwa artykuły, które prezentują fundamentalne reguły pisania zapytań SQL.

W tej części, znajdziesz informacje o sposobie realizacji zapytań do wielu tabel. Przedstawię wpływ kolejności JOINów na wydajność oraz typowe błędy logiczne.


Zapytania SQL do wielu tabel

Przypomnijmy podstawowe zasady kolejności przetwarzania zapytań SQL. Pierwszym krokiem jest określenie źródeł danych, czyli wykonywanie poleceń umieszczonych we FROM.

W przypadku kwerend odwołujących się do jednego zbioru sprawa jest bardzo prosta. Wynikiem przetwarzania kroku FROM, jest wtedy tabela wirtualna VT1. Zawiera ona wszystkie rekordy ze zbioru źródłowego. Jest tożsama z nią, posiada te same kolumny i rekordy. Zbiór VT1 jest także źródłem kolejnego kroku – przetwarzania WHERE. Jak widać w zapytaniach do jednej tabeli nie ma szczególnej filozofii.

W rzeczywistych środowiskach bazodanowych, kwerendy dotyczą zazwyczaj wielu tabel.

Dla przykładu rozważmy scenariusz zapytania SQL w bazie testowej Northwind. Interesują nas informacje o Klientach z określonego miasta – Madrytu. Chcemy znać detale ich zamówień, złożonych we wrześniu 1996 – nazwy i ilości produktów, które kupili.

Struktura tej bazy danych jest znormalizowana. Informacje które nas interesują, wymagają połączenia 4 tabel.
Zapytania_SQL_do_wielu_tabel_2

Przykładowa kwerenda, realizująca to zadanie, może wyglądać tak :

SELECT  c.CompanyName, o.OrderID, od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
       INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID
       INNER JOIN dbo.Products p on od.ProductID = p.ProductID
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'

Żeby było ciekawiej (trochę zaczepnie), zastosowałem jako pierwsze połączenie – LEFT OUTER JOIN. Chcę tym samym przy okazji analizy złączeń, pokazać jeden z częstszych błędów logicznych.

W wyniku okazuje się, że jest tylko jeden Klient, który w dodatku złożył tylko jedno zamówienie w podanym przedziale dat.
Zapytania_SQL_do_wielu_tabel_3

Zanim przystąpimy do szczegółowej analizy naszego zapytania, możemy podzielić klauzulę FROM na etapy. Bierzemy pod uwagę ilość występujących tu złączeń i związanych z nimi operacji. Mamy trzy złączenia – będą więc trzy etapy.

Zapiszmy FROM bardziej obrazowo :

FROM 
	-- ETAP 1 – łączenie dwóch pierwszych tabel
          dbo.Customers c 	
             LEFT OUTER JOIN  dbo.Orders o      ON c.CustomerID = o.CustomerID 
 
	-- ETAP 2 – do wyniku ETAPu 1 łączymy tabelę dbo.[Order details]
             INNER JOIN dbo.[Order Details] od  ON od.OrderID = o.OrderID     
 
	-- ETAP 3 – do wyniku ETAPu 2 łączymy tabelę dbo.Products
             INNER JOIN dbo.Products p          ON od.ProductID = p.ProductID

Zasady połączeń, obowiązujące w zapytaniach do wielu tabel SQL, są identyczne jak te opisane dla przykładu łączenia dwóch tabel.

Zaczynamy zawsze od łączenia dwóch pierwszych, stojących zaraz po FROM. Możemy także stosować nawiasy (o tym w następnym przykładzie). Mają one priorytet – czyli łączona jest tabela z wynikiem operacji w nawiasach.

Przy łączeniu trzech i więcej tabel, każda kolejna jest dołączana do wyniku poprzedniego kroku. Algorytm działa iteracyjnie od lewej do prawej, aż do przetworzenia ostatniego zbioru. Prześledźmy zatem dokładnie kolejne etapy przetwarzania FROM naszej kwerendy.


ETAP 1

Łączenie zewnętrzne (LEFT OUTER JOIN) pierwszych dwóch tabel :

SELECT  c.CompanyName, o.OrderID 
FROM   dbo.Customers c  
		LEFT OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID

Krok ten, zostanie przetworzony zgodnie z typowymi regułami logicznego przetwarzania złączeń :

  • Pobierz wszystkie dane z tabeli dbo.Customers
  • Pobierz wszystkie dane z tabeli dbo.Orders
  • Wykonaj złączenie zewnętrzne
    • Najpierw iloczyn kartezjański tych dwóch tabel
    • Następnie filtracja rekordów zgodna z wyrażeniami określonymi w ON
    • Dodanie „odfiltrowanych” rekordów z tabeli stojącej po LEWEJ stronie operatora JOIN (z tabeli dbo.Customers). Wartości atrybutów tabeli dbo.Orders dla tych „niedopasowanych elementów” z kroku poprzedniego uzupełnij NULLami.

Zapytania_SQL_do_wielu_tabel_4

W wyniku otrzymamy wszystkich Klientów (całą zawartość tabeli dbo.Customers) wraz z informacjami o ich zleceniach. Zwróć uwagę na pierwsze dwa rekordy. Są to elementy „dorzucone” w krokach charakterystycznych dla połączeń zewnętrznych – klienci którzy nie złożyli żadnych zleceń.

CompanyName                              OrderID
---------------------------------------- -----------
FISSA Fabrica Inter. Salchichas S.A.     NULL
Paris spécialités                        NULL
Vins et alcools Chevalier                10248
Toms Spezialitäten                       10249
...
Richter Supermarkt                       11075
Bon app'                                 11076
Rattlesnake Canyon Grocery               11077

(832 row(s) affected)

ETAP 2

Zbiór wynikowy poprzedniego etapu (VT1.1), będzie tabelą stojącą po LEWEJ stronie kolejnego złączenia. Tym razem będzie to złączenie wewnętrzne. VT1.1 umieściłem dla lepszego zobrazowania w nawiasie.

SELECT  c.CompanyName, o.OrderID , od.Quantity 
FROM   -- w nawiasie wynik ETAPu 1
       (
           dbo.Customers c  
	 LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
       )
           INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID

Wykonywane są więc kroki :

  • Pobierz wszystkie dane ze zbioru będącego wynikiem pierwszego złączenia – VT1.1
  • Pobierz wszystkie dane z tabeli dbo.[Order Details].
  • Wykonaj złączenie wewnętrzne tych zbiorów
    • iloczyn kartezjański
    • filtrowanie rekordów

Zapytania_SQL_do_wielu_tabel_5

CompanyName                              OrderID     Quantity
---------------------------------------- ----------- --------
Vins et alcools Chevalier                10248       12
Vins et alcools Chevalier                10248       10
Vins et alcools Chevalier                10248       5
Toms Spezialitäten                       10249       9
...
Rattlesnake Canyon Grocery               11077       2
Rattlesnake Canyon Grocery               11077       4
Rattlesnake Canyon Grocery               11077       2

(2155 row(s) affected)

Zwróć uwagę co się stało z Klientami, którzy nie złożyli żadnych zleceń. W poprzednim kroku, dzięki LEFT JOIN, byli oni uwzględnieni (dorzuceni do wyniku). Teraz, przy kolejnym złączeniu – elementy te zostały odfiltrowane.

W naszym scenariuszu nie miało to znaczenia na wynik końcowy. W końcu chcemy uzyskać informacje tylko o Klientach, którzy złożyli zamówienia. Tak naprawdę pierwszym złączeniem, powinien być INNER JOIN.

Zdarza się, że początkujący użytkownicy SQL, oczekują w takim przypadku, że w wyniku będą również mieli Klientów bez zleceń. Jest to dość często popełniany błąd logiczny na który chciałem zwrócić Twoją uwagę. Wynika on z niewiedzy, w jakiej kolejności wykonywane są złączenia. Być może także z nadinterpretacji zakresu działania LEFT OUTER JOIN, który przecież dotyczy tylko pierwszego złączenia.

Wróćmy do dalszej analizy naszego przykładu.


ETAP 3

Algorytm powtórzony zostanie jeszcze raz, dołączając finalnie do powyższego wyniku ostatnią tabelę – dbo.Products.

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   -- w nawiasie wynik ETAPu 2
       (
          dbo.Customers c  
          LEFT OUTER JOIN dbo.Orders o on c.CustomerID = o.CustomerID 
          INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID 
       )
          INNER JOIN dbo.Products p on od.ProductID = p.ProductID

Wykonywane są znów standardowe kroki, jak przy każdym złączeniu dwóch zbiorów :

  • Pobierz wszystkie dane ze zbioru będącego wynikiem etapu 2 (umieszczone w nawiasie) – VT1.2.
  • Pobierz wszystkie dane z tabeli dbo.Products.
  • Wykonaj złączenie wewnętrzne tych zbiorów
    • Iloczn kartezjański
    • Filtrowanie rekordów.

Zapytania_SQL_do_wielu_tabel_6

CompanyName                              OrderID     Quantity ProductName
---------------------------------------- ----------- -------- ------------------------------
Vins et alcools Chevalier                10248       12       Queso Cabrales
Vins et alcools Chevalier                10248       10       Singaporean Hokkien Fried Mee
Vins et alcools Chevalier                10248       5        Mozzarella di Giovanni
Toms Spezialitäten                       10249       9        Tofu
...
Rattlesnake Canyon Grocery               11077       2        Röd Kaviar
Rattlesnake Canyon Grocery               11077       4        Rhönbräu Klosterbier
Rattlesnake Canyon Grocery               11077       2        Original Frankfurter grüne Soße

(2155 row(s) affected)

W tym momencie kończy się przetwarzanie klauzuli FROM. Wszystkie złączenia zostały wykonane. Zbiór wynikowy oznaczyłem jako VT2, aby odróżnić go od prostego przypadku zapytania do jednej tabeli (VT1). Zostaje on przekazany do kolejnego kroku – filtrowania rekordów w WHERE. Proces przetwarzania postępuje dalej zgodnie z opisanym tutaj logicznym porządkiem.


Stosowanie nawiasów w złączeniach tabel i filtracja we FROM

Rozważmy teraz scenariusz, ponownie w bazie Northwind, w którym chcemy uzyskać wiedzę o WSZYSTKICH Klientach z Madrytu, wraz z informacją o produktach, które zamówili w grudniu 1996. Jeśli nie złożyli żadnego zamówienia w tym okresie, to również chcemy ich zobaczyć.

Zapytanie realizujące to zadanie, powinno zwrócić następujący zbiór :

Zapytania_SQL_do_wielu_tabel_7

Jest to szczególny przypadek, w którym filtrowanie rekordów ze względu na datę zamówienia, powinno odbywać się we FROM zamiast w WHERE. Zobaczysz tu także praktyczne zastosowanie nawiasów we FROM, dzięki którym możemy sterować logiką połączeń. Nie będę stosował tu podzapytań, tylko czyste, proste złączenia.

Aby uzyskać informacje o wszystkich Klientach z Madrytu z ich zamówieniami (na razie bez filtrowania daty), można zapisać zapytanie w ten sposób :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM    dbo.Customers c  
        LEFT OUTER JOIN 
        ( 
              dbo.Orders o INNER JOIN dbo.[Order Details] od ON od.OrderID = o.OrderID
                           INNER JOIN dbo.Products p ON od.ProductID = p.ProductID 
        ) ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid'

Uzyskamy teraz faktyczny LEFT OUTER JOIN tabeli dbo.Customers wraz ze wszystkimi szczegółami zamówień.

Pierwsze złączenie będzie połączeniem tabeli dbo.Customers z wynikiem operacji ujętych w nawias. Wykonane zostaną najpierw wszystkie złączenia wewnątrz nawiasu. Najpierw tabeli dbo.Orders z dbo.[Order Details]. Następnie do wyniku dołączona zostanie tabela dbo.Products.

Dopiero na samym końcu, zostanie wykonane połączenie zewnętrzne, lewostronne, tabeli dbo.Customers z efektem działań w nawiasie (wszystkie szczegóły zamówień).

Alternatywnie, moglibyśmy połączenia zapisać bez nawiasów, z wykorzystaniem RIGHT OUTER JOIN :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   dbo.Orders o 	
          INNER JOIN dbo.[Order Details] od  ON od.OrderID = o.OrderID
          INNER JOIN dbo.Products p          ON od.ProductID = p.ProductID 
          RIGHT OUTER JOIN dbo.Customers c   ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid'

Wynik będzie identyczny. Najpierw zostaną wykonane kolejno wszystkie INNER JOINy. Na samym końcu dołączona zostanie tabela dbo.Cutomers, dorzucając w ostatnim kroku wszystkich Klientów bez zleceń (RIGHT OUTER JOIN).

Teraz zastanów się, gdzie powinniśmy wykonywać filtrację rekordów jeśli chodzi o daty zamówień. Co uzyskamy stosując filtrację w WHERE ?

SELECT  c.CompanyName, o.OrderID, od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN  
       ( dbo.Orders o INNER JOIN dbo.[Order Details] od ON od.OrderID = o.OrderID
                      INNER JOIN dbo.Products p ON od.ProductID = p.ProductID 
        ) ON c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' 
    and ( o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30' OR o.OrderDate is null)

Zapytania_SQL_do_wielu_tabel_8

Filtracja WHERE jest bezapelacyjna. Raz usunięte rekordy, nie mają prawa się więcej pojawić w zbiorze wynikowym. Nie ma tu mechanizmu „dorzucania” elementów takiego jak w przypadku połączeń zewnętrznych. Dlatego powyższy zapis odsiał nam niestety wszystkich Klientów z Madrytu, którzy złożyli zamówienia, ale w innych datach.

Przypomnę, że chcieliśmy ich także zobaczyć w wyniku końcowym. Straciliśmy więc tutaj wiedzę o firmie „Bólido Comidas preparadas”.

Rozwiązaniem tego problemu jest przeniesienie filtracji ze względu na datę do klauzuli FROM, tak aby dorzucić ewentualnych Klientów z zamówieniami złożonymi poza interesującym nas zakresem dat. Właściwie zapisana kwerenda będzie wyglądała tak :

SELECT  c.CompanyName, o.OrderID , od.Quantity, p.ProductName
FROM   dbo.Customers c  
       LEFT OUTER JOIN 
       ( dbo.Orders o  INNER JOIN dbo.[Order Details] od on od.OrderID = o.OrderID
                       INNER JOIN dbo.Products p on od.ProductID = p.ProductID 
       ) ON c.CustomerID = o.CustomerID 
            and o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30' 
WHERE c.City = 'Madrid'

Dzięki temu zabiegowi, uzyskamy wiedzę o WSZYSTKICH Klientach z Madrytu. Dodatkowo o zamówieniach z określonego przedziału czasowego.


Logiczna a fizyczna kolejność łączenia zbiorów

Fizyczna realizacja połączeń zbiorów przez silnik bazodanowy (plan wykonania) może się różnić od przyjętych w teorii zasad (od lewej do prawej). Jednak wynik zawsze musi być identyczny z rachunkiem teoretycznym.

Zauważ, że czasem warto zajrzeć ciut dalej, np. do filtrowania WHERE aby już na etapie FROM, nie analizować wszystkich rekordów, które i tak zostaną później odfiltrowane. Ponadto fizyczna kolejność wykonywania połączeń, filtracji – też ma znaczenie na wydajność.

Wybór najkrótszej i najbardziej efektywnej metody dostępu do danych, to przede wszystkim zadanie dla optymalizatora zapytań. Najważniejszą zasadą jego działań jest zawsze przewidywalny efekt końcowy. Musi on być w 100% zgodny z opisanymi zasadami teoretycznymi.

Optymalizator potrafi ograniczać i efektywnie łączyć zbiory. Obecne w analizie teoretycznej iloczyny kartezjańskie czy też pobieranie wszystkich wierszy tabeli zazwyczaj nie mają miejsca.
Zapiszmy więc w różny sposób kwerendę realizującą zadanie z przykładu pierwszego :

-- Pierwszą tabelą do której odwołujemy się jest dbo.Customers
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC 
 
-- Pierwszą tabelą do której odwołujemy się jest dbo.Orders
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Orders o 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID 
	inner join dbo.Products p on od.ProductID = p.ProductID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC 
 
-- Pierwszą tabelą do której odwołujemy się jest dbo.Products 
SELECT    c.CompanyName, o.OrderID, o.OrderDate , ProductName
FROM      dbo.Products p 
	inner join dbo.[Order Details] od on od.ProductID = p.ProductID
	inner join dbo.Orders o on od.OrderID = o.OrderID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
WHERE c.City = 'Madrid' AND o.OrderDate BETWEEN '1996-09-01' AND '1996-09-30'
ORDER BY CompanyName, OrderDate DESC

Pomimo, że złączenia zapisane są w różnej kolejności, silnik bazodanowy i tak po swojemu zrealizuje plan działania. Wszystkie mają identyczny :

Execution_PLAN_SQL

Pytanie które samo nasuwa się po analizie tego przypadku. Czy jednak kolejność joinów może mieć znaczenie? Jeśli tak to może spróbujmy „zoptymalizować” zapytanie i na siłę ją zmienić.


Kolejność połączeń a wydajność

Kolejność jest istotna. W końcu każde z takich łączeń, może produkować różnej wielkości zbiory pośrednie. Możemy założyć, że im bardziej selektywnie będą wykonywane złączenia od samego początku, tym bardziej efektywnie zostanie przetworzona cała kwerenda.

Sprawdźmy więc jakie plany zapytań, będą generowane dla pokazanych powyżej zapytań z zastosowaniem siłowego wymuszenia kolejności połączeń.

Mam możliwość ingerowania w proces optymalizacji poprzez zastosowanie HINTów. Do wymuszenia kolejności złączeń służy opcja :

          OPTION (FORCE ORDER)

Która wymusza na optymalizatorze określoną kolejność od LEWEJ do PRAWEJ. Wykonaj poniższy skrypt, który obrazuje 4 kwerendy zwracające te same wyniki. Skorzystaj z opcji Include Actual Execution Plan, aby porównać plany wykonania i ocenić procentowy udział obciążeniowy każdego z tych poleceń.

-- Kwerenda 1 – dajmy szanse optymalizatorowi
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
where c.City = 'Madrid'    and o.OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
 
-- Kwerenda 2 z FORCE ORDER - identycznie zapisana jak Kwerenda 1
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Customers c 
	inner join dbo.Orders o on c.CustomerID = o.CustomerID 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID
	inner join dbo.Products p on od.ProductID = p.ProductID
where c.City = 'Madrid'    and o.OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)
 
-- Kwerenda 3 z FORCE ORDER
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Orders o 
	inner join dbo.[Order Details] od on od.OrderID = o.OrderID 
	inner join dbo.Products p on od.ProductID = p.ProductID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
where City = 'Madrid'    and OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)
 
-- Kwerenda 4 z FORCE ORDER
select c.CompanyName, o.OrderID , o.OrderDate , ProductName
from dbo.Products p 
	inner join dbo.[Order Details] od on od.ProductID = p.ProductID
	inner join dbo.Orders o on od.OrderID = o.OrderID
	inner join  dbo.Customers c on c.CustomerID = o.CustomerID 
where City = 'Madrid'    and OrderDate between '1996-09-01' and  '1996-09-30'
order by CompanyName, OrderDate desc
OPTION (FORCE ORDER)

Wydajnościowo, najlepsza okazała się kwerenda pierwsza, w której pozwoliliśmy optymalizatorowi, wybór własnego planu wykonania. Każda inna, wykorzystująca podpowiedź, siłowego narzucania kolejności działań Wyniki mówią same za siebie :
Zapytania_SQL_do_wielu_tabel_9


Stosowanie hintów bywa pomocne ale musi być stosowane świadomie. Rozkłady, optymalne sposoby dostępu do danych mogą się zmieniać. Raz utworzony plan, może po pewnym czasie nie być optymalnym, dlatego raczej odradzałbym stosowanie tego typu wskazówek początkującym użytkownikom SQL.

Łączenie tabel – pobieranie danych z wielu źródeł

W klauzuli FROM, określamy przede wszystkim źródła (zbiory) z których chcemy pobierać dane. Możliwości w zakresie pobierania danych z jednego zbioru, opisuję w artykule dot. źródeł danych stosowanych we FROM.

W zapytaniu SQL możemy odwoływać się do jednego lub wielu zbiorów. Jeśli chcemy wybierać z przynajmniej dwóch, powinniśmy określić sposób ich połączenia oraz warunki.

W artykule tym, znajdziesz opis pełnego zakresu możliwości FROM – czyli wszystkie sposoby łączenia tabel, zbiorów oraz określania warunków złączeń. Pamiętaj, że mogą to być tabele, widoki, wspólne wyrażenia tablicowe (CTE), zmienne i funkcje tabelaryczne czy podzapytania. Dla uproszczenia, będę je nazywał wymiennie – tabele / zbiory – mając na myśli wszystkie obiekty tabelaryczne, do których możemy odwoływać się we FROM.


Łączenie tabel SQL – zasady ogólne

Dla przypomnienia, FROM jest pierwszym krokiem przetwarzania zapytania. Każdy kolejny, bazuje na pośredniej, wynikowej tabeli wirtualnej, poprzedniego. Zbiór otrzymany po przetworzeniu całego kroku (np. FROM) jest wejściem, następnego (WHERE).

Niezależnie od wybranego typu złączenia, w wyniku przetwarzania FROM, otrzymujemy zawsze zbiór elementów (virtual table VT), opisany za pomocą wszystkich kolumn tabel wejściowych. Nie ma znaczenia czy łączysz dwie czy więcej tabel połączeniem wewnętrznym, zewnętrznym. Elementy (rekordy, wiersze) tabeli wynikowej, będą określone zawsze przez wszystkie atrybuty (kolumny) łączonych zbiorów.

Przykładowo, zbiór wynikowy (VT) operacji łączenia trzech tabel złączeniem wewnętrznym INNER JOIN, będzie opisany przez wszystkie kolumny, trzech tabel wejściowych.
JOIN_02
Inną sprawą jest to, czy będziemy chcieli wszystkie z nich zwracać w kwerendzie. Pewnie nie, ale to określamy dopiero w SELECT. Mamy, więc wyjaśnioną pierwszą kwestię – strukturę zbioru wynikowego tabeli pośredniej.

Po wybraniu tabel, musimy określić jeszcze typ złączenia oraz ich warunki. Tutaj stosujemy w praktyce wiedzę na temat relacyjności bazy – czyli sposobów powiązań tabel między sobą.

Łączenie wielu zbiorów (trzech i więcej) sprowadza się do wielokrotnego wykonania operacji łączenia dwóch tabel
. W kolejnym rozdziale tego kursu, opisuję szczegóły przetwarzania zapytań do wielu tabel.

Zaprezentuję teraz po kolei wszystkie możliwe typy złączeń dwóch tabel, na przykładzie prostego scenariusza.

Istnieje firma X, której część pracowników (tabela EMP) posiada samochód służbowy (tabela CAR). W firmie samochody służbowe mogą być używane przez różne osoby, ale tylko jedna jest bezpośrednio przypisana i jest w pełni za niego odpowiedzialna.
Ponieważ firma dynamicznie się rozwija, część samochodów stoi na placu – nie przypisana jeszcze do nikogo. Obowiązkiem każdego pracownika, z wyjątkiem BOSS’a, jest prowadzenie comiesięcznego rozliczania użytkowanego pojazdu (tabela HIST)

BaseStructure
Kod źródłowy struktur na których prezentuję poszczególne przykłady, do pobrania tutaj.


INNER JOIN – łączenie wewnętrzne

W wyniku złączenia wewnętrznego (INNER JOIN) otrzymujemy tabelę wynikową (VT), składającą się ze wszystkich kolumn tabel wejściowych.

Tabela wynikowa zawierać będzie tylko takie elementy, dla których warunki złączenia wewnętrznego będą spełnione (w logice trójwartościowej, wynik musi być TRUE).
Klauzula FROM wraz z warunkami określonymi w ON jest pierwszym miejscem filtrowania rekordów. Wszystkie elementy dla których wynik nie będzie spełniony (FALSE oraz UNKNOWN), zostaną odrzucone.
INNER_JOIN

Logika łączenia tabel INNER JOIN

Zrozumienie zasad działania złączeń wewnętrzych jest kluczowe. Jest to część wspólna wszystkich typów złączeń INNER oraz OUTER JOIN.

W pierwszym kroku wykonywany jest iloczyn kartezjański obu tabel. Jest to połączenie każdego elementu zbioru A ze wszystkimi zbioru B.

Może to być dla Ciebie trudno do zaakceptowania :), ale uspokoję Cię – logiczne przetwarzanie zapytania a fizyczna jego realizacja to zupełnie co innego. Silnik relacyjny świetnie sobie z tym radzi. Nie oznacza to, że za każdym razem odwołując się do tabeli czytane są wszystkie jej rekordy. Jednak patrząc przez pryzmat zasad panujących w SQL, kroków logicznych – zakładamy, że tak właśnie jest.

Wiem że jeśli choć raz zdarzyło Ci się napisać lub spotkać z iloczynem kartezjańskim, tym trudniej będzie Ci ten fakt zaakceptować. Pod koniec tego akapitu – udowodnię tą zasadę za pomocą prostego przykładu, które napewno Cię przekona.

Po wyznaczeniu iloczynu kartezjańskiego, dla każdego wiersza obliczany jest wynik warunku (lub warunków) określonych w ON. Tu spotykamy się z logiką trójwartościową. Wynik może być spełniony = TRUE, niespełniony = FALSE lub nieznany czyli UNKNOWN (np. porównanie z NULL – więcej na ten temat w artykule o NULL).

Ostatnim krokiem jest odrzucenie wszystkich wierszy niespełniających warunków w ON. W zbiorze wynikowym zostają tylko te elementy, dla których wynik = TRUE.

Całość procesu łączenia wewnętrznego obrazuje poniższy diagram :
INNER_JOIN_01

Załóżmy więc, że w firmie X potrzebny jest raport z informacjami o pracownikach, którzy mają przypisany samochód służbowy.

SELECT * 
FROM dbo.EMP as e INNER JOIN dbo.CAR as c ON e.IdPrac=c.IdPrac

INNER_JOIN_02

INNER_JOIN_03

INNER JOIN jest złączeniem symetrycznym i nie ma specjalnego znaczenia czy łączymy tabelę A z B czy odwrotnie. Podobnie z warunkami w ON. Dla porządku, sensownie jest jednak zachować kolejność atrybutów w ON po tej samej stronie co określenie tabel źródłowych wobec operatora JOIN.

Poniższe warunki łączenia są równoważne :

FROM dbo.EMP e INNER JOIN dbo.CAR c ON e.IdPrac=c.IdPrac
 
FROM dbo.EMP e INNER JOIN dbo.CAR c ON c.IdPrac=e.IdPrac
 
FROM dbo.CAR c INNER JOIN dbo.EMP e ON e.IdPrac=c.IdPrac
 
FROM dbo.CAR c INNER JOIN dbo.EMP e ON c.IdPrac=e.IdPrac

Potencjalne problemy i błędy

To w jaki sposób zapiszemy warunki złączeń, podobnie jak w WHERE może mieć wpływ na wydajność (stosowanie funkcji etc.).

Generalnie INNER JOIN raczej nie stwarza kłopotów. Przyjrzyjmy się jednak tak zapisanemu warunkowi złączenia :

SELECT * 
FROM dbo.EMP e INNER JOIN dbo.CAR c ON e.IdPrac = 1

INNER_JOIN_04

Ponieważ tylko jeden wiersz tabeli dbo.EMP spełnia warunek określony w ON (IdPrac=1), zostaną zwrócone wszystkie wiersze będące wynikiem iloczynu kartzjańskiego tego wiersza z całym zbiorem rekordów tabeli dbo.CAR.

Dowodzi to, że faktycznie wykonywany jest iloczyn kartezjański w logicznym przetwarzaniu złączeń. Najpierw A x B, potem dopiero filtrowanie. Możemy pójść dalej i zapisać warunek w tym przkłądzie jako ON 1=1 – wtedy pełen iloczyn kartezjański gwarantowany bo nic nie zostanie odfiltrowane.


OUTER JOIN – łączenie zewnętrzne

Realizacja dowolnych połączeń zewnętrznych jest wykonywana, w pewnym zakresie, dokładnie tak samo jak wewnętrzne. Trzy pierwsze kroki logicznego przetwarzania są identyczne.

  1. Najpierw wykonywany jest iloczyn kartezjański tabeli A oraz tabeli B (łączymy każdy z każdym).
  2. Dla każdego wiersza, określany jest wynik warunków połączeń (zdefiniowane w ON) – TRUE, FALSE lub UNKNOWN.
  3. Następnie usunięcie wszystkich elementów z pośredniego zbioru wynikowego, dla których wynik połączenia (z p.2) jest różny od TRUE/

W połączeniach wewnętrznych to było wszystko. W zewnętrznych dodany jest jeszcze jeden krok.

W zależności od typu – LEFT, RIGHT lub FULL JOIN, wykonywane jest dopełnienie zbioru, o wszystkie elementy tabeli występującej po LEWEJ, PRAWEJ lub OBYDWU operatora JOIN, dla których wynik warunków nie był spełniony (FALSE lub UNKNOWN).

Brzmi to może trochę zawile, ale jest naprawdę proste i jeśli wiesz już jak działa połączenie wewnętrzne – tutaj dojdzie tylko ten jeden, dodatkowy krok. Zerknij na poniższe przykłady i z pewnością wszystko stanie się jasne.

LEFT OUTER JOIN – połączenie lewostronne otwarte

Postępujemy identycznie jak w INNER JOIN. Na koniec uzupełniamy zbiór wynikowy (INNER JOIN to tylko element C) o wszystkie elementy tabeli stojącej po LEWEJ stronie operatora JOIN (będą to rekordy A oraz B).

Ponieważ wiersze dopełniające muszą być również opisane, przez wszystkie kolumny łączonych tabel.- wartości atrybutów w tym przypadku TabeliB (po prawej stronie JOINa) będą nieznane czyli będą po prostu NULLami.

LEFT_JOIN_01

W naszym scenariuszu, niech będzie to zapytanie wyciągające dane o wszystkich pracownikach pracujących w Firmie oraz informacja czy dany pracownik ma przypisany samochód służbowy.

SELECT e.Imie, e.Nazwisko, e.Stanowisko , c.Marka 
-- LEFT JOIN oraz LEFT OUTER JOIN oznaczają dokładnie to samo
FROM dbo.EMP e LEFT JOIN dbo.CAR c ON e.IdPrac=c.IdPrac

LEFT_JOIN_02

RIGHT OUTER JOIN – łączenie zewnętrzne prawostronne

Ta sama historia co z LEFT JOIN tylko w drugą stronę :). Łączone są najpierw wewnętrznie dwa zbiory (INNER JOIN), na koniec dodawane są wszystkie niedopasowane elementy tabeli po PRAWEJ stronie operatora JOIN (elementy D oraz E). Ponieważ wartości kolumn tabeli po lewej stronie są nieznane, będą NULLami.

RIGHT_JOIN_01

W naszym scenariuszu może to być pytanie o szczegóły wszystkich samochodów służbowych, wraz z dodatkową informacją o osobie przypisanej

select  c.Marka, c.NrRej, c.Rocznik, e.Imie + ' ' + e.Nazwisko as Pracownik
from dbo.EMP e RIGHT JOIN dbo.CAR c on e.IdPrac=c.IdPrac

RIGHT_JOIN_02

Jak łatwo zauważyć, złączenia zewnętrzne LEFT i RIGHT nie są symetryczne. Wynik zależy od pozycji tabel względem operatora JOIN. Nie ma znaczenia zapis warunków (czyli to co jest po ON). Powyższe zapytanie, moglibyśmy zapisać równie dobrze jako połączenie LEFT JOIN i wynik będzie identyczny.

-- zamieniłem tylko kolejność tabel CAR i EMP oraz użyłem LEFT JOIN
SELECT  c.Marka, c.NrRej, c.Rocznik, e.Imie + ' ' + e.Nazwisko as Pracownik
FROM    dbo.CAR c LEFT JOIN dbo.EMP e on e.IdPrac=c.IdPrac

FULL OUTER JOIN – pełne złączenie zewnętrzne

Jeśli wiesz już jak działają INNER, LEFT, RIGHT – to wiesz także jak działa FULL JOIN ! Dopełnieniem zbioru wynikowego są wszystkie elementy obydwu łączonych zbiorów. Podobnie jak poprzednio, wartości nieznanych nie wymyślimy. Elementy dopełniające zbioru A (stojącego po lewej stronie operatora JOIN), będą miały uzupełnione wartości atrybutów tabeli B NULLami. Analogicznie będzie z dopełnieniem drugiego zbioru.

FULL_JOIN_01
W naszym przykładzie będzie to zapytanie zwracające informacje o pełnej relacji – wszystkich pracownikach i samochodach, zgodnie z ich przypisaniem.

SELECT e.Imie, e.Nazwisko, e.Stanowisko , c.Marka 
-- FULL JOIN to skrót od FULL OUTER JOIN 
FROM dbo.EMP e FULL JOIN dbo.CAR c ON e.IdPrac=c.IdPrac

FULL_JOIN_02

CROSS JOIN – iloczyn kartezjański

Raczej rzadko stosowane połączenie zbiorów. Jego sposób działania jest banalny – łączy każdy wiersz tabeli A z każdym wierszem tabeli B. Jako jedyne nie ma możliwości utworzenia warunków połączenia w ON bo z założenia ma połączyć wszystko ze wszystkim.

Skutek łączenia dwóch tabel zawierających po 100 rekordów – to tabela z 10000 wierszami, opisanych za pomocą wszystkich kolumn. Więc jeśli chcesz przetestować wytrzymałość DBA, możesz śmiało spróbować połączyć kilka średniej wielkości tabel :)

Złączenie typu CROSS JOIN jest realizowane również wtedy, gdy wyszczególnimy tabele we FROM, separując je tylko przecinkiem.

SELECT *
FROM dbo.EMP, dbo.CAR

Dlatego powinniśmy unikać stosowania warunków połączeń w WHERE (bardzo stary sposób, niezgodny z ANSI SQL:92).

SELF JOIN – połączenie tabeli z samą sobą

Wszystkie do tej pory prezentowane przykłady, zakładały łączenie dwóch różnych zbiorów. Język SQL jest elastyczny i jeśli coś jest zbiorem, może być użyte we FROM wiele razy.

Połączenia typu SELF JOIN to zawsze jedno z już poznanych – INNER, CROSS lub OUTER JOIN, w T-SQL nie stosuje się zapisu SELF JOIN. W strukturze tabeli dbo.EMP firmy X mamy zdefiniowaną referencję pomiedzy kolumnami IdManager i IdPrac.

select IdPrac, Imie, Nazwisko, Stanowisko, IdManager 
from EMP

SELF_JOIN_00
Wyświetlmy podstawowe dane dla wszystkich pracowników wraz z informacją o bezpośrednim przełożonym.
Ponieważ jest jedna osoba (BOSS), która nie ma przełożonego, musimy w tym zadaniu zastosować połączenie zewnętrzne. Aby móc połączyć dwie te same tabele, koniecznie musimy zastosować aliasy (e1 i e2).

SELECT e1.Imie + ' ' + e1.Nazwisko as Pracownik, e1.Stanowisko, 
	e2.Imie + ' ' + e2.Nazwisko as Manager, e2.Stanowisko as ManStanowisko
FROM dbo.EMP e1 LEFT OUTER JOIN dbo.EMP e2 on e1.IdManager=e2.IdPrac

SELF_JOIN_01

Ten przykład bazuje na istniejącej relacji, kluczu obcym tabeli dbo.EMP do samej siebie, jednak wcale nie musi ta referencja być jawnie i permanentnie określona.

Wszystkie do tej pory prezentowane przykłady, łączyły tabele w naturalny sposób ich powiązań. Po kolumnach będącymi jednocześnie kluczami obcymi/podstawowymi tabel.

Ogólną zasadą łączenia jest możliwość jej realizacji po dowolnych kolumnach. Musi być spełniony tylko jeden warunek – kompatybilność typów danych łączonych atrybutów. To jak zapiszemy warunek i czy będzie miał sens, zależy tylko od nas – język SQL nie ogranicza tu naszej wyobraźni.

Dodatkowo, na wartościach atrybutów po których łączymy, możemy wykonywać dowolne operacje. Przetwarzać je za pomocą funkcji skalarnych, wykonywać działania arytmetyczne, łączenia stringów etc…

Na koniec bardziej „zaawansowany” przykład, łączący różne techniki pisania zapytań prezentowane w tym rozdziale kursu. Będzie obrazował połączenie wewnętrzne typu SELF JOIN tabeli dbo.HIST w której przechowywane są informacje o przebiegu samochodów w postaci „logu”.

Scenariusz jest taki, że co miesiąc, każdy z pracowników, musi wpisać stan licznika swojego samochodu służbowego. Zapytanie ma za zadanie wyświetlenie raportu o przebiegach miesięcznych, każdego samochodu za okres wakacyjny. W tym celu posłużę się technikami wspólnych wyrażeń tablicowych, łączeniem wewnętrznym oraz funkcją ROW_NUMBER(). Zauważ, że wykonuję operacje arytmetyczne na atrybutach łączących (dodaje 1, aby uzyskać przesunięcie odczytów) oraz łączę kilka warunków w klauzuli ON.

WITH LogTab as (
   -- mozesz uruchomić testowo tylko zawartość CTE, 
   -- żeby sprawdzić co zwraca i co będzie sednem (tabela LogTab) której potem używam
   SELECT  * , ROW_NUMBER() OVER(partition by NrRej order by DtPomiaru) as IdUniqueRange  
   FROM  dbo.HIST
   where DtPomiaru between '2012-06-01' and '2012-08-31'
 
)
 
SELECT  l1.NrRej, YEAR(l1.DtPomiaru) as Rok,MONTH(l1.DtPomiaru) as Miesiac, 
	l1.Przebieg as PrzebiegStart,l2.Przebieg as PrzebiegEND,
        l1.Przebieg - l2.Przebieg as Delta
FROM  LogTab l1 INNER JOIN LogTab l2 
	   ON l1.IdUniqueRange = l2.IdUniqueRange+1 and l1.NrRej = l2.NrRej
ORDER BY l1.NrRej, l1.DtPomiaru

SELF_JOIN_02


Zgodność składni łączenia tabel ze standardami ANSI SQL

W praktyce można spotkać różne możliwości określania warunków złączeń.

Przedstawione w tym artykule (INNER, LEFT OUTER, CROSS, warunek ON) są zgodne ze standardem ANSI SQL:92 i powinny być stosowane w produkcyjnych bazach. Czysto informacyjnie, istnieją inne, starsze metody zapisu, które jednak nie powinny być już stosowane.

Łączenie wewnętrzne z warunkiem zamiast w ON – w WHERE.

-- równoważnik INNER JOIN - warunek połączenia dopiero w filtrowaniu w WHERE 
-- UNIKAĆ !!! bo łatwo o pomyłkę i cartesian product gotowy :)
SELECT e.Imie, e.Nazwisko, e.Stanowisko , ISNULL(c.Marka , '-') as Samochod
FROM dbo.EMP e , dbo.CAR c 
WHERE e.IdPrac = c.IdPrac

IS NULL or IS NOT NULL

W relacyjnych systemach baz danych, elementy (wiersze) przechowywane w tabelach, mogą być opisywane przez atrybuty (kolumny) określone jak i nieokreślone, czyli poprostu zawierające wartości bądź nie.

Atrybut nieokreślony to taki, który nie ma wartości. “Wartość” taką, oznaczamy jako NULL (w teorii baz danych NULL oznaczany jest małą grecką literą – omega ω ). Zazwyczaj będzie to atrybut opcjonalny, nieobowiązkowy lub po prostu nie określony.

Ten sposób postrzegania rzeczywistości i modelowania jej w bazach, od lat dostarcza wielu dyskusji z uwagi na potencjalne nieścisłości, które z tego tytułu się pojawiają. Artykuł ten, nie ma na celu odnoszenia się do dyskutowania problemów relacyjnych. Przedstawiam tutaj wszystkie fakty istnienia NULL w SQL Server od strony praktycznej, związanej z pisaniem zapytań.


Logika Trójwartościowa

Z uwagi na to, że nie wszystkie kolumny muszą posiadać określone wartości (dane), wprowadzono pojęcie logiki trójwartościowe. Zgodnie z nią, rezultat porównania dwóch wartości, może przyjmować jeden z 3 stanów :

  • TRUE – gdy wartości są równe
  • FALSE – gdy się różnią
  • UNKNOWN – wynik porównania nie jest znany

Dwóch pierwszych nie ma co omawiać, bo z pewnością były dokładnie wytłumaczone w pierwszych latach szkoły podstawowej. Ciekawszym stanem jest wynik UNKNOWN – który jest związany właśnie z wartością NULL – czyli wartością nieznaną / nieokreśloną.

Wartość NULL definiuje atrybut lub logiczny wynik porównania jako nieokreślony, niezdefiniowany. Może występować w rekordach (elementach zbioru), w kolumnach, którym nie zostały określone wartości.

NULL nie jest równy 0, ani też nie jest to wartość pusta w sensie pustego stringu ”. Lepszym porównaniem byłaby tu próżnia – coś co nic w sobie nie zawiera.


Praca z NULL

Warunki logiczne

Wartości nieokreślonej nie możemy sensownie porównać z czymkolwiek gdyż wynikiem takiego porównania będzie również wartość nieokreślona czyli UNKNOWN. Trudno odnieść się do czegoś czego się nie zna lub czego nie ma – więc jak to porównać. A co w takim razie da porównanie dwóch wartości NULL ?

-- deklaracja zmiennych, bez przypisania wartości, 
-- każdy z nich przechowuje wartość nieznaną NULL
declare @wartosc_a int,@wartosc_b int
 
select CASE WHEN @wartosc_a = @wartosc_b THEN 'EQUAL' 
	    WHEN NOT (@wartosc_a = @wartosc_b ) then 'NOT EQUAL'
	    WHEN @wartosc_a != @wartosc_b then 'VARIOUS'
	    ELSE 'UNKNOWN' END as NULLsComaprisionResult

NULL_01

Wynik porównania dwóch wartości NULL to także wartość nieznana. Nie jest ani większa, ani równa. Podobnie, jeśli byśmy chcieli porównać wartość znaną, z czymś czego nie znamy – wynik zawszę będzie UNKNOWN.

Znajomość NULL skutkuje w wielu miejscach związanych z pisaniem zapytań.
W warunkach filtracji, obojętnie czy będzie to filtracja w WHERE, we FROM – jako warunek złączenia czy w HAVING, zwracane są tylko te wiersze, dla których wynik porównania jest spełniony – czyli zwraca wartość TRUE.

Ponieważ porównanie czegokolwiek z NULL, zwraca wartość UNKNOWN, dlatego w interpretacji filtrowania rekordów, wiersze dla których taki wynik zostanie wyznaczony – będą odrzucone.

Jeśli spróbowalibyśmy odnieść się do wartości NULL w ten sposób :

USE Northwind
GO
 
-- Pomimo istnienia rekordów dla których Region nie jest nullem, 
-- tak skonstruowane zapytanie nic nie zwróci
select * from dbo.Employees
where Region <> null

Zapytanie nie zwróci żadnego rekordu :

(0 row(s) affected)

Pomimo że istnieją rekordy, dla których wartość kolumny Region nie jest NULLem, ale żadne z takich porównań nie zwróci TRUE (chyba że mamy ustawiony brak zgodności ze standardem ANSI – opisuje to w dalszej części artykułu). Wynik jakichkolwiek porównań z NULL to zawsze wartość nieznana UNKNOWN, choć czasem mylnie interpretowany jako FALSE. Dlatego jeśli zależy nam na porównaniach odnoszących się do NULL musimy jawnie używać specjalnie do tego celu stworzonego zapisu czyli IS NULL lub IS NOT NULL :

-- poprawne porównywanie do NULL
select LastName, FirstName, Region from Employees
where Region IS NOT NULL

NULL_02
Z tego samego powodu, zapytanie w którym użyjemy w warunku WHERE, operatora IN – tłumaczonego na szereg warunków logicznych OR, nie zwróci rekordów dla których kolumna wartość zawiera NULL

select LastName, FirstName, Region 
from Employees
-- IN to równoważnik poniższego zapisu : Region = 'WA' or Region = NULL
where Region IN ('WA',NULL)

Wartość NULL w wyrażeniach

Wykonując jakiekolwiek działania na wartościach nieznanych, należy spodziewać się również, że i wynik będzie tak samo dokładny jak i wartości na których operujemy czyli UNKNOWN.
Zobaczmy do jakiego rezultatu doprowadzą, wszelkiego rodzaju działania z wartością nieokreśloną :

Select  100000 + NULL as wynik1,
	   (5000+300) * 2 - null as wynik2,
	   'Ala ma ' + 'kota' + null + '!' as wynik3

NULL_03
Wszystkie operacje z udziałem wartości nieznanej – dają zawsze w wyniku NULL. Niezależnie czy są wartości przechowywane jako zmienne, kolumny czy stałe – cokolwiek połączymy z NULL – da nam NULL.
Ponieważ wartości nieokreślone są często spotykane w bazach, musimy sobie jakoś z nimi radzić. Tylko w jaki sposób je traktować. Pawdopodobnie przy operacjach matematycznych, chcielibyśmy traktować NULL jak 0 natomiast w wyrażeniach łączącym stringi – interpretować jako string pusty ”. Jest to więc bardzo subiektywne, w zależności od konkretnego scenariusza. Rozpatrzmy następujący przypadek :

USE AdventureWorks2008
GO
 
SELECT FirstName + ' ' + MiddleName + ' ' + LastName  AS FullName,
 Firstname, MiddleName, LastName
FROM Person.Person

NULL_04
Kolumna MiddleName, zawiera wartości NULL, stąd wszędzie tam gdzie się pojawia wartość nieokreślona, wynik złączenia tekstu jest również nieokreślony.

Wpływ NULLi na określone działania możemy zniwelować za pomocą wyrażeń warunkowych CASE WHEN, podstawiając w tym przypadku wartość stringu pustego ” w miejsce NULL:

SELECT FirstName + ' ' + 
       CASE WHEN MiddleName is null THEN '' ELSE MiddleName END
       + ' ' + LastName  AS FullName,
       FirstName, MiddleName, LastName
FROM Person.Person

NULL_05
Sposób skuteczny, ale mało wygodny. Mamy na szczęście do dyspozycji kilka funkcji wbudowanych, które pomagają w radzeniu sobie z NULLami i upraszaczją kod SQL.

Funkcje wspierające przy pracy z NULL

ISNULL ( wartość , wartość_zastępcza ) jedna z dwóch najczęściej stosowanych i użytecznych do pracy z NULLami. Podstawia wartość wskazaną w drugim argumencie, jeśli wykryje w danej kolumnie NULL. Zastosujmy ją więc do poprzedniego przykładu, zamiast CASE WHEN :

SELECT FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName  AS FullName
FROM Person.Person

NULL_06
COALESCE ( wartosc_1 , wartosc_2 … wartosc_n ) – zwraca pierwszą nie-nullową wartość z listy. Startuje od wartości podanej jako pierwsza, jeśli nie jest NULLem to ją zwraca a jeśli jest to analizuje kolejną podaną w jej definicji. Równoważnik takiego zapisu np. dla 3 argumentów :

COALESCE ( wartosc_1 , wartosc_2 , wartosc_3)
 
-- równoważnik z wykorzystaniem IS NULL
ISNULL ( ISNULL( wartosc_1 , wartosc_2 ), wartosc_3 )
-- z wykorzystaniem CASE WHEN
CASE WHEN wartosc_1 is not null then wartosc_1
     WHEN wartosc_2 is not null then wartosc_2 
     WHEN wartosc_3
end

NULLIF (wartosc1, wartosc2) – porównuje dwie wartości i zwraca NULL jeśli są równe.
Jest logicznym odpowiednikiem zapisu warunkowego :

CASE WHEN wartosc1 = wartosc2 THEN NULL ELSE wartosc1 END

Logicznie uzasadnione wyjątki

Są wyjątki w implementacji logiki trójwartościowej (TRUE, FALSE, UNKNOWN), które po analizie możemy zakwalifikować do łamiących jej reguły.

Poniższe przypadki, mają na celu pokazanie kilku uzasadnionych odstępstw, aby bardziej świadomie patrzeć na obowiązujące zasady w bazach danych.

Miejsca, gdzie wynik porównania wartości NULL traktowany jest jako TRUE :

  • ORDER BY – wszystkie wartości kolumny, które zawierają NULL i po której sortujemy – są traktowane jako równe – występują obok siebie.
  • GROUP BY – wszystkie elementy, grupowane po atrybucie zawierającym NULL, tworzą jedną grupę, zatem również traktowane są jak równe.
  • UNIQUE CONSTRAINT – w kolumnie unikalnej, gdy jest już w niej wartość NULL, przy próbie wpisania kolejnego, traktowane są jakby NULLe były sobie równe (jeden już jest, więcej nie można).
  • budowanie indeksu na kolumnie zawierającej NULLe – traktuje wartości nieokreślone jak równe. Efektywność indeksu na kolumnie zawierającej nulle maleje.

Porównanie NULL z wartością znaną, czasem daje TRUE

  • jeśli zdefiniujesz ograniczenie np. CHECK CONSTRAINT >0 na kolumnie przechowującej wartości liczbowe. Przy próbie wpisania wartości NULL – wynik porównania będzie spełniony tak jakby NULL faktycznie był większy od 0 (tylko liczby ujemne i 0 nie spełnią tego ograniczenia).

Standard ANSI

Sposób porówynania wartości null opisany do tej pory, jest zgodny ze standardem ANSI SQL:92. W SQL Server, dostępna jest możliwość wyłączenia tej zgodności np. na poziomie połączenia (sesji), poprzez ustawienie

SET ANSI_NULLS OFF

Szczerze mówiąc, nie spotkałem się z przypadkami w rzeczywistych systemach, gdzie korzystało by się z globalnego wyłączenia zgodności ze standardem ANSI w kontekście całej bazy. W sporadycznych sytuacjach, można łatwo zmienić sposób interpretacji porównania z null. Jeśli ustawisz ANSI_NULLS na OFF – wtedy NULL będzie traktowany jak zwykła wartość czyli wynik warunku np. NULL = NULL będzie TRUE.

--Wylaczenie zgodnosci z ansi (domyslnie jest włączone)
SET ANSI_NULLS OFF
 
select FirstName, LastName, Region
from Employees
where Region = NULL

NULL_08

CTE – Common Table Expressions

CTE, czyli wspólne wyrażenia tablicowe, zostały wprowadzone po raz pierwszy w SQL Server 2005, jako rozszerzenie składni T-SQL.

Upraszczają i poprawiają przejrzystość kodu SQL. W tym zakresie, ich stosowanie nie ma wpływu na wydajność zapytań, tylko na jego czytelność. Oprócz funkcji czysto estetycznej, posiadają jeszcze jedną, specjalną właściwość – ich struktura pozwala na realizację rekurencji.


Wspólne wyrażenia tablicowe CTE – czytelność kodu

CTE, możemy stosować praktycznie wszędzie – w widokach, funkcjach, procedurach składowanych, skryptach etc. Składnia jest prosta i omówię ją na przykładzie :

-- definicja wyrażenia tablicowego o nazwie Sales_CTE
WITH Sales_CTE  (SalesPersonID, NumberOfOrders, MaxDate)
AS
(
    SELECT top 5 SalesPersonID, COUNT(*) , MAX(OrderDate) 
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
    ORDER BY 2 ASC
)
-- bezpośrednio po definicji, kwerenda odwołująca się m.in do tego CTE
SELECT P.FirstName, P.LastName, c.NumberOfOrders, c.MaxDate
FROM Sales_CTE c left join Person.Person AS P
    ON c.SalesPersonID = P.BusinessEntityID

CTE_01
Definicję CTE otwiera słowo kluczowe WITH z nazwą zbioru, który zostanie za jej pomocą utworzony. Do niej będziemy odwoływać się w kwerendzie, tak jak do zwykłej tabeli.

Obowiązują te same reguły jak w przypadku każdego obiektu tabelarycznego, do którego chcemy się w jakikolwiek sposób odnosić w zapytaniu.

Nazwy kolumn (atrybutów) muszą być unikalne w ramach zbioru. Jeśli określimy je w „ciele” wyrażenia tablicowego, nie jest wymagane ich ponowne nazywanie w klauzuli WITH – co jest często stosowanym skrótem. W kodzie produkcyjnym, zalecane jest jednak jawne wyszczególnianie nazw.

WITH Sales_CTE
AS
(   -- określenie unikalnych nazw kolumn
    SELECT SalesPersonID, COUNT(*) as NumberOfOrders ,
 MAX(OrderDate) as MaxDate
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
)
SELECT * from Sales

Do raz zdefiniowanego CTE, możemy się odnosić wiele razy w kwerendzie z którą jest związany. Zakres widoczności jest bardzo wąski i obejmuje tylko i wyłącznie zapytanie następujące po nim, ale w pełnym zakresie. Dokładnie tak jakbyśmy odpytywali zbiór, tabelę lub widok, określoną w tym przypadku przez CTE.

Możemy definiować kilka wyrażeń tablicowych i co istotne, widoczne są one nie tylko w kwerendzie powiązanej z WITH, ale również w kolejno określanych CTE (jeśli jest ich więcej niż 1)

WITH CTE_1 -- definicja pierwszego CTE
AS
(
    SELECT SalesPersonID, COUNT(*) as NumberOfOrders ,
	MAX(OrderDate) as MaxDate
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
),  
CTE_2 -- definicja drugiego CTE, możemy odwołaś się już do CTE_1
AS
(
    SELECT SalesPersonID, NumberOfOrders, MaxDate
    FROM CTE_1 
    WHERE NumberOfOrders  < 200 
)
 -- tutaj możemy odwołać się do wszystkich CTE określonych w WITH
SELECT top 10 CTE_1.*, CTE_2.* 
FROM CTE_1 LEFT JOIN CTE_2
	ON CTE_1.SalesPersonID= CTE_2.SalesPersonID

CTE_02
CTE są szczególnie użyteczne w przypadku rozbudowanych zapytań, łączących wiele tabel, które chcemy użyć w kolejnym kroku, wykonując na nich dodatkowe operacje.

Inny przykład zastosowań to skomplikowane wyrażenia w klauzuli SELECT (np. z CASE WHEN) zapytania, które również chcemy dalej przetwarzać, np. połączyć wewnętrznie z samą sobą. Nie będziemy musieli powielać tego samego kodu zapytania lub materializować (choć czasem się opłaca) tabeli pośredniej.

CTE jest po prostu wygodnym opakowaniem, tabel pośrednich, dzięki któremu łatwiej odnaleźć się w kodzie.

Trzeba pamiętać, że słowo kluczowe WITH, używane jest również do innych celów np. do przekazywania hintów (podpowiedzi dla silnika relacyjnego) w zapytaniach. Z tego względu, jeśli chcemy definiować CTE w ramach skryptu zawierającego kilka odrębnych komend T-SQL np. w procedurze składowanej, konieczne jest jawne określania końca poleceń poprzedzających – czyli stosowanie znaku średnika, przynajmniej na końcu polecania SQL poprzedzającego CTE.

Jeśli o tym zapomnimy – parser, zasygnalizuje błąd, bo będzie traktował słowo kluczowe WITH jako kontynuację poprzedniej komendy SQL. Stosowanie ‘;’ jest jedną z dobrych praktyk pisania poleceń SQL i nic nie stoi na przeszkodzie, aby umieszczać je zawsze na koniec komendy.

Rekurencyjne wyrażenia tablicowe (recursive CTE)

Interesującą i bardzo użyteczną właściwością wspólnych wyrażeń tablicowych, jest możliwość stosowania rekurencji w ich wnętrzu. Tego typu funkcjonalność, wykorzystujemy w zbiorach z określoną hierarchią elementów (pracownicy / przełożeni, struktura folderów na dysku, wątków na forum itp.). Rekurencja pozwala rozwiązywać skomplikowane zadania, typu wyznaczanie drzew rozpinających, grafów.

Składnię rekurencyjnych wyrażeń CTE, przeanalizujemy na przykładzie hierarchicznej struktury zatrudnienia w firmie Northwind:

USE NORTHWIND
GO
WITH recursive_CTE
AS
(
        -- zapytanie zakotwiczające, określa korzeń, najwyższy poziom hierarchii
        -- w tym przypadku tylko TOP Management (nie mają przełożonych)
	select EmployeeId, ReportsTo, FirstName, LastName, 0 as Organization_Level 
        from dbo.Employees
	where ReportsTo is null
 
        -- operator UNION ALL – obowiązkowy w rekurencji, 
        -- łączący ostetecznie wyniki z kolejnych iteracji (przebiegów) wykonania
 
	UNION ALL
 
        -- Zapytanie rekurencyjne działające zawsze na zbiorze wynikowym z kroku n-1. 
        -- Zwróć uwagę, że jest powiązane po nazwie wyrażenia tablicowego CTE
 
	select e.EmployeeId, e.ReportsTo, e.FirstName, e.LastName, Organization_Level + 1
	from dbo.Employees e inner join recursive_CTE r 
             on e.ReportsTo = r.EmployeeId
)
Select * from recursive_CTE

CTE_03
Definicja wyrażeń rekurencyjnych CTE składa się z 3 elementów.

  • rozpoczyna się przez określenie zapytania zakotwiczającego. Jest to zazwyczaj zbiór elementów stanowiących korzeń (lub korzenie). W naszym przykładzie jest to TOP management czyli pracownicy, którzy do nikogo nie raportują. Od tych elementów będziemy rekurencyjnie „rozpinać” nasze struktury drzew.
  • Zapytanie rekursywne – skorelowane z wynikiem zwracanym przez zapytanie poprzednie. To tu odwołujemy się do struktury hierarchicznej. W naszym przypadku, w pierwszym kroku jest to powiązanie TOP managementu z bezpośrednimi podwładnymi. W następnym kroku, będzie to powiązanie tych bezopśrednich podwładnych z ich własnymi podwładnymi czyli kolejny poziom niżej itd.
    Operator UNION ALL łączy wszystkie przebiegi w finalny zbiór wynikowy. Ważne jest, aby zrozumieć, że w każdym kroku działamy tylko na zbiorze zwracanym przez krok poprzedni (u nas to będzie za każdym razem, zbiór pracowników, kolejnego, niższego szczebla).
  • – Niejaweny warunek zakończenia rekurencji. Jeśli zapytanie rekurencyjne, skorelowane, nie zwróci żadego elementy, działanie CTE zostaje porzerwane.

Istnieje kilka istotnych faktów związanych z tą strukturą. Warunek zakończenia jest niejawny, dlatego jeśli w naszych danych występują cykle, i rekurencja wpadnie w taką pętle, zapytanie CTE zostanie przerwane dopiero po osiągnięciu maksymalnego poziomu – 32767 przebiegów.

Może to trwać bardzo długo. Na szczęście możemy jawnie określić maksymalną ilość wykonywanych przebiegów za pomocą opcji MAXRECURSION :

WITH recursive_CTE
AS
(       
	select EmployeeId, ReportsTo, FirstName, LastName, 0 as Organization_Level 
        from dbo.Employees
	where ReportsTo is null
 
	UNION ALL
 
	select e.EmployeeId, e.ReportsTo, e.FirstName, e.LastName, Organization_Level + 1
	from dbo.Employees e inner join recursive_CTE r on e.ReportsTo = r.EmployeeId
)
Select * from recursive_CTE
option (maxrecursion 1)

CTE_04
W sytuacji gdy CTE samo nie zakończy działania na n-tym przebiegu, czyli jeśli zwróci w n-tym kroku niepusty zbiór, opcja ‘MAXRECURSION n’ wymusi jej zakończenie. Zostanie również zwrócona informacji o błędzie :

Msg 530, Level 16, State 1, Line 2
The statement terminated. The maximum recursion 1 has been exhausted before statement completion.

Jeśli chcemy umieszczać tego typu „hamulce”, trzeba zapewnić obsługę błędów (np. za pomocą try catch). Maksymalna liczba przebiegów które możemy z góry określić za pomocą MAXRECURSION to 32767. Ewentualnie pozostaje opcja “infinitive” czyli MAXRECURSION = 0.