To jest drugi z serii artykułów dotyczących podzapytań. W tym artykule omówimy podzapytania na liście kolumn w instrukcji SELECT. Inne artykuły omawiają ich zastosowania w innych klauzulach.
Wszystkie przykłady w tej lekcji są oparte na Microsoft SQL Server Management Studio i bazie danych AdventureWorks2012. Możesz rozpocząć korzystanie z tych bezpłatnych narzędzi, korzystając z mojego Przewodnika Pierwsze kroki Korzystanie z SQL Server.
Używanie podzapytań w instrukcji Select
Kiedy podzapytanie jest umieszczone na liście kolumn, jest używane do zwracają pojedyncze wartości. W takim przypadku podzapytanie można traktować jako pojedyncze wyrażenie wartości. Zwrócony wynik nie różni się od wyrażenia „2 + 2”. Oczywiście podzapytania również mogą zwracać tekst, ale o co chodzi!
Podczas pracy z podzapytaniami instrukcja główna jest czasami nazywana zapytaniem zewnętrznym. Podzapytania są ujęte w nawiasach, dzięki czemu łatwiej je dostrzec .
Zachowaj ostrożność podczas korzystania z podzapytań. Mogą być przyjemne w użyciu, ale gdy dodasz więcej do zapytania, mogą zacząć spowalniać twoje zapytanie.
Proste podzapytanie do obliczenia średniej
Zacznijmy od prostego zapytania, aby wyświetlić SalesOrderDetail i porównajmy je z ogólną średnią SalesOrderDetail LineTotal. Instrukcja SELECT, której użyjemy, to:
SELECT SalesOrderID,LineTotal,(SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotalFROM Sales.SalesOrderDetail;
To zapytanie zwraca wyniki jako:
Podzapytanie, które jest pokazane na czerwono powyżej, jest uruchamiane jako pierwsze aby uzyskać średnią LineTotal.
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetail
Ten wynik jest następnie umieszczany z powrotem na liście kolumn, a zapytanie jest kontynuowane. Jest kilka rzeczy, na które chcę zwrócić uwagę :
- Podzapytania są zawarte w nawiasach .
- Gdy podzapytania są używane w instrukcji SELECT, mogą zwrócić tylko jedną wartość. To powinno mieć sens, po prostu wybranie kolumny zwraca jedną wartość dla wiersza i musimy postępować zgodnie z tym samym wzorcem.
- Ogólnie podzapytanie jest uruchamiane tylko raz dla całego zapytania, a jego wynik jest ponownie używany . Dzieje się tak, ponieważ wynik zapytania nie różni się dla każdego zwróconego wiersza.
- Ważne jest, aby używać aliasów nazw kolumn, aby poprawić czytelność.
Proste podzapytanie w wyrażeniu
Jak można się spodziewać, wynik podzapytania może być użyty w innych wyrażeniach. Opierając się na poprzednim przykładzie, użyjmy podzapytania, aby określić, jak bardzo nasza LineTotal różni się od średniej.
Wariancja to po prostu LineTotal minus średnia Line total. W poniższym podzapytaniu pokolorowałem je na niebiesko. Oto wzór na wariancję:
LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail)
Instrukcja SELECT ujęta w nawiasach to podzapytanie. Podobnie jak we wcześniejszym przykładzie, to zapytanie zostanie uruchomione raz i zwróci wartość liczbową, która jest następnie odejmowana od każdej wartości LineTotal.
Oto zapytanie w ostatecznej formie:
SELECT SalesOrderID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS AverageLineTotal, LineTotal - (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail) AS VarianceFROM Sales.SalesOrderDetail
Oto wynik:
Podczas pracy z podzapytaniami w instrukcjach select zwykle buduję i najpierw przetestuj podzapytanie. Instrukcje SELECT mogą się bardzo szybko skomplikować. Najlepiej je budować stopniowo. Budowanie i testowanie różnych elementów osobno naprawdę pomaga w debugowaniu.
Zapytania skorelowane
Istnieją sposoby na włączenie wartości zapytania zewnętrznego do klauzul podzapytania. Te typy zapytań nazywane są podzapytaniami skorelowanymi, ponieważ wyniki z podzapytania są w jakiejś formie połączone z wartościami w zapytaniu zewnętrznym. Zapytania skorelowane są czasami nazywane zapytaniami zsynchronizowanymi.
Jeśli nie wiesz, co oznacza korelacja, zapoznaj się z tą definicją od Google:
Korelacja: „masz wzajemny związek lub połączenie, w którym jedna rzecz wpływa lub zależy od innej. ”
Typowym zastosowaniem skorelowanego podzapytania jest jedna z kolumn zapytania zewnętrznego w klauzuli WHERE zapytania wewnętrznego. Jest to w wielu przypadkach zdrowy rozsądek, który chcesz ogranicz zapytanie wewnętrzne do podzbioru danych.
Przykład skorelowanego podzapytania
Przedstawimy przykład skorelowanego podzapytania, składając raport z każdego SalesOrderDetail LineTotal, a Average LineTotal dla całej sprzedaży Zamówienie.
To żądanie różni się znacznie od naszych wcześniejszych przykładów, ponieważ średnia, którą obliczamy, jest różna dla każdego zamówienia sprzedaży.
Tutaj wchodzą w grę skorelowane podzapytania. Możemy użyć wartość z zapytania zewnętrznego i włącz ją do kryteriów filtrowania podzapytania.
Weźmy spójrz, jak obliczamy średnią sumę linii. Aby to zrobić, przygotowałem ilustrację pokazującą instrukcję SELECT z podzapytaniem.
Aby dokładniej rozwinąć diagram. Instrukcja SELECT składa się z dwóch części, zapytania zewnętrznego i podzapytania. Zapytanie zewnętrzne służy do pobierania wszystkich wierszy SalesOrderDetail.Podzapytanie służy do znajdowania i podsumowywania wierszy szczegółów zamówienia sprzedaży dla określonego identyfikatora SalesOrderID.
Gdybym zwerbalizował kroki weźmiemy, podsumowałbym je następująco:
- Uzyskaj SalesOrderID.
- Zwróć średnią LineTotal ze wszystkich elementów SalesOrderDetail, do których pasuje SalesOrderID.
- Przejdź do następnego SalesOrderID w zewnętrznym zapytaniu i powtórz kroki 1 i 2.
Zapytanie, które możesz uruchomić w bazie danych AdventureWork2012 to:
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail SOD
Oto wyniki zapytania:
Istnieje kilka rzeczy, na które warto zwrócić uwagę.
- Możesz zobaczyć, że użyłem aliasów kolumn, aby ułatwić odczytywanie wyników zapytania.
- Użyłem również aliasu tabeli, SOD, dla zapytanie zewnętrzne. Umożliwia to użycie wartości zapytania zewnętrznego w podzapytaniu. W przeciwnym razie zapytanie nie jest skorelowane!
- Użycie aliasów tabel pozwala jednoznacznie określić, które kolumny pochodzą z każdej tabeli.
Podział skorelowanego podzapytania
Spróbujmy teraz rozwiązać to za pomocą SQL.
Na początek załóżmy, że weźmiemy przykład dla SalesOrderDetailID 20. Odpowiadający mu identyfikator SalesOrderID to 43661.
Uzyskanie średniej LineTotal dla tego elementu jest łatwe
SELECT AVG(LineTotal)FROM Sales.SalesOrderDetailWHERE SalesOrderID = 43661
Zwraca wartość 2181.765240.
Teraz, gdy mamy średnią, jaką możemy podłącz go do naszego zapytania
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, 2181.765240 AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Przy użyciu podzapytań stanie się to
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetail WHERE SalesOrderID = 43661) AS AverageLineTotalFROM Sales.SalesOrderDetailWHERE SalesOrderDetailID = 20
Ostateczne zapytanie to :
SELECT SalesOrderID, SalesOrderDetailID, LineTotal, (SELECT AVG(LineTotal) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SOD.SalesOrderID) AS AverageLineTotalFROM Sales.SalesOrderDetail AS SOD
Podzapytanie skorelowane z inną tabelą
Podzapytanie skorelowane lub dowolne podzapytanie może używać innej tabeli niż zapytanie zewnętrzne. Może się to przydać, gdy pracujesz z tabelą „nadrzędną”, taką jak SalesOrderHeader, i chcesz zawrzeć w wyniku podsumowanie wierszy podrzędnych, na przykład z SalesOrderDetail.
Zwróćmy OrderDate, TotalDue i liczba wierszy szczegółów zamówienia sprzedaży. Aby to zrobić, możemy posłużyć się następującym diagramem, aby uzyskać orientację:
W tym celu uwzględnimy skorelowane podzapytanie w naszej instrukcji SELECT, aby zwrócić COUNT wierszy SalesOrderDetail. Upewnimy się, że liczymy prawidłowy element SalesOrderDetail, filtrując na podstawie SalesOrderID zapytania zewnętrznego.
Oto ostatnia instrukcja SELECT:
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
Wyniki są następujące:
Kilka rzeczy, na które warto zwrócić uwagę w tym przykładzie:
- Podzapytanie wybiera dane z innej tabeli niż zapytanie zewnętrzne.
- Użyłem tabeli i aliasy kolumn, aby ułatwić odczytywanie kodu SQL i wyników.
- Pamiętaj o podwójnym sprawdzeniu Twoja klauzula Where! Jeśli zapomnisz uwzględnić nazwę tabeli lub aliasy w klauzuli subquery WHERE, zapytanie nie zostanie skorelowane.
Skorelowane podzapytania a łączenie wewnętrzne
Jest to ważne aby zrozumieć, że te same wyniki można uzyskać, używając podzapytania lub łączenia. Chociaż obie zwracają te same wyniki, każda metoda ma zalety i wady!
Rozważmy ostatni przykład, w którym liczymy elementy zamówienia dla elementów SalesHeader.
SELECT SalesOrderID, OrderDate, TotalDue, (SELECT COUNT(SalesOrderDetailID) FROM Sales.SalesOrderDetailWHERE SalesOrderID = SO.SalesOrderID) as LineCountFROM Sales.SalesOrderHeader SO
To samo zapytanie można wykonać za pomocą INNER JOIN wraz z GROUP BY jako
SELECT SO.SalesOrderID, OrderDate, TotalDue, COUNT(SOD.SalesOrderDetailID) as LineCountFROM Sales.SalesOrderHeader SO INNER JOIN Sales.SalesOrderDetail SOD ON SOD.SalesOrderID = SO.SalesOrderIDGROUP BY SO.SalesOrderID, OrderDate, TotalDue
Który z nich jest szybszy?
Przekonasz się, że wiele osób powie, aby unikać podzapytań, ponieważ są one wolniejsze. Będą argumentować, że skorelowane podzapytanie musi „wykonać” raz dla każdego wiersza zwróconego w zapytaniu zewnętrznym, podczas gdy INNER JOIN musi wykonać tylko jedno przejście przez dane.
Ja? plan zapytań. Postępowałem zgodnie z własną radą dla obu powyższych przykładów i stwierdziłem, że plany są takie same!
Nie oznacza to, że plany uległyby zmianie, gdyby było więcej danych, ale moja uwaga polega na tym, że nie powinieneś tylko przyjmować z góry założeń. Większość optymalizatorów SQL DBMS jest naprawdę dobra w określaniu najlepszego sposobu wykonania zapytania. Przyjmą Twoją składnię, taką jak podzapytanie lub INNER JOIN, i użyją ich do utworzenia faktyczny plan wykonania.
Który z nich jest łatwiejszy do odczytania?
W zależności od tego, co czujesz, przykład INNER JOIN może być łatwiejszy do odczytania niż skorelowane zapytanie. Osobiście, w tym przykładzie podoba mi się podzapytanie skorelowane, ponieważ wydaje mi się bardziej bezpośrednie. Łatwiej jest mi zobaczyć, co jest zliczane.
W mojej opinii INNER JOIN jest mniej bezpośredni. Najpierw musisz zobaczyć, że wszystkie wiersze szczegółów sprzedaży są zwracane ręcznie, a następnie podsumowane. Tak naprawdę nie rozumiesz, dopóki nie przeczytasz całego oświadczenia.
Który z nich jest lepszy?
Daj mi znać, co myślisz. Chciałbym usłyszeć, czy wolałbyś użyć skorelowanego podzapytania lub przykładu INNER JOIN.