Tworzenie warunków filtracji
Drugim etapem przetwarzania zapytań, po określeniu zbiorów źródłowych i relacji między nimi w klauzuli FROM, jest możliwość filtracji (wyszukiwania) konkretnych elementów.
(5) SELECT
(1) FROM
(2) WHERE
(3) GROUP BY
(4) HAVING
(6) ORDER BY
Wymagania określane w WHERE, są również drugim miejscem w którym możemy dokonywać selekcji wierszy. Dla przypomnienia, pierwszym jest klauzula FROM wraz z określonymi warunkami łączenia tabel.
Prześledźmy możliwości WHERE, zaczynając od najprostszych warunków wyszukiwania rekordów.
Operatory porównania
Stosowanie WHERE jest intuicyjne. Najczęściej używana filtracja, czy też wyszukiwanie określonych rekordów, bazuje na podstawowych, znakowych operatorach porównania (=,>,<, <>, >=,<=). Czasem stosuje się ich aliasy np. symbol „różny od” != oraz <> są synonimami, podobnie jak !> i <= (nie większy niż i mniejszy lub równy). Rzadziej stosowanymi są operatory negacji ~, czy bitowe & AND, | OR oraz ^ XOR. Warunek selekcji określamy za pomocą wyrażenia, wiążącego zazwyczaj wartości kolumny z wartością porównywaną.
select Name, Color, ListPrice
from Production.Product
where Color = 'White'
Filtrację tworzy się w prosty sposób, określając jeden lub wiele warunków dla danych atrybutów jakie mają zostać spełnione.
Każdy z nich, musi stanowić logicznie spójną, niezależną strukturę porównywania wartości w określony sposób. Jeśli chcesz określić ich kilka, trzeba połączyć je za pomocą operatorów logicznych AND – jeśli wszystkie, lub OR, jeśli przynajmniej jeden z nich ma być spełniony.
select Name, Color, ListPrice
from Production.Product
-- niepoprawne byłoby określenie np. Color = 'WHITE' OR 'Grey'
-- zawsze muszą to być niezależne w pełni poprawne warunki, stąd :
WHERE Color = 'White' OR Color = 'Grey'
Tworzenie ciągu logicznych warunków, może wymagać stosowania nawiasów. Szczególnie, jeśli chcesz aby konkretne wymagania były spełnione opcjonalnie (OR) czy obligatoryjnie (AND).
Napiszmy więc zapytanie, które zwróci nam wszystkie produkty kolorów białego lub szarego i ceną, każdego z tych produktów większą niż 9. Na początek bez nawiasów :
select Name, Color, ListPrice
from Production.Product
WHERE Color = 'White' OR Color = 'Grey' and ListPrice > 9
Powyższe zapytanie, zwróci wszystko co jest białe. Dodatkowo, operator OR umożliwi także wybranie wszystkich elementów szarych, ale z ceną większą niż 9. Ponieważ, nie określiliśmy jawnie w jaki sposób mają być traktowane poszczególne warunki, wszystkie wymagania które połączone są operatorem AND zostaną potraktowane razem (obligatoryjnie). Pozostałe, zostaną rozpatrzone osobno.
Jest to powód, przez który do zbioru wynikowego dostały się elementy, których cena jest mniejsza niż 9. Silnik bazodanowy przetwarzający zapytanie, potraktował warunki AND łącznie, czyli poprzedni zapis bez nawiasów będzie równoznaczny z poniższym :
WHERE Color = 'White' OR ( Color = 'Grey' and ListPrice > 9 )
Tłumacząc na polski – wybierz wszystkie elementy (koloru białego) lub (koloru szarego i ceną większą niż 9).
Właściwie zapisany warunek filtracji w przypadku naszego scenariusza, powinien wyglądać następująco :
select Name, Color, ListPrice
from Production.Product
WHERE ( Color = 'White' OR Color = 'Grey' ) and ListPrice > 9
Brak, lub niepoprawne stosowanie nawiasów, jest prawdopodobnie jednym z najczęstszych błędów logicznych w pisaniu zapytań, popełnianych przez początkujacych użytkowników.
Operacje porównania, muszą być spójne w zakresie kompatybilności typów danych. Logicznym jest, że nie ma za bardzo sensu porównywanie np. wartości liczbowych przechowywanych w kolumnie typu int z ciągami znakowymi o ile nie jest możliwa konwersja niejawna (automatyczna).
Warunki filtracji, mogą być budowane za pomocą wyrażeń, jak również, możemy tu stosować dowolne funkcje skalarne. Należy jednak pamiętać, że stosowanie ich, może znacząco wpłynąć na wydajność wykonywanych zapytań. Aspekty związane z optymalizacją w tym zakresie i dobrymi praktykami, opisuję w ostatnim rozdziale tego kursu.
select Name, Color, ListPrice, StandardCost , ListPrice - StandardCost as CostDiff
from Production.Product
where ListPrice > StandardCost + 1000
Filtrowanie wartości znakowych (stringów)
Oprócz typowej selekcji rekordów, których kolumny zawierają wartości tekstowe bazującej na operatorze równości (=) , możemy stosować również operatory porównania – ><, > lub <. Ciekawsze możliwości oferuje filtrowanie stringów za pomocą wzorców (maski) określanych w LIKE.
Maskę dopasowania, tworzymy za pomocą prostych konstrukcji. Prześledźmy kilka przykładów :
- symbol „%” – zastępuje dowolną liczbę znaków.
-- wszystkie rekordy, dla których wartość kolumny City zaczyna się od „par” select distinct city from Person.Address where City LIKE 'par%'
- Inny przykład, ze stosowaniem % – znajdź wszystkie rekordy, dla których wartość kolumny City zwiera ciąg znaków „par” (plus dowolna liczba znaków przed i po)
select distinct city from Person.Address where City LIKE '%par%'
- symbol „_” – zstępuje jeden dowolny znak
-- wszystkie miasta które zaczynają się od dowolnego znaku, -- zawierają na pozycji 2-4 ciąg „par” select distinct city from Person.Address where City LIKE '_par%'
- Określenie zakresu znaków na wskazanej pozycji w nawiasach kwadratowych []. Zakres może być ciągły [a-z] lub stanowić listę możliwych znaków [adg]. Może też określać, wykluczenie konkretnych znaków – stosujemy wtedy symbol ^. Wszystkie rekordy zawierające w kolumnie City ciąg znaków „ringt” lub „ringd”.
select distinct city from Person.Address where City LIKE '%ring[td]%'
- a teraz z wykluczeniem tych ciągów znaków, czyli wszystko co zawiera ring i kolejnym znakiem nie jest t lub d.
select distinct city from Person.Address where City LIKE '%ring[^td]%'
- Określanie zakresu może być wykonane dla kilku znaków. Na przykład, poniższe zapytanie zwraca wszystkie elementy zaczynające się na a lub b, i mające drugą literę z przedziału n – p.
select distinct city from Person.Address where City LIKE '[ab][n-p]%'
Porówywanie wartości tekstowych odbywa się zawsze w określony sposób – zgodny z regułami określonego alfabetu. Ponieważ istenieje wiele reguł (języków), mamy możliwość wyboru konkretnej z nich. Garść informacji na temat COLLATION (czyli właśnie sposobu porównywania znaków) znajdziesz w artykule dotyczącym sortowania wyników.
Filtrowanie zakresów za pomocą BETWEEN … AND
Przeszukiwania wartości liczbowych czy dat, wykonywane jest często w ramach zakresów. Dwa poniższe zapisy są równoważne :
SELECT SalesOrderNumber, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE TotalDue >= 870 AND TotalDue <= 1000
And OrderDate >= '2002-01-01' AND OrderDate <= '2002-06-30'
SELECT SalesOrderNumber, OrderDate, TotalDue
FROM Sales.SalesOrderHeader
WHERE TotalDue BETWEEN 870 AND 1000
and OrderDate BETWEEN '2002-01-01' AND'2002-06-30'
Operator „BETWEEN AND” określa przedział obustronnie domknięty dla którego chcemy akceptować wiersze w zbiorze wynikowym. Może odnosić się do różnych typów danych – liczb, dat czy stringów.
Filtrowanie według wartości z listy – IN, ANY (SOME), ALL
Kolejnym sposobem filtrowania wartości jest odniesienie do listy wartości za pomocą operatorów IN, ANY, ALL.
IN oraz ANY (SOME) – logiczny ciąg warunków OR
Sposób ten, jest tłumaczony na szereg operacji logicznych z zastosowaniem OR.
select FirstName, LastName
from Person.Person
where FirstName in('Joan', 'John' ,'Joanna','Johnny')
-- powyższe jest równoznaczne z
select FirstName, LastName
from Person.Person
where FirstName = 'Joan' or FirstName = 'John' or FirstName = 'Joanna'
or FirstName = 'Johnny'
Elementy listy, mogą być podane jawnie, tak jak powyżej, lub mogą być wektorem zwracanym przez podzapytanie. Musi to być wektor, czyli zbiór elementów, opisanych jednym atrybutem (kolumną).
Napiszmy więc zapytanie, które zwróci nam tylko te rekordy, które później będziemy chcieli użyć na liście IN :
select distinct FirstName
from Person.Person
where FirstName like 'Jo[ah]%' and LEN(FirstName)<7
Pełne zapytanie, korzystające z tego wektora wartości, określającego akceptowalną listę, będzie wyglądało następująco (wynik będzie dokładnie taki sam jak poprzednio) :
select FirstName, LastName
from Person.Person
-- Podzapytanie zwraca 4 elementy
-- zatem będzie to równoważne z where FirstName IN ('Joan', 'John' ,'Joanna','Johnny')
where FirstName IN
(
select distinct FirstName
from Person.Person
where FirstName like 'Jo[ah]%' and LEN(FirstName)<7
)
W tym przykładzie, użyłem dodatkowo funkcji LEN(), która zwracająca długość ciągu tekstowego. Filtruję tu wszystkie imiona krótsze niż 7 znaków, zaczynające się od ‘Joa’ lub ‘Joh’. Distinct, usuwa ze zbioru wynikowego wszystkie duplikaty.
Co istotne, operator IN tłumaczony jest tylko i wyłącznie na operator ‘=’ równości. Żeby skorzystać z innych (np. > lub <) należy użyć operatora ANY (ewentualnie jego synonimu SOME – który działa dokładnie tak samo). Przykład użycia ANY – wybieramy rekordy, które są większe lub równe od przynajmniej jednej wartości zwracanej przez podzapytanie :
SELECT Name,ListPrice
FROM Production.Product
WHERE ListPrice >= ANY
(
SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)
ALL – logiczny ciąg AND
Ostatnim z operatorów działających na listach, jest ALL, który tłumaczony jest na szereg warunków AND. Zatem porównanie w filtrze z uzyciem ALL, zakłada, że dana wartość jest np. większa bądź równa, od każdego elementu listy zwracanej przez podzapytanie :
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice >= ALL
(
SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID
)
Jeśli na liście jest więcej niż jeden element, stosowanie = ALL, jest błędem logicznym, bo żadna z wartości nie spełni takiego ciągu warunków.
Podzapytania, funkcje skalarne w WHERE
Jak już zdążyłeś pewnie zauważyć, możliwe jest stosowanie podzapytań w klauzuli WHERE.
Jest to generalna zasada elastyczności języka SQL. Jeśli zadbamy aby w określonym miejscu, pojawiły się wartości określonego (oczekiwanego) typu np. wartości skalarne to wszystko będzie zgodne z jego strukturą. Pisząc więc warunek :
WHERE kolumna = 'wartosc'
Mamy do czynienia z porównaniem wartości skalarnej, przechowywanych w określonej kolumnie z inną wartością skalarną. Zaznaczam, że nie musi to być wartość stała. Może to być zmienna (np. w skrypcie, czy definicji procedury składowanej)
WHERE kolumna = @zmienna
lub wartość zwracana przez podzapytanie czy też funkcję.
-- podzapytanie zwracające wartość skalarną
SELECT ListPrice
FROM Production.Product
WHERE Name = 'Women''s Mountain Shorts, S'
-- filtrowanie w WHERE za pomocą subquery (skalar)
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice =
(
SELECT ListPrice
FROM Production.Product
WHERE Name = 'Women''s Mountain Shorts, S'
)
Zapytanie : |
Podzapytanie : |
Jeśli dane podzapytanie, w miejscu oczekiwanej wartości skalarnej, zwróci więcej niż jeden element – otrzymasz komunikat o błędzie :
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice =
(
SELECT ListPrice
FROM Production.Product
)
Msg 512, Level 16, State 1, Line 2 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Podobnie jeśli element zwrócony przez podzapytanie, okaże się wartością opisaną przez więcej niż jeden atrybut (nie będzie już skalarem), również otrzymamy błąd:
SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice =
(
SELECT *
FROM Production.Product
WHERE Name = 'Women''s Mountain Shorts, S'
)
Msg 116, Level 16, State 1, Line 7 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Istotne jest więc zrozumienie ogólnych reguł struktur języka SQL, aby w ich ramach czuć się swobodnie w stosowaniu pełnych możliwości jakie oferuje.
Podsumowanie
Etap filtrowania rekordów, operuje na elementach tabeli wirtualnej, powstałej w pierwszym kroku przetwarzania zapytania czyli po klauzuli FROM. Jeśli łączymy wewnętrznie (INNER JOIN) np. dwie tabele, Klienci i Zamówienia, dostępne będą tutaj tylko te rekordy z tabel źródłowych, dla których wynik operacji złączenia zostanie spełniony – będzie równy TRUE. Wyszukiwanie wśród rekordów „odsianych” w kroku łączenia tabel (np. Klientów bez zamówień) w tej sytuacji nie powiedzie się. Musielibyśmy inaczej skonstruować zasady relacji (połączenie zewnętrzne OUTER JOIN), aby na etapie WHERE, mieć dostęp do wszystkich elementów zbioru Klienci.
Tworząc warunki filtracji, mamy do dyspozycji szeroki zakres możliwości oferowanych przez język SQL. Możemy odwoływać się tu do wszystkich kolumn, tabel źródłowych, wyszczególnionych we FROM. Na wartościach które zawierają, dokonywać dowolnych przekształceń i tak otrzymane dane, porównywać z innymi kolumnami lub wyrażeniami. Do przekształceń danych, posłużyć nam mogą wbudowane lub własne funkcje skalarne. Warunki filtracji, mogą być również definiowane w oparciu o podzapytania.
Hejka
mam pytanie o operator ANY w tym zapytaniu:
SELECT Name,ListPrice
FROM Production.Product
WHERE ListPrice >= ANY
(
SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)
Jesli w wyniku grupowania po ProductSubcategoryID otrzymamy powiedzmy 20 grup, dla kazdej z tych grup wyznaczona zostanie MAX(listPrice), czyli bedziemy miec 20 ListPrice i teraz gdy będziemy porownywać kazdy rekord z zapytania nadrzednego, z Max(ListPrice) i powiedzmy ze ten rekord ma wartość ListPrice równa 100, a my w MAX(ListPrice) bedziemy miec wartosci 10, 20, 50, 120 itd, to czy po porównaniu 100 z 10, dalej będzie porównywac 100 z 20 itd?
wystarczy jak jeden spełni warunek (True) dalej nie ma co sprawdzać. W przypadku ALL byłoby sprawdzenie po wszystkich wartościach. Pozdr
Ja u siebie zauważyłem problemy ze stosowaniem operatorów AND i OR. Użycie ich obu powoduje czasem śmieszne i niechciane rezultaty.
Nawiasy użyte w określeniu znaków na wskazanej pozycji to nazwiasy kwadratowe „[]”, nie klamrowe „{ }” jak to zostało napisane w tekście
Dlaczego zapytanie:
„SELECT Name, ListPrice
FROM Production.Product
WHERE ListPrice >= ANY
(
SELECT MAX (ListPrice)
FROM Production.Product
GROUP BY ProductSubcategoryID)”
daje wynik jaki przedstawiono niżej?
Jeżeli ListPrice ma być większe równe przynajmniej jednej wartości zwracanej przez podzapytanie, a podzapytanie wybiera wartość maksymalną z kolumny ListPrice czyli np 1431,50 i jest to jedna jedyna wartość, więc całość powinna zwracać tylko te rekordy gdzie ListPrice = 1431,50, bo reszta będzie mniejsza albo równa (nie będzie większej niż MAX)?
Zauważ że podzapytanie nie zwraca bezwzględnego MAXa ze wszystkich cen. Zwraca ceny maksymalne w grupach (jest tu grupowanie po SubCategory), więc nie jest to jedyna porównywalna wartość. Najlepiej odpal sobie to podzapytanie i wszystko stanie się jasne.
Twoje oczekiwania spełnia za to kolejny przykład czyli z operatorem ALL 😉 Tu będzie porównywanie do wartości MAX.
Witam serdecznie! Mam przez to rozumieć, iż całe zapytanie wraz z podzapytaniem i użyciem operatora ANY w tym wypadku powinno dać listę tych produktów, które dla każdej „Subcategorii” spełnią warunek, czyli będą z cenami maksymalnymi w danej „Subcategorii”? Pytam, gdyż właśnie w tej części kursu to zapytanie nie dawało mi spokoju. Zrobiłem więc „mały test” zmodyfikowałem to zapytanie (dodałem w pierwszej linijce ProductSubcategoryID, i na końcu posortowałem po ProductSubcategoryID:
Okazało się, iż zwracana jest dokładnie ta sama liczba wierszy co pierwotnie, ale w poszczególnych Subcategoriach występują produkty z różnymi cenami, a nie tylko te z maksymalną ceną w danej Subcategorii. Chyba, że mój tok rozumowania w przypadku przedstawionego przez Pana zapytania jest błędny. Ps. dzięki za kurs!
W Twoim przykładzie, porównujemy w WHERE tak naprawdę taki warunek :
WHERE ListPrice >= ANY ( …. i tutaj jest wektor wartości MAX(ListPrice) per grupa… )
Czyli w tym przypadku dostaniesz wszystkie produkty, których cena jest >= jakiemukolwiek maxowi z tego wektora :). Najlepiej potestować na czymć prostszym, jeśli ten opis jest zbyt zawiły – dwie kategorie, dwie sub-kat i odpal najpierw podzayptanie… pozdr !
„select Name, Color, ListPrice
from Production.Product
— niepoprawne byłoby określenie np. Color = 'WHITE’ OR 'Grey’
— zawsze muszą to być niezależne w pełni poprawne warunki, stąd :
WHERE Color = 'White’ OR Color = 'Grey'”
W pierwszym komentarzu zdaje się, że jest błąd. Powinno być:
— niepoprawne byłoby określenie np. Color = 'WHITE’ AND 'Grey’
Jest OK. w przykładzie opisuję dwa warunki z logicznym operatorem OR, czyli szukam produktów o kolorze WHITE lub GREY, a nie AND – co by znaczyło, że interesuje mnie produkt koloru WHITE i jednocześnie GREY.
Chciałem tu zwrócić uwagę, że tworząc warunki, za każdym razem trzeba je w pełni określić …. samo OR / AND 'Grey’ – nie jest poprawnym warunkiem.
Czy distinct nie jest zbędny w poniższym zapytaniu? distinct powoduje, że serwer musi usunąć duplikaty.
select FirstName, LastName
from Person.Person
— Podzapytanie zwraca 4 elementy
— zatem będzie to równoważne z where FirstName IN (’Joan’, 'John’ ,’Joanna’,’Johnny’)
where FirstName IN
(
select distinct FirstName
from Person.Person
where FirstName like 'Jo[ah]%’ and LEN(FirstName)<7
)
W tym przypadku, gdy używam podzapytania, nie ma to specjalnie znaczenia czy dodam w nim jawnie distinct czy też nie. Jeśli do operatora IN() przekazuję szereg argumentów, zwracanych przez podzapytanie – DISINCT będzie tak czy siak wykonywany (niejawnie).
Żeby się o tym przekonać, pomiń w warunku funkcję LEN() która trochę zaciemnia obraz i przeanalizuj plan wykonania. Z jawnym DISTINCT czy bez – usunięcie duplikatów będzie wykonane (DISTINCT SORT). W końcu nie ma sensu wykonywać n razy porównań do powtarzających się wartości.
Nie do końca ściśle użyłem w tym przypadku sformułowania – równoważnik. Mam nadzieję, że nie to Cię zmyliło – jeśli porównujemy dwie metody, pierwszą IN() z argumentami podanymi na talerzu oraz drugą, która przekazuje wartości poprzez podzapytanie, mogą być one równoważne pod względem wyniku. Pod względem wydajności to dwie różne bajki 🙂