Data: 13.07.2021

MS SQL podzapytania

Spis treści

• MS SQL podzapytania
  • MS SQL podzapytania w bazach SQL Server
  • Podzapytania niepowiązane
  • Podzapytania powiązane
  • Podzapytania w klauzulach SQL
    • Podzapytania w klauzuli SELECT
    • Podzapytania w klauzuli FROM
    • Podzapytania w klauzuli WHERE
  • Podzapytania z operatorem IN
  • Podzapytania z operatorem EXISTS
  • Podzapytania w wyrażeniach
  • Zagnieżdżanie podzapytań

Podzapytania służą do zagnieżdżania zapytań. Używane są one, gdy jedno zapytanie ma bazować na wyniku drugiego zapytania.

MS SQL podzapytania w bazach SQL Server

Podzapytanie polega na umieszczeniu instrukcji SELECT wewnątrz innej instrukcji SELECT. Serwer bazodanowy w pierwszej kolejności wykona zapytania wewnętrzne. Wynik zapytania wewnętrznego zwracany jest do zapytania zewnętrznego.

W dużym uproszczeniu podzapytanie można przedstawić w następujący sposób:

Podział podzapytań ze względu na wynik zwracany przez zapytanie wewnętrzne:

• podzapytania zwracające pojedynczą wartość skalarną np. maksymalna wartość sprzedaży

  SELECT MAX(LineTotal)
  FROM Sales.SalesOrderDetail;

• podzapytania zwracające listę wartości np. nazwiska osób zajmujących się w firmie sprzedażą

  SELECT LastName
  FROM Employees
  WHERE Title = 'Sales Representative';

• podzapytania zwracające dane tabelaryczne np. ID sprzedawców i sumę zrealizowanych przez nich zamówień

  SELECT  EmployeeID, SUM(Freight)
  FROM Orders
  GROUP BY EmployeeID;

Podział podzapytań ze względu na korelację z zapytaniem nadrzędnym:

• podzapytania niepowiązane (nieskorelowane)
• podzapytania powiązane (skorelowane)

Podzapytania niepowiązane

W podzapytaniach niepowiązanych wewnętrzne zapytanie jest wykonywane tylko raz. Zwraca jeden wynik. Zapytanie wewnętrzne jest niezależne od zewnętrznego i może być wykonane samodzielnie.

Za pomocą poniższego zapytania możemy odczytać nazwę podkategorii, do której należy produkt posiadający ID o numerze 748.

SELECT b.Name
FROM Production.ProductSubcategory b
WHERE b.ProductSubcategoryID =
                     (SELECT a.ProductSubcategoryID
                      FROM Production.Product a
                      WHERE a.ProductID  = 748);

Dane w tabeli Production.ProductSubcategory

Dane w tabeli Production.Product

Z tabeli Production.ProductSubcategory odczytujemy nazwy wszystkich podkategorii (kolumna Name). Następnie sprawdzamy warunek w zewnętrznej klauzuli WHERE.

W celu sprawdzenia warunku należy wykonać wewnętrzne zapytanie. W zapytaniu wewnętrznym możemy wybrać tylko jedną wartość (wartość skalarna).

W podanym przykładzie, po uruchomieniu zapytania wewnętrznego, otrzymamy wartość 12. Jest to ID podkategorii (ProductSubcategoryID) dla rekordu posiadającego ID produktu (ProductID) o numerze 748.

Następnie w zapytaniu zewnętrznym wybrana zostanie nazwa, dla której ID podkategorii (ProductSubcategoryID) jest równe wartości otrzymanej w podzapytaniu tj. 12.

Operatory porównania, takie jak =, >,<, <>, nie mogą być używane do sprawdzania listy wartości. Gdyby w powyższym przykładzie, w zapytaniu wewnętrznym, została zwrócona lista wartości a nie wartość skalarna, serwer bazodanowy nie byłby w stanie zinterpretować, czy numer podkategorii ma być równy wszystkim, czy dowolnemu ze zwróconych przez zapytanie wewnętrzne numerów.

Nazwy kolumn w podzapytaniach należy poprzedzać nazwami lub aliasami nazw tabel. Jeśli kolumna o podanej nazwie nie istnieje w tabeli odczytywanej przez wewnętrzne zapytanie, SQL Server sprawdza, czy kolumna o tej nazwie istnieje w tabeli odczytywanej przez zapytanie zewnętrzne. Jeżeli tak, zostanie ona użyta do sprawdzenia warunku WHERE. Gdyby zaistniała taka sytuacja w powyższym przykładzie, porównywane ze sobą byłyby dane z tej samej kolumny (ProductSubcategoryID) w tabeli Production.ProductSubcategory i zapytanie zwróciłoby nazwy wszystkich podkategorii.

W kolejnym przykładzie poniżej, w podzapytaniu odczytujemy kolumny Nazwa firmy (CompanyName), Miasto (City) i kraj (Country) z tabeli Customers i tyko te rekordy, w przypadku których miasto to Londyn (London). Wynik przekazujemy do zapytania nadrzędnego, za pomocą którego spośród rekordów zwróconych przez zapytanie wewnętrzne wybierzemy tylko te, w przypadku których nazwa firmy (CompanyName) zaczyna się na litery A, B lub C.

SELECT *
FROM
     (SELECT CompanyName, City, Country
     FROM Customers
     WHERE City = 'London') as London
WHERE CompanyName like '[A-C]%';

W wyniku otrzymamy firmy z Londynu, których nazwy zaczynają się na literę A, B lub C.

Podzapytania powiązane

Podzapytania powiązane (skorelowane) są bezpośrednio powiązane z zapytaniem nadrzędnym. Łącznikiem jest jeden lub więcej atrybutów, przekazywanych z zapytania nadrzędnego. Zapytanie wewnętrzne wykonywane jest osobno dla każdego wiersza zwróconego przez zapytanie zewnętrzne. Zwraca ono tyle wyników, ile jest rekordów w wyniku zapytania zewnętrznego.

Ze względu na powiązanie z zapytaniem nadrzędnym, zapytanie wewnętrzne nie może być wykonane samodzielnie.

 SELECT b.BusinessEntityID,
  (SELECT COUNT(a.AddressTypeID)
  FROM Person.BusinessEntityAddress as a
  WHERE a.BusinessEntityID = b.BusinessEntityID) as z
 FROM Person.Person as b
 ORDER BY z DESC;

W powyższym przykładzie w zapytaniu wewnętrznym zliczana jest liczba rekordów w tabeli Person.BusinessEntityAddress. Zapytanie wewnętrzne uruchomione jest dla każdego rekordu z tabeli Person.Person, w przypadku którego BusinessEntityID jest takie samo jak BusinessEntityID w tabeli Person.BusinessEntityAddress.

W wyniku zapytania otrzymamy:

Podzapytania w klauzulach SQL

Podzapytania możemy stosować w dowolnym bloku logicznym kwerendy. Ograniczeniem jest rodzaj zbioru zwracanego przez podzapytanie. Musi on pasować do miejsca, w którym chcemy go użyć:

• w klauzuli FROM zwracany przez podzapytanie zbiór może być dowolny (jednoelementowy, wieloelementowy, itp.)
• w klauzulach SELECT, WHERE, ORDER BY zwracany przez podzapytanie zbiór musi być jednoelementowy (wartość skalarna)

Podzapytania w klauzuli SELECT

W klauzuli SELECT podzapytania stosowane są do pobierania danych wykorzystywanych następnie przez zapytanie główne. Wynik zwracany przez podzapytanie musi być wartością skalarną.

SELECT Name, ListPrice,
      (SELECT AVG(ListPrice)
FROM Production.Product) AS Średnia_cena
FROM Production.Product;

W powyższym przykładzie za pomocą zapytania wewnętrznego wyliczamy średnią cenę produktów i przekazujemy otrzymaną wartość do wyniku zewnętrznego zapytania. W wyniku otrzymamy:

Podzapytania w klauzuli FROM

Podzapytanie umieszczone w klauzuli FROM dostarcza danych dla zewnętrznego zapytania. Musi ono zostać zamknięte w nawiasach ( ). Dodatkowo wszystkie kolumny muszą mieć nazwy, a całe podzapytanie musi mieć nadaną nazwę za pomocą aliasu (np. as tab1 ).

SELECT ID, FirstName
FROM
   (SELECT BusinessEntityID as ID, FirstName
    FROM Person.Person) as tab1
WHERE FirstName = 'Ken';

Podzapytania w klauzuli WHERE

Podzapytanie w klauzuli WHERE służy do filtrowania rekordów. Musi zwracać ono wartość skalarną. W poniższym przykładzie podzapytanie występuje zarówno w klauzuli SELECT jak i w klauzuli WHERE.

SELECT
 SalesOrderID,
 OrderDate,
 TotalDue,

 (SELECT AVG(TotalDue)
  FROM Sales.SalesOrderHeader) AS podz_1

 FROM Sales.SalesOrderHeader soh

 JOIN Sales.SalesTerritory st
 ON soh.TerritoryID = st.TerritoryID

 WHERE st.CountryRegionCode <> 'GB'
    AND  TotalDue <=

 (SELECT AVG(TotalDue) AS Srednia
    FROM Sales.SalesOrderHeader)

Podzapytania z operatorem IN

Za pomocą operatora IN możemy zweryfikować, czy określona wartość pasuje do dowolnej wartości w podzapytaniu lub na liście.

W poniższym przykładzie podzapytanie odczytuje z tabeli Sales.SalesPerson jedną kolumnę (BusinessEntityID) zawierającą ID rekordów, które w kolumnie SalesQuota mają wartość większą niż 250 tys.

Zapytanie nadrzędne odczytuje z tabeli Person.Person kolumny Imię (FirstName) i Nazwisko (LastName). Zwraca ono tylko te rekordy, w przypadku których:

• wartość identyfikatora w kolumnie BusinessEntityID w tabeli Person.Person jest taka sama jak BusinessEntityID w tabeli Sales.SalesPerson
• wartość identyfikatora z kolumny BusinessEntityID w tabeli Person.Person znajduje się na liście identyfikatorów zwróconych przez podzapytanie.

SELECT a.FirstName, a.LastName
FROM Person.Person a
   JOIN Sales.SalesPerson  b
   ON a.BusinessEntityID = b.BusinessEntityID
WHERE
    a.BusinessEntityID IN
                        (SELECT BusinessEntityID
                        FROM Sales.SalesPerson
                        WHERE SalesQuota > 250000);

Podzapytania z operatorem EXISTS

Operator EXISTS zwraca wartość logiczną TRUE (PRAWDA) lub FALSE (FAŁSZ) w zależności od tego, czy podzapytanie zwróciło odpowiednio pusty lub niepsuty zbiór wyników.

W odróżnieniu od większości predykatów w języku SQL, EXISTS wykorzystuje logikę dwuwartościową. Zwraca albo PRAWDĘ, albo FAŁSZ. Nie może zwrócić wartości nieznanej, gdyż nie ma sytuacji, w której nie wiadomo, czy podzapytanie zwróciło jakiś wiersz.

W poniższym przykładzie podzapytanie zwraca rekordy z tabeli HumanResources.Employee, w przypadku których była dokonana co najmniej jedna wypłata wynagrodzenia. Wypłaty wynagrodzeń sprawdzane są w tabeli HumanResources.EmployeePayHistory za pomocą podzapytania.

SELECT *
FROM HumanResources.Employee a
WHERE EXISTS (
       SELECT *
       FROM HumanResources.EmployeePayHistory b
       WHERE b.BusinessEntityID = a.BusinessEntityID
       AND YEAR(b.RateChangeDate) = 2007);

Podzapytania w wyrażeniach

Podzapytania mogą być częścią wyrażeń. W poniższym zapytaniu sprawdzamy, na ile cena standardowa (ListPrice) odbiega od średniej ceny produktu.

SELECT Name, ListPrice,
                    (SELECT AVG(ListPrice)
                     FROM Production.Product),
       (ListPrice/ (SELECT AVG(ListPrice)
                    FROM Production.Product))*100-100
FROM Production.Product;

Wyrażeń możemy używać też w zapytaniach wewnętrznych. W poniższym przykładzie zapytanie zwraca nazwę towaru, który jest droższy o 112,565 niż towar o ID numer 1

SELECT Z.Name
FROM Production.Product AS Z
WHERE Z.ListPrice =
               (SELECT W.ListPrice + 112.565
               FROM Production.Product AS W
               WHERE W.ProductID=1);

Zagnieżdżanie podzapytań

W języku SQL możemy zagnieżdżać podzapytania. Zagnieżdżanie polega na umieszczaniu podzapytań wewnątrz innych podzapytań. Za pomocą poniższego zapytania odczytamy dane osób, które są na drugim miejscu pod względem wykorzystanych godzin urlopu:

SELECT a.JobTitle, a.NationalIDNumber, a.VacationHours
FROM HumanResources.Employee AS a
WHERE a.VacationHours =
  (SELECT MAX(b.VacationHours)
  FROM HumanResources.Employee AS b
  WHERE b.VacationHours <
           (SELECT MAX(c.VacationHours)
           FROM HumanResources.Employee AS c));

Podzapytania przećwiczysz na naszym kursie MS SQL poziom średnio-zaawansowany. Zapraszamy do wspólnej nauki z nami.

Jarek Olechno
Programista SQL, Cloud Developer

Udostępnij wpis: