14. Praca z podzapytańami, tabelami tymczasowymi i zmiennymi tabelarycznymi

Zapytanie SELECT może być zagnieżdżone w dowolnym z poleceń INSERT, UPDATE, DELETE i oczywiście innym poleceniu SELECT. Większość z podzapytań może być zrealizowana za pomocą złączeń. Jednakże nie zawsze jest to możliwe. Jeśli jednak jest taka możliwość, powinniśmy zastosować właśnie złączenia. Poprawi to wydajność zapytania, a przez to czas jego wykonywania.
Składnia samego podzapytania jest dość prosta. Każde z nich zamykamy w nawiasach okrągłych i każde może zawierać dowolną ilość podzapytań. Ograniczeniem jak zwykle jest tylko zdrowy rozsądek i możliwości naszego serwera. Czas na przykład. Napiszemy teraz skrypt który zwraca wszystkich wiceprzewodniczących znajdujących się w firmie AdventureWorks.

SELECT FirstName, LastName
FROM   Person.Contact
WHERE  (ContactID IN
                  (SELECT ContactID
                  FROM    HumanResources.Employee
                  WHERE   (Title LIKE '%Vice President%')))

Zapytanie zagnieżdżone zwraca wszystkie kontakty z tabeli Employee w schemacie HumanResources które piastują pozycje wiceprzewodniczącego. Następnie pytanie zewnętrzne wybiera imię i nazwisko tych osób które mają ContactID w wynikach zapytaniach wewnętrznego. Można było by to przetłumaczyć w następujący sposób.

SELECT   FirstName, LastName
FROM     Person.Contact
WHERE    ContactID IN (1001, 1052, 1010)

Gdzie liczby w zbiorze (w nawiasach okrągłych) są wynikami z zapytania zwracającego odpowiednie ContactID.
Do tego samego wyniku udałoby nam się dojść za pomocą złączeń. Alternatywne zapytanie wyglądało by tak:

SELECT   Person.Contact.FirstName, Person.Contact.LastName
FROM     Person.Contact INNER JOIN
         HumanResources.Employee ON Person.Contact.ContactID = HumanResources.Employee.ContactID
WHERE    (HumanResources.Employee.Title LIKE '%Vice President%')

W pierwszym i drugim przykładzie podzapytania zwracały tylko jedną kolumnę. W takich przypadkach można użyć operatorów =, <, >, <=, >=, <>. Podzapytanie może jednak zwracać też wiele wierszy, które mogą być używane do sprawdzania istnienia wartości. W tym przypadku używamy słowa kluczowego EXISTS w klauzuli WHERE.

SELECT FirstName, LastName
FROM   Person.Contact
WHERE  EXISTS
       (SELECT ContactID, Title
       FROM    HumanResources.Employee
       WHERE   (Person.Contact.ContactID = ContactID) AND (Title LIKE '%Vice President%'))

W klauzuli WHERE mamy bezpośrednio podzapytanie, które zwraca tabelę z wierszami. Polecenie EXISTS sprawdza, czy jakakolwiek wartość jest zwracana przez zapytanie wewnętrzne. Jeśli tak, zwraca to zapytanie zewnętrzne i wypisuje imię i nazwisko, w przeciwnym wypadku pomija rekord. Możemy tu zauważyć dość charakterystyczną dla podzapytań klauzulę WHERE w zapytaniu wewnętrznym. Pobiera ona wartości z tabeli zapytania zewnętrznego i porównuje je z wartością zwracaną przez podzapytanie. Przy podawaniu ograniczeń w podzapytaniach należy pamiętać o jednej bardzo ważnej zasadzie. Wszystkie wartości, które wywodzą się z zapytań zewnętrznych są widoczne w zapytaniach wewnętrznych, ale nie na odwrót! Uporządkowując ograniczenia, które tyczą się podzapytań.

1. nie może być używane w klauzuli ORDER BY (nie ma to po prostu sensu jeśli i tak wybieramy zbiór danych)
2. zapytanie wewnętrzne musi być ujęte w nawiasy okrągłe
3. w klauzuli WHERE IN nie może znajdować się więcej niż jedna kolumna

Dla tych, którzy dopiero zaczynają przygodę z SQL podzapytania na pierwszy rzut oka nie wydają się zbyt zachęcające. Jednakże zrozumienie mechanizmu tworzenia zapytań zagnieżdżonych jest kwestią dość kluczową w operacjach na profesjonalnych bazach danych. Zignorowanie tego rozdziału może niekiedy uniemożliwić skuteczne pobieranie odpowiednich danych.

Jednakże jakby w tym celu zostały wprowadzone dwie inne struktury: tabele tymczasowe oraz zmienne tabelaryczne. Tabele tymczasowe znajdowały się już we wcześniejszej wersji serwera, ale zmienne tabelaryczne to jedno z usprawnień, które zostało dodane dopiero w SQL Server 2005.

Tabele tymczasowe są strukturami, które umożliwiają przechowywanie danych z wyjścia jednego zapytania SQL i użycia ich w następnym zapytaniu. Tymczasowa struktura istnieje w pamięci na czas sesji z serwerem, po czym jest niszczona.

SELECT ContactID
INTO   #tblTemporary
FROM   HumanResources.Employee
WHERE  (Title LIKE '%Vice President%')

SELECT Person.Contact.FirstName, Person.Contact.LastName
FROM   Person.Contact INNER JOIN #tblTemporary ON Person.Contact.ContactID = #tblTemporary.ContactID

Należy dodatkowo, zdawać sobie sprawę, że tabela tymczasowa jest widoczna tylko dla użytkownika, który ją stworzył i nikogo więcej. Jeśli chcielibyśmy stworzyć tabelę tymczasową, która byłaby widoczna dla wszystkich użytkowników, należałoby umieścić dwa znaki # przed nazwą tabeli.

Jak wcześniej wspomniałem, podobną z punktu widzenia funkcjonalności do tabel tymczasowych strukturą są zmienne tabelaryczne. Zmienne tabelaryczne są nowym typem danych, który został wprowadzony w MS SQL Server 2005. Mamy teraz możliwość zadeklarowania zmiennej, która będzie odzwierciedlała całą tabele.

DECLARE @tblTemporary TABLE(ContactID int)

INSERT INTO @tblTemporary(HumanResources.Employee.ContactID)
SELECT  HumanResources.Employee.ContactID
FROM    HumanResources.Employee
WHERE   (Title LIKE '%Vice President%')

SELECT  Person.Contact.FirstName, Person.Contact.LastName
FROM    Person.Contact
WHERE   Person.Contact.ContactID IN
(
        SELECT ContactID
        FROM   @tblTemporary
)

Pierwszy etap to zadeklarowanie zmiennej typu tabelarycznego. Deklaracja odbywa się w sposób następujący: Najpierw słowo kluczowe DECLARE, następnie nazwa zmiennej (nazwy zmiennych w SQL Server zawsze poprzedzamy @), potem typ zmiennej. Typ zmiennej może być oczywiście różny w zależności od potrzeby. W naszym przypadku, ponieważ użyliśmy zmiennej tabelarycznej, musimy w zwykłych okrągłych nawiasach umieści nazwę i typ kolumny. Jeśli chcemy, aby nasza tabela zawierała więcej niż jedną kolumnę należy oddzielić kolejne wpisy przecinkiem. Potem używamy już zmiennej jak normalnej tabeli.

Podsumowanie

W tym rozdziale dokonaliśmy przeglądu przez cały szereg sposobów wybierania wartości z poszczególnych tabel. Złączenia, podzapytania, tabelki tymczasowe oraz zmienne tabelaryczne wszystkie służą do tego samego celu: pobrania odpowiednich danych w formacie jaki jest dla użytkownika najodpowiedniejszy.

Z doświadczenia wiem, że dla większości osób które rozpoczynają zabawę z SQL najłatwiejsze i najbardziej intuicyjne będzie wykorzystanie tabel tymczasowych do pobierania danych, jednakże z czasem i z nabranym doświadczeniem większość osób przejdzie na używanie złączeń połączonych z zapytaniami zagnieżdżonymi. Gorąco polecam jak najszybsze przestawienie się i korzystanie z tej właśnie opcji. Ograniczy to ból związany z przestawieniem się na pracę z komercyjnymi projektami.