Używanie samodzielnie zawartych lub skorelowanych podzapytania

  • 3 min

Wcześniej przyjrzeliśmy się samodzielnym podzapytaniom; w którym zapytanie wewnętrzne jest niezależne od zapytania zewnętrznego, wykonuje raz i zwraca wyniki do zapytania zewnętrznego. Język T-SQL obsługuje również skorelowane podzapytania, w których wewnętrzne zapytanie odwołuje się do kolumny w zapytaniu zewnętrznym i koncepcyjnie wykonuje je raz w każdym wierszu.

Praca z skorelowanych podzapytaniami

Podobnie jak własne podzapytania, skorelowane podzapytania to instrukcje SELECT zagnieżdżone w zapytaniu zewnętrznym. Skorelowane podzapytania mogą być również podzapytaniami skalarnych lub wielowartych. Są one zwykle używane, gdy zapytanie wewnętrzne musi odwoływać się do wartości w zapytaniu zewnętrznym.

Jednak w przeciwieństwie do samodzielnie zawartych podzapytania istnieją pewne specjalne zagadnienia podczas korzystania z skorelowanych podzapytania:

  • Skorelowane podzapytania nie mogą być wykonywane oddzielnie od zapytania zewnętrznego. To ograniczenie komplikuje testowanie i debugowanie.
  • W przeciwieństwie do samodzielnie zawartych podzapytania, które są przetwarzane raz, skorelowane podzapytania będą uruchamiane wiele razy. Logicznie zapytanie zewnętrzne jest uruchamiane jako pierwsze, a dla każdego zwracanego wiersza zapytanie wewnętrzne jest przetwarzane.

W poniższym przykładzie użyto skorelowanego podzapytania, aby zwrócić najnowsze zamówienie dla każdego klienta. Podzapytywanie odwołuje się do zapytania zewnętrznego i odwołuje się do jej wartości CustomerID w klauzuli WHERE. Dla każdego wiersza w zapytaniu zewnętrznym podzapytywanie znajduje maksymalny identyfikator zamówienia dla klienta, do którego odwołuje się ten wiersz, a zapytanie zewnętrzne sprawdza, czy wiersz, na który patrzy, jest wierszem o tym identyfikatorze zamówienia.

SELECT SalesOrderID, CustomerID, OrderDate
FROM SalesLT.SalesOrderHeader AS o1
WHERE SalesOrderID =
    (SELECT MAX(SalesOrderID)
     FROM SalesLT.SalesOrderHeader AS o2
     WHERE o2.CustomerID = o1.CustomerID)
ORDER BY CustomerID, OrderDate;

Pisanie skorelowanych podzapytania

Aby napisać skorelowane podzapytania, należy wziąć pod uwagę następujące wskazówki:

  • Napisz zapytanie zewnętrzne, aby zaakceptować odpowiedni wynik zwracany z zapytania wewnętrznego. Jeśli zapytanie wewnętrzne jest skalarne, można użyć operatorów równości i porównania, takich jak =, <, >i <>, w klauzuli WHERE. Jeśli zapytanie wewnętrzne może zwrócić wiele wartości, użyj predykatu IN. Zaplanuj obsługę wyników null.
  • Zidentyfikuj kolumnę z zapytania zewnętrznego, do którego odwołuje się skorelowana podzapytywanie. Zadeklaruj alias tabeli, która jest źródłem kolumny w zapytaniu zewnętrznym.
  • Zidentyfikuj kolumnę z tabeli wewnętrznej, która zostanie porównana z kolumną z tabeli zewnętrznej. Utwórz alias dla tabeli źródłowej, tak jak w przypadku zapytania zewnętrznego.
  • Napisz zapytanie wewnętrzne, aby pobrać wartości ze źródła na podstawie wartości wejściowej z zapytania zewnętrznego. Na przykład użyj kolumny zewnętrznej w klauzuli WHERE zapytania wewnętrznego.

Korelacja między zapytaniami wewnętrznymi i zewnętrznymi występuje, gdy wartość zewnętrzna jest odwołuje się do zapytania wewnętrznego w celu porównania. Jest to ta korelacja, która daje podzapytywanie jego nazwy.

Praca z programem EXISTS

Oprócz pobierania wartości z podzapytania język T-SQL udostępnia mechanizm sprawdzania, czy jakiekolwiek wyniki zostaną zwrócone z zapytania. Predykat EXISTS określa, czy istnieją jakiekolwiek wiersze spełniające określony warunek, ale zamiast zwracać je, zwraca wartość TRUE lub FALSE. Ta technika jest przydatna do sprawdzania poprawności danych bez ponoszenia kosztów pobierania i przetwarzania wyników.

Gdy podzapytywanie jest powiązane z zapytaniem zewnętrznym przy użyciu predykatu EXISTS, program SQL Server obsługuje wyniki podzapytania w specjalny sposób. Zamiast pobierać wartość skalarną lub listę wielowartośćową z podzapytania, funkcja EXISTS po prostu sprawdza, czy w wyniku znajdują się jakieś wiersze.

Koncepcyjnie predykat EXISTS jest odpowiednikiem pobierania wyników, zliczania zwracanych wierszy i porównywania liczby do zera. Porównaj następujące zapytania, które będą zwracać szczegółowe informacje o klientach, którzy składali zamówienia:

Pierwsze przykładowe zapytanie używa funkcji COUNT w podzapytaniu:

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID) > 0;

Drugie zapytanie, które zwraca te same wyniki, używa metody EXISTS:

SELECT CustomerID, CompanyName, EmailAddress 
FROM Sales.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM Sales.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

W pierwszym przykładzie podzapytywanie musi zliczać każde wystąpienie każdego custidu znalezionego w tabeli Sales.SalesOrderHeader i porównać wyniki zliczeniowe do zera, aby po prostu wskazać, że klient złożył zamówienia.

W drugim zapytaniu funkcja EXISTS zwraca wartość TRUE dla wartości custid zaraz po znalezieniu odpowiedniego zamówienia w tabeli Sales.SalesOrderHeader . Kompletna księgowość każdego wystąpienia jest niepotrzebna. Należy również pamiętać, że w przypadku formularza EXISTS podzapytywanie nie jest ograniczone do zwracania pojedynczej kolumny. W tym miejscu mamy pozycję SELECT *. Zwrócone kolumny są nieistotne, ponieważ sprawdzamy tylko, czy w ogóle są zwracane jakiekolwiek wiersze, a nie jakie wartości znajdują się w tych wierszach.

Z perspektywy przetwarzania logicznego dwa formularze zapytania są równoważne. Z punktu widzenia wydajności aparat bazy danych może traktować zapytania inaczej, ponieważ optymalizuje je pod kątem wykonywania. Rozważ przetestowanie każdego z nich dla własnego użycia.

Uwaga

Jeśli konwertujesz podzapytanie przy użyciu funkcji COUNT(*) na jedną, używając parametru EXISTS, upewnij się, że podzapytanie używa wartości SELECT * i nie SELECT COUNT(*). Funkcja SELECT COUNT(*) zawsze zwraca wiersz, więc funkcja EXISTS zawsze zwróci wartość TRUE.

Innym przydatnym zastosowaniem funkcji EXISTS jest negowanie podzapytania z not, jak w poniższym przykładzie, który zwróci każdego klienta, który nigdy nie złożył zamówienia:

SELECT CustomerID, CompanyName, EmailAddress 
FROM SalesLT.Customer AS c 
WHERE NOT EXISTS
  (SELECT * 
   FROM SalesLT.SalesOrderHeader AS o
   WHERE o.CustomerID = c.CustomerID);

Program SQL Server nie będzie musiał zwracać danych dotyczących powiązanych zamówień dla klientów, którzy składali zamówienia. Jeśli w tabeli Sales.SalesOrderHeader zostanie znaleziony identyfikator custid, funkcja NOT EXISTS zwróci wartość FALSE i ocena zostanie szybko ukończona.

Aby napisać zapytania korzystające z funkcji EXISTS z podzapytaniami, należy wziąć pod uwagę następujące wskazówki:

  • Słowo kluczowe ISTNIEJE bezpośrednio po where. Żadna nazwa kolumny (lub inne wyrażenie) nie poprzedza jej, chyba że nie jest również używana.
  • W podzapytaniu użyj polecenia SELECT *. Żadne wiersze nie są zwracane przez podzapytywanie, więc nie trzeba określać żadnych kolumn.