Używaj podzapytań niezależnych lub skorelowanych

Ukończone

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 w ujęciu koncepcyjnym jest wykonywane raz dla każdego wiersza.

Praca ze skorelowanymi 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 skalarnymi lub wielowartościowymi. 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 niezależnych podzapytań istnieją pewne szczególne kwestie, które należy uwzględnić podczas korzystania ze skorelowanych podzapytań:

  • 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. Podzapytanie odwołuje się do zapytania zewnętrznego i używa jego 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órej będzie odwoływać się skorelowane podzapytanie. 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 zapytaniem wewnętrznym a zewnętrznym występuje, gdy zapytanie wewnętrzne odwołuje się do wartości z zapytania zewnętrznego w celu porównania. To właśnie ta korelacja nadaje podzapytaniu jego nazwę.

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 SalesLT.Customer AS c 
WHERE
(SELECT COUNT(*) 
  FROM SalesLT.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 SalesLT.Customer AS c 
WHERE EXISTS
(SELECT * 
  FROM SalesLT.SalesOrderHeader AS o
  WHERE o.CustomerID = c.CustomerID);

W pierwszym przykładzie podzapytanie musi zliczać każde wystąpienie każdego custidu znalezionego w tabeli SalesLT.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 elementu custid, gdy tylko w tabeli SalesLT.SalesOrderHeader zostanie znalezione odpowiednie zamówienie. 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 używające COUNT(*) na podzapytanie używające EXISTS, upewnij się, że podzapytanie zawiera klauzulę SELECT *, a nie SELECT COUNT(*). Funkcja SELECT COUNT(*) zawsze zwraca wiersz, więc funkcja EXISTS zawsze zwróci wartość TRUE.

Innym przydatnym zastosowaniem operatora EXISTS jest zanegowanie podzapytania za pomocą operatora NOT, jak w poniższym przykładzie, co spowoduje zwrócenie wszystkich klientów, którzy nigdy nie złożyli 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 SalesLT.SalesOrderHeader zostanie znaleziony element custid, wyrażenie NOT EXISTS daje wartość FALSE, a obliczenie szybko się kończy.

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

  • Słowo kluczowe EXISTS znajduje się bezpośrednio po WHERE. Nie poprzedza go nazwa kolumny (ani żadne inne wyrażenie), chyba że jest również używany operator NOT.
  • W podzapytaniu użyj polecenia SELECT *. Podzapytanie nie zwraca żadnych wierszy, więc nie trzeba podawać żadnych kolumn.