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.