MS SQL podzapytania
Bazy danych

MS SQL podzapytania

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

Podzapytania w języku MS SQL

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: MS SQL podzapytania

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

  • podzapytania zwracające pojedynczą wartość skalarną np. maksymalna wartość sprzedaży
    SELECT SUM(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 MS SQL podzapytania przykład 1

Dane w tabeli Production.Product MS SQL podzapytania przykład 2

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 kategorii, dla której ID kategorii (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 licz_zam
 FROM Person.Person as b
 ORDER BY licz_zam 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:

MS SQL podzapytania skorelowane

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:

MS SQL podzapytania przykład 3

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));

Jarosław Olechno

Programista SQL, Cloud Developer
Udostępnij wpis: udostępnij Facebook udostępnij Linkedin udostępnij e-mail

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

    Podobne artykuły z kategorii: Bazy danych

    Może Cię zainteresować