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.

Logiczne przetwarzanie zapytań SQL

Naukę http://www.dreamstime.com/stock-image-handshake-abstract-cogwheels-blackboard-green-blackboard-hand-image44667161języka SQL w zakresie pisania zapytań, powinieneś rozpocząć właśnie od tego rozdziału. Zrozumienie procesu przetwarzania kwerendy jest niezbędne, aby w pełni świadomie tworzyć polecenia SQL i móc wykorzystać ich możliwości.

W artykule tym, znajdziesz informacje o kolejności kroków przetwarzania zapytań.

Analizując ten proces, nie skupiaj się proszę na szczegółach i możliwościach konkretnych poleceń. Chciałbym abyś uchwycił tu ogólne zasady, w jaki sposób wykonywane są kwerendy. Temat ten, z uwagi na zakres materiału podzieliłem na dwie części. W tej, omawiam pełen proces na prostym przykładzie zapytania do pojedynczej tabeli.

Druga część wymaga znajomości zasad łączenia tabel i umieściłem ją zaraz po rozdziale opisującym te zagadnienia. Wyjaśniam w niej aspekty kolejności łączenia tabel.


Kolejność kroków przetwarzania zapytań

Wykonywanie każdej kwerendy przez silnik bazodanowy odbywa się krokowo, w ściśle określonej kolejności. Reguły te są spójne dla wszystkich relacyjnych baz danych.

W konstrukcji zapytań możemy wyszczególnić 6 głównych bloków logicznych.

(Step 5)   SELECT   -- określanie kształtu wyniku, selekcja pionowa (kolumn)
(Step 1)   FROM     -- określenie źródła (źródeł) i relacji między nimi
(Step 2)   WHERE    -- filtracja rekordów
(Step 3)   GROUP BY -- grupowanie rekordów
(Step 4)   HAVING   -- filtrowanie grup
(Step 6)   ORDER BY -- sortowanie wyniku

W ramach każdego z nich, możemy umieszczać całkiem złożone konstrukcje. Podzapytania, zapytania skorelowane, dodatkowe polecenia, takie jak operator DISTINCT czy TOP w SELECT. Poszczególne klauzule i zakres ich możliwości omawiam szczegółowo w kolejnych rozdziałach tego kursu.

Najprostsze polecenie SQL może składać się z samego SELECTa np. :

SELECT 'Hello World!'

Takie „zapytania” rzadko kiedy nas interesują i tak naprawdę trudno nawet nazwać je zapytaniami. Zazwyczaj chcemy pobierać dane z jakiegoś źródła np. tabeli czy widoku. Do ich określenia, służy klauzula FROM. Ustalmy więc, że kwerenda to konstrukcja składająca się przynajmniej z bloków SELECT oraz FROM.

Poza nimi, wszystkie pozostałe są opcjonalne. Warto jednak zauważyć, że stosowanie HAVING bez GROUP BY również za bardzo nie będzie miało sensu. Z punktu widzenia samej konstrukcji jest jednak możliwe.

Wynikiem przetwarzania każdego z kroków jest tabela wirtualna (VT) , będąca jednocześnie obiektem wejściowym kolejnego etapu. Do tabel wirtualnych, czyli produktów pośrednich kroków przetwarzania, nie mamy dostępu z zewnątrz. Są one logicznymi strukturami, które rozpatrujemy tylko w kontekście analizy teoretycznej.

Trzeba podkreślić, że faktyczny sposób realizacji zapytań, jest wykonywany za pomocą efektywnych i zoptymalizowanych przez silnik bazodanowy metod. W efekcie końcowym, pozwalają one na osiągnięcie dokładnie tego samego wyniku co w analizie teoretycznej. Podkreślam to, bo szczególnie opis iloczynów kartezjańskich czy konieczność czytania całej zawartości tabel może słusznie budzić w Tobie niedowierzanie czy wewnętrzny sprzeciw :)

W analizie teoretycznej tak właśnie się dzieje, aby osiągnąć określony wynik. W praktyce, silnik bazodanowy zna skróty, które doprowadzają dokładnie do tego samego celu – znacznie szybciej.


Analiza przetwarzania kwerendy na przykładzie

Cały proces najlepiej prześledzić na przykładzie. Na początek coś prostego – zapytanie pobierające dane tylko z jednej tabeli dbo.Pracownicy. Zawartość tego zbioru jest następująca :

IdPrac Nazwisko        Placa                 Zespol     Dzial
------ --------------- --------------------- ---------- ----------
1      Kasprzak        3000,00               DBA        IT
2      Norwid          2200,00               DBA        IT
3      Walewska        4000,00               Security   IT
4      Nowak           1300,00               Detal      Sprzedaz
5      Piotrowska      2300,00               Hurt       Sprzedaz
6      Lewandowski     3300,00               DBA        IT
7      Tusk            1100,00               HelpDesk   IT
8      Podemski        9500,00               Hurt       Sprzedaz
9      Gmoch           1750,00               Security   IT
10     Walendziak      1750,00               NULL       NULL

(10 row(s) affected)

W naszym scenariuszu, będziemy chcieli wyświetlić informacje o liczbie pracowników, pracujących w zespołach wieloosobowych (więcej niż jeden pracownik) w ramach Działu IT. Końcowy wynik posortujemy rosnąco po liczbie pracowników.

Kwerenda realizująca to zadanie będzie wyglądała następująco :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Zapytanie to wykorzystuje wszystkie bloki logiczne z jakich może składać się dowolna kwerenda. Na razie celowo nie omawiam logiki łączenia tabel (pobieramy dane tylko z jednego zbioru), aby lepiej przedstawić generalne zasady.

Krok 1 : FROM – określenie źródła (źródeł) i relacji między nimi

Na początku trzeba określić skąd będziemy czerpać dane. Dlatego pierwszym krokiem jest klauzula FROM. W naszym przykładzie zbiorem wejściowym jest jedna tabela – dbo.Pracownicy. Wynikiem przetwarzania pierwszego kroku będzie zatem tabela wirtualna VT1, zawierającą całą (tak, CAŁĄ !) zawartość tego zbioru.

W tym momencie powinna zapalić się u Ciebie lampka ostrzegawcza. Jak to? Czy faktycznie tak jest, że jak odpytuję tabelę zawierającą 100 milionów rekordów to wszystkie te elementy są czytane i przekazywane do jakiejś VT1? Teoretycznie, zgodnie z zasadami logicznego przetwarzania – tak właśnie się dzieje.

Tylko teoretycznie. Jak już wspominałem, w praktyce silnik serwera zna efektywne sposoby dostępu do danych. Z pewnością jeśli nie są potrzebne wszystkie dane tabeli w kolejnych krokach, to nie będzie do nich sięgał.

Zawartość tabeli VT1 będzie więc wyglądała identycznie jak tabeli dbo.Pracownicy :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy                                    -- wynik VT1WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników
VT1

VT1

Krok 2 : WHERE – filtrowanie rekordów

Krok ten, działa na wyniku poprzedniego. Czyli w naszym wypadku będzie operował na tabeli VT1. Może to się wydawać oczywiste, ale świadomie tu podkreślę, że filtrować (wyszukiwać) możemy rekordy tylko takie, które są obecne w tabeli VT1.

W WHERE, dla każdego wiersza wyznaczany jest wynik logiczny zastosowanych wyrażeń (warunków). W naszym przykładzie stawiamy tylko jeden warunek. Interesują nas elementy zbioru VT1, dla których wartość kolumny Dział jest równa ciągowi znaków ‚IT’.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'                                     -- wynik VT2GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Do tabeli wynikowej VT2 – czyli rezultatu przetwarzania kroku WHERE, zostaną wstawione tylko takie rekordy, dla których wynik postawionych warunków (wyrażeń) będzie TRUE.

Przyjrzyjmy się zatem, jakie wyniki logiczne naszego wyrażenia, przyjmą kolejne rekordy.

Wynik logiczny wyrażeń w WHERE

Wynik logiczny wyrażeń określonych w WHERE

Relacyjne systemy baz danych, w tym także SQL Server, bazują na logice trójwartościowej. Jak sama nazwa wskazuje, wynik porównań może przyjmować trzy wartości – TRUE, FALSE lub UNKNOWN. TRUE oraz FALSE nie trzeba tłumaczyć – albo jest coś równe czemuś, albo nie.

Wartość UNKNKOWN, czyli nieznana została wyznaczona dla ostatniego (10) rekordu. Jest ona bezpośrednio związana z koncepcją wartości nieokreślonych – NULL. Jakiekolwiek porównanie, czy operacje z NULLem, skutkują zawsze wartością nieznaną – UNKNOWN. Szczegółowe wyjaśnienie tego zagadnienia, znajdziesz w artykule poświęconym NULL.

Podsumowując, do kolejnego kroku, zostanie przekazana tabela VT2. Zawierać będzie tylko 6 rekordów – te dla których wynik wyrażeń wynosi TRUE.

VT2 - wynik filtrowania WHERE

VT2 – wynik filtrowania WHERE

Krok 3 – GROUP BY – grupowanie rekordów

Podobnie jak filtrowanie, GROUP BY jest blokiem opcjonalnym. W tym kroku tworzone są grupy rekordów. Definicję grupy tworzą kolumny (atrybuty) grupujące, wyszczególnione w GROUP BY.

W naszym prostym przykładzie, potrzebujemy znać informacje o liczbie pracowników w ramach zespołów. Grupować będziemy po kolumnie Zespol.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol                                        -- wynik VT3HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

Każdy element tabeli wejściowej, czyli wynik pochodzący z kroku WHERE – VT2, może znaleźć się tylko w jednej grupie. Utworzonych zostanie tyle grup, ile jest unikalnych wartości w kombinacjach wszystkich kolumn grupujących. W naszym przypadku utworzone zostaną 3 grupy rekordów, reprezentujące zespoły DBA, HelpDesk oraz Security :

GROUP BY - tworzenie grup rekordów

GROUP BY – tworzenie grup rekordów

Ten krok przetwarzania jest szczególny, gdyż jego istnienie wprowadza bardzo ważne konsekwencje we wszystkich kolejnych krokach. Każda grupa, reprezentowana będzie przez jeden rekord tabeli wynikowej VT3, określony wartościami kolumn grupujących.

Sednem działania tego kroku, jest utworzone dwóch sekcji danych. Sekcji grupującej – opisanej przez kolumny, według których grupujemy oraz sekcji danych surowych. Ta z kolei, zawiera wszystkie pozostałe atrybuty (kolumny) nie występujące w klauzuli GROUP BY.

GROUP BY - sekcja grupująca i danych surowych

GROUP BY – sekcja grupująca i danych surowych

Od tego momentu, bezpośrednio odwoływać się można tylko do kolumn sekcji grupującej. To one tworzą elementy tabeli VT3 – są po prostu ich atrybutami.

Tabela VT3 zawierać będzie 3 elementy (rekordy), opisane atrybutem Zespol. Z każdym z tych rekordów będzie skojarzona sekcja danych surowych, tworzona przez wszystkie pozostałe kolumny. Do kolumn sekcji danych surowych, możemy odwoływać się w kolejnych krokach, tylko poprzez funkcje agregujące – np. COUNT().

Jest to całkiem logiczne. Zastanów się, jaką wartość kolumny Nazwisko, silnik bazodanowy miałby zwrócić w reprezentacji relacyjnej dla rekordu grupy DBA. Pierwsze? Ostatnie? A może losowe? Nic z tych rzeczy. SQL bazuje na matematycznej teorii zbiorów i nie ma w niej miejsca na przypadek. Wszystko musi odbywać się wedle ściśle określonych zasad.

W wyniku działania GROUP BY, otrzymamy tabelę VT3 zawierającą 3 rekordy. Każdy z nich, reprezentuje grupę. Z każdą z grup, skojarzona jest sekcja surowa. Celem lepszego zobrazowania, poniżej przedstawiam zawartość tabeli VT3. Dodatkowe trzy kolumny, pokazują wynik działania funkcji agregujących.

COUNT(idPrac) – zlicza wystąpienia wartości idPrac, wszystkich rekordów w ramach sekcji danych surowych. MAX i MIN zwracają największą i najmniejszą wartość w kolumnie Nazwisko.

SELECT Zespol , COUNT( IdPrac ) as COUNT_Raw_Data_Records, 
	MIN( Nazwisko ) MIN_Nazwisko, MAX( Nazwisko ) MAX_Nazwisko 
FROM dbo.Pracownicy
WHERE Dzial='IT'
GROUP BY Zespol
VT3 + dodatkowe kolumny - wynik funkcji agregujących

VT3 + dodatkowe kolumny – wynik funkcji agregujących

Krok 4 – HAVING – filtrowanie grup

HAVING jest bezpośrednio związany z operacjami na grupach. Jest więc logicznym, że ten krok przetwarzania nastąpi po etapie tworzenia grup czyli po GROUP BY. Ogólne zasady filtrowania są identyczne jak w WHERE. Do tabeli wynikowej VT4, trafią tylko takie rekordy (grupy), dla których wynik wyrażeń logicznych będzie TRUE.

Budując warunki filtracji grup rekordów, trzeba pamiętać o konsekwencji grupowania. Bezpośrednio odwoływać możemy się tylko do kolumn sekcji grupującej, do pozostałych za pośrednictwem funkcji agregujących.

W naszym scenariuszu, interesuje nas liczba elementów w sekcji danych surowych w obrębie grupy. Czyli z ilu pracowników składa się każda grupa. Wynik logiczny określony w HAVING, spełniają dwa rekordy i tylko te dwie grupy znajdą się w tabeli wynikowej VT4.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1                            -- wynik VT4ORDER BY LiczbaPracowników
HAVING - filtrowanie grup

HAVING – filtrowanie grup

Krok 5 – SELECT – selekcja pionowa i kształtowanie wyniku

SELECT odpowiada za ostateczny kształt zbioru wynikowego czyli prezentowanie wyników. Możemy dokonać wyboru interesujących nas kolumn (selekcja pionowa), przekształceń (np. dodać do siebie wartości dwóch kolumn) czy wywoływać funkcje skalarne.

Pełne możliwości tego kroku opisuje w rozdziale poświęconym SELECT. Warto wspomnieć o dwóch dodatkowych operatorach, które mogą się w SELECT pojawić. DISTINCT – czyli usuwanie duplikatów oraz TOP – ograniczenie wyników.

Z punktu widzenia kolejności wykonywanych kroków, najpierw wykonywane są wszelkie działania mające na celu ostateczne określenie wartości kolumn (np. złączenia stringów czy obliczenia matematyczne). Następnie, jeśli jest stosowane polecenie DISTINCT – usuwane są wszystkie duplikaty. Polecenie TOP – wykonywane jest na samym końcu przetwarzania kwerendy, czyli po sortowaniu.

Wynik działania naszej kwerendy – kroku SELECT – czyli zawartość VT5 będzie następująca :

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników   -- wynik VT5FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników
VT5 - wynik SELECT

VT5 – wynik SELECT

Jeśli nadajesz tutaj aliasy (alternatywne nazwy kolumn), widoczne one będą tylko w ostatnim kroku – ORDER BY. Przecież te nazwy, to nic innego jak atrybuty tabeli VT5 – czyli produktu przetwarzania SELECT. Taki zapis będzie więc nieprawidłowy :

-- alias LiczbaPracownikow, znany będzie dopiero w kroku
-- następujących po SELECT czyli w ORDER BY
SELECT z.Nazwa as Zespol , COUNT(*) as LiczbaPracownikow,  LiczbaPracownikow + 1
......

Krok 6 : ORDER BY – sortowanie wyniku

Sortujemy oczywiście efekt kroku SELECT, czyli tabelę VT5. Tylko tutaj w kwerendzie, możemy odwoływać się do zastosowanych w SELECT aliasów nazw kolumn.

SELECT Zespol , COUNT( IdPrac ) AS LiczbaPracowników
FROM dbo.Pracownicy 
WHERE Dzial = 'IT'
GROUP BY Zespol
HAVING COUNT ( IdPrac ) > 1 
ORDER BY LiczbaPracowników

W tym kroku otrzymujemy finalny rezultat przetwarzania kwerendy – posortowany w określony sposób zbiór elementów (domyślnie rosnąco ASCending).

Finalny rezultat kwerendy

Finalny rezultat kwerendy

Wyjątkiem jest stosowanie w SELECT polecenia TOP – ograniczające ilość zwracanych rekordów. Wtedy, wykonywana jest dodatkowa filtracja ze względu na istnienie tego polecenia.

Jednym z ciekawszych możliwości ORDER BY, jest wprowadzona w dialekt T-SQL (SQL Server 2012) możliwość stronnicowania wyników – opisuję to w artykule poświęconym ORDER BY.


Podsumowanie

W SQL Server dostępnych jest szereg rozszerzeń funkcjonalnych pisania kwerend. Na przykład elementy składniowe stosowane do tworzenia i przeszukiwania dokumentów XML (FOR XML, XQuery, OPENXML) czy tabel przestawnych (PIVOT, UNPIVOT).

Nie stanowią one jednak fundamentu logiki przetwarzania zapytań.

Opisany powyżej proces, ma zastosowanie do wszystkich typowych zapytań. Oczywiście można je mocno skomplikować, stosując np. zapytania skorelowane czy podzapytania. Jednak ich przetwarzanie i tak odbywa się zgodnie z opisanym powyżej sposobem.

Kolejnym krokiem w poznawaniu SQL, jest wiedza na temat wymienionych tu bloków funkcjonalnych oraz zasad łączenia tabel. Opisuję te zagadnienia w kolejnych rozdziałach tego kursu.

Łą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

HAVING – filtrowanie grup

Jest to trzeci i ostatni krok, w którym możemy filtrować elementy zbioru wynikowego. Różni się zasadniczo od poznanych do tej pory, związanych z selekcją wierszy we FROM (gdzie filtrem są warunki złączeń z innymi tabelami) oraz WHERE.

(5)     SELECT
(1)	FROM
(2)	WHERE
(3)	GROUP BY
(4)     HAVING(6)     ORDER BY

Operacja grupowania opisana w artykule na temat GROUP BY, wprowadza pewne ograniczenia. W każdym kolejnym kroku po GROUP BY, odwoływać się możemy bezpośrednio tylko do atrybutów (kolumn) sekcji grupującej. Do pozostałych kolumn (sekcji danych surowych) tylko za pośrednictwem funkcji agregujących.

HAVING jest kolejnym krokiem po GROUP BY – działamy zatem na całych grupach wierszy. Jest to tak zwana selekcja pozioma grup wierszy. Warunki określone w WHERE, traktujemy jako selekcję poziomą pojedynczych rekordów.


Sposób działania HAVING

Najlepiej pokazać to na przykładzie. W naszym scenariuszu, będziemy działać na bazie Northwind. Załóżmy że potrzebujemy wyciągnąć informacje o miastach w Brazylii, w których mamy więcej niż jednego Klienta (z tabeli dbo.Customers).

Pierwszym krokiem, będzie selekcja pozioma wierszy (WHERE) do odfiltrowania wszystkich Klientów z Brazylii. Działanie tego filtra jest proste, wybieramy precyzyjnie tylko te pojedyncze rekordy, których Country = ’Brazil’. Fragment działania filtra w WHERE obrazuje poniższy zrzut :
Having_01
Otrzymamy wyselekcjonowane wiersze zgodnie z definicją filtra w WHERE.

select Country, CIty, CustomerID , ContactName, CompanyName  
from dbo.Customers
WHERE Country = 'Brazil'

Having_02
Kolejnym krokiem analizy naszych danych, niech będzie wyciągnięcie informacji o liczbie Klientów z danego miasta w Brazylii.

select CIty, COUNT(CustomerID) as CustQty
from dbo.Customers
WHERE Country = 'Brazil'
GROUP BY City

Having_03
Filtrowanie w HAVING, polega na filtrowaniu całych grup rekordów. Zgodnie z zasadą opisaną na początku artykułu, możemy filtrować po kolumnach grupujących lub pozostałych, za pośrednictwem funkcji agregujących. W tym momencie, chcemy właśnie filtrować grupy rekordów, ze względu na ilość elementów (liczby klientów) w ich ramach.

Cel ten zrealizuje filtrowanie za pomocą HAVING. Filtrem będzie wynik funkcją agregującej COUNT(), wybierający tylko te grupy, dla których ilość wierszy (Klientów) będzie większa od 1.

select City, COUNT(CustomerID) as CustQty
from dbo.Customers
WHERE Country = 'Brazil'
GROUP BY City
HAVING COUNT(CustomerID)>1

Having_04
Tworzenia filtrów w HAVING podobnie jak w WHERE, umożliwia łączenie wielu warunków ze sobą, za pomocą operatorów logicznych AND i OR. Powyższe zapytanie, moglibyśmy również zapisać w ten sposób :

select Country,City, COUNT(CustomerID) as CustQty
from dbo.Customers
GROUP BY Country, City
HAVING Country = 'Brazil' AND COUNT(CustomerID) >1

Wynik działania będzie identyczny. Zauważ jednak, że istnieje różnica logiczna w jego przetworzeniu. Przynajmniej teoretycznie, w tym przypadku, całość filtracji odbędzie się tylko w kroku HAVING.
W praktyce optymalizator i tak zastosuje filtrację w pierwszym kroku przetwarzania, minimalizując liczbę rekordów, które będzie przetwarzał w kolejnych etapach. Zobaczyć można to na planie wykonania. Obydwa zapytania posiadają identyczny plan.
Having_06
Having_05


Podsumowanie

Ważne jest abyś dobrze zrozumiał, w jaki sposób wykonywane są zapytania. Pozwala to zapobiec popełnianiu, najtrudniej wykrywalnych błędów logicznych.

Pamiętać należy również, że funkcje agregujące pomijają w kalkulacjach wartości null. Więcej na temat grupowania, having oraz stosowania funkcji agregujacych znajdziesz w rozdziale opisującym szerzej, praktyczne aspekty pisania zapytań.