T-SQL pozwala na użycie trzech klauzul w których możemy filtrować dane bazując na predykatach. Są to klauzule ON, WHERE i HAVING. W tej lekcji omówiona zostanie klauzula WHERE.
Predykaty, logika trzy-wartościowa i argumenty wyszukiwania
Filtrowanie danych z użyciem predykatów w klauzuli WHERE nie zawsze jest tak proste jak może się wydawać. Przykładowo, należy zastanowić się jak predykat zachowa się w przypadku wartości NULL i jak zachowa się filtr oparty na takim predykacie. Ważne jest również zrozumienie jak sformułować predykat aby zmaksymalizować wydajność pisanych zapytań a co za tym idzie zrozumieć koncept argumentów wyszukiwania. Zacznijmy od prostego przykładu filtrującego pracowników tylko ze Stanów Zjednoczonych w tabeli HR.Employees bazy danych TSQL2012:
SELECT empid, firstname, lastname, country, region, city FROM HR.Employees WHERE country = N'USA';
Jeżeli w danych nie można wstawić wartości NULL to predykaty mogą mieć tylko wartość true lub false. Typ logiki w takiej sytuacji nazywa się logiką dwu-wartościową. Filtr w klauzuli WHERE zwraca tylko wiersze które spełniają predykat (czyli wartość predykatu równa się true). Oto wynik zapytania:
empid firstname lastname country region city ------ ---------- --------- -------- ------- --------- 1 Sara Davis USA WA Seattle 2 Don Funk USA WA Tacoma 3 Judy Lew USA WA Kirkland 4 Yael Peled USA WA Redmond 8 Maria Cameron USA WA Seattle
Jednakże kiedy wartość NULL jest dopuszczalna w danych to logika robi się trudniejsza. Rozważmy kolumny lokalizacji klientów w tabeli Sales.Customers: kraj, region i miasto. Dla niektórych miejsc na świecie, np. Stanów Zjednoczonych wszystkie kolumny mają zastosowanie np: Country: USA, Region: WA, City: Seattle. Lecz dla innych miejsc jak Wielka Brytania zastosowanie mają tylko dwie kolumny np. Country: UK, Region: NULL, City: London. Rozważmy zapytanie filtrujące pracowników ze stanu Waszyngton:
SELECT empid, firstname, lastname, country, region, city FROM HR.Employees WHERE region = N'WA';
Kiedy możliwa jest wartość NULL to predykat może przyjmować trzy wartości: prawda, fałsz lub nieznana wartość. Taki typ logiki jest nazywany logiką trzy-wartościową. Używając operatora równości w predykacie jak w zapytaniu otrzymujemy prawdę jeżeli oba operandy nie są NULL i są równe, otrzymujemy fałsz gdy oba operandy nie są NULL i nie są sobie równe. Trudniejszą częścią jest kiedy zaangażowane są wartości NULL. Otzrymujemy wtedy nieznaną wartość jeżeli jeden z operandów jest NULL lub oba są NULL. Jak wspomniano filtr w klauzuli WHERE zwraca wiersze których wartość predykatu jest prawdą. Oznacza to, że zapytanie zwraca pracowników których region nie jest równy NULL i jest równy 'WA':
empid firstname lastname country region city ------ ---------- --------- -------- ------- --------- 1 Sara Davis USA WA Seattle 2 Don Funk USA WA Tacoma 3 Judy Lew USA WA Kirkland 4 Yael Peled USA WA Redmond 8 Maria Cameron USA WA Seattle
Takie rozwiązanie jest intuicyjne lecz rozważmy zapytanie które ma zwrócić pracowników którzy nie są ze stanu Waszyngton. Użyjmy następujące zapytanie:
SELECT empid, firstname, lastname, country, region, city FROM HR.Employees WHERE region <> N'WA';
Wynik:
empid firstname lastname country region city ------ ---------- --------- -------- ------- ---------
Czy wynik jest sensowny? Jak się okazuje wszyscy pracownicy zapisani w tabeli którzy nie są ze stanu Waszyngton pochodzą z Wielkiej Brytanii, która nie ma regionów i w kolumnie region ma wstawione wartości nieoznaczone NULL. T-SQL traktuje NULL jako brakującą wartość. Inaczej mówiąc dla regionu z NULL predykat region <> 'WA' zwraca wartość nieznaną i wiersz jest odrzucony. Więc taki predykat zwróci tylko wiersze dla których region nie jest NULL i wartość jest różna od 'WA'. Przykładowo gdybyśmy mieli pracownika z wartością 'NY' w kolumnie region to zostałby on zwrócony. Ponieważ NULL jest wartością nieznaną to dwie wartości NULL nie są sobie równe i nie można używać dla nich operatora równości. Wynikiem wyrażenia NULL = NULL jest niewiadoma a nie TRUE. Dla sprawdzania wartości NULLT-SQL posiada specjalny operator IS NULL. Zwraca on prawdę jeżeli testowana wartość to NULL. Przeciwieństwem jest operator IS NOT NULL który zwraca prawdę jeżeli testowana wartość nie jest NULL. Rozwiązaniem poprzedniego problemu jest poniższy kod:
SELECT empid, firstname, lastname, country, region, city FROM HR.Employees WHERE region <> N'WA' OR region IS NULL;
Wynik:
empid firstname lastname country region city ------ ---------- ------------- -------- ------- ------- 5 Sven Buck UK NULL London 6 Paul Suurs UK NULL London 7 Russell King UK NULL London 9 Zoya Dolgopyatova UK NULL London
Filtry użyte w zapytaniach mają duży wpływ na wydajność zapytań. Po pierwsze filtrowanie wierszy ogranicza ruch sieciowy. Po drugie, bazując na zastosowanych w zapytaniu filtrach SQL Serwer ocenia czy użyć indeksów do wydajnego pobrania danych bez skanowania całej tabeli. Ważną rzeczą jest to aby zapamiętać, że predykaty muszą być napisane w formie zwanej argumentami wyszukiwania (ang. search argument SARG). Predykaty w formie "kolumna - operator - wartość" lub "wartość - operator - kolumna" mogą być argumentami SARG. Przykładowo predykaty jak "col1 = 10" i "col1 > 10" są argumentami SARG. Użycie manipulacji na kolumnie filtrowanej w większości przypadków powoduje, że predykat nie jest SARG. Przykładem manipulacji na filtrowanej kolumnie jest użycie funkcji na niej np "F(col1) = 10" gdzie F to jakaś funkcja. Załóżmy, że mamy procedurę składowaną która przyjmuje jako parametr datę wysyłki @dt. Procedura zwraca zamówienia które zostały zamówione w dniu wyznaczonym przez parametr. Jeżeli kolumna daty wysłania nie zezwala na użycie NULL to zapytanie wykonujące to zadanie może wyglądać tak:
SELECT orderid, orderdate, empid FROM Sales.Orders WHERE shippeddate = @dt;
Jednakże kolumna shippeddate zezwala na NULL, co oznacza zamówienia które nie zostały jeszcze wysłane. jeżeli użytkownik potrzebuje pobrać zamówienia które nie zostały jeszcze wysłane to powinien wprowadzić NULL jako wejściowy parametr a zapytanie będzie musiało sobie poradzić z takim parametrem. Porównując dwie wartości NULL otrzymujemy niewiadomą i wiersze są odrzucane. Więc aktualne zapytanie nie pobiera poprawnie niewysłanych zamówień. Pewnym rozwiązaniem może być użycie COALESCE lub ISNULL do zastąpienia NULL przez normalnie nieistniejącą datę jak poniżej:
SELECT orderid, orderdate, empid FROM Sales.Orders WHERE COALESCE(shippeddate, '19000101') = COALESCE(@dt, '19000101');
Pomimo tego, że rozwiązanie zwraca poprawny wynik nawet dla wartości parametru wejściowego równej NULL to niestety predykat filtrujący nie jest predykatem SARG. Oznacza to, że SQL Serwer nie jest w stanie użyć indeksu założonego na kolumnie shippeddate w celu polepszenia wydajności zapytania. Aby predykat był predykatem SARG należy zmodyfikować go w następujący sposób:
SELECT orderid, orderdate, empid FROM Sales.Orders WHERE shippeddate = @dt OR (shippeddate IS NULL AND @dt IS NULL);
Co ciekawe, standard SQL posiada predykat zwany IS NOT DISTINCT FROM który ma takie same znaczenie jak predykat w ostatnim zapytaniu. Niestety T-SQL go nie wspiera. Innym przykładem manipulacji na filtrowanych kolumnach jest użycie wyrażeń jak np. col1 - 1 < = @n. Predykat taki możemy przepisać tak aby był argumentem SARG poprzez użycie prostej matematyki np. col1 < = @n + 1.
Kombinowanie predykatów
Predykaty w klauzuli WHERE można kombinować używając operatorów logicznych AND i OR oraz operatora negacji NOT. Negacją prawdy jest fałsz i odwrotnie więc NOT false = true i NOT true = false. Negacja wartości niewiadomej daje w wyniku niewiadomą. Wcześniejszy przykład zwracający wszystkich pracowników ze stanu Waszyngton używał predykatu region = N'WA' w klauzuli WHERE. Załóżmy, że chcemy otrzymać pracowników z poza Waszyngtonu i użyjemy predykatu NOT region = N'WA'. Dla wierszy z innym regionem niż WA zwracana jest prawda a dla wierszy z WA zwracany jest fałsz. Jednakże dla wartości regionu równej NULL predykat i jego negacja dają w wyniku niewiadomą i wiersze są odrzucane z wyniku. Poprawnym sposobem aby wartości NULL znalazły się w wyniku jest użycie operatora IS NULL w połączeniu z wyrażeniem NOT region = N'WA'. Dla kombinacji predykatów istnieje kilka interesujących cech do zapamiętania. Pewne zasady pierwszeństwa określają kolejność logicznej oceny predykatów. Operator NOT poprzedza AND i OR a AND poprzedza OR. Rozpatrzmy poniższą kombinację predykatów:
WHERE col1 = 'w' AND col2 = 'x' OR col3 = 'y' AND col4 = 'z'
Ponieważ AND poprzedza OR to wyrażeniem równoważnym jest:
WHERE (col1 = 'w' AND col2 = 'x') OR (col3 = 'y' AND col4 = 'z')
Starając się wyrazić operatory jako pseudo funkcje to ta kombinacja jest równoważna do:
OR( AND( col1 = 'w', col2 = 'x' ), AND( col3 = 'y', col4 = 'z' ) )
Ponieważ nawiasy mają zawsze największy priorytet to możemy ich zawsze używać do kontroli kolejności logicznej oceny predykatów, np:
WHERE col1 = 'w' AND (col2 = 'x' OR col3 = 'y') AND col4 = 'z'
Używając pseudo-funkcji można to wyrażenie zapisać tak:
AND( col1 = 'w', OR( col2 = 'x', col3 = 'y' ), col4 = 'z' )
Wszystkie wyrażenia pojawiające się w tej samej fazie przetwarzania logicznego zapytania (np faza WHERE) są konceptualnie wyliczane w tym samym punkcie czasu. Rozważmy wyrażenie:
WHERE propertytype = 'INT' AND CAST(propertyval AS INT) > 10
Załóżmy, że tabela posiada różne wartości dla tej właściwości. Kolumna propertytype reprezentuje typ danych właściwości a kolumna propertyval zawiera wartość zapisaną jako ciąg znaków. Jeżeli propertytype = 'INT' to propertyval może być skonwertowane na INT; w przeciwnym wypadku niekoniecznie. Niektórzy założą, że jeżeli zasady kolejności nie wskazują inaczej to predykat będzie obliczany od lewej do prawej. Innymi słowy pierw sprawdzane będzie czy propertytype = 'INT', jeżeli wartość będzie nieprawdą to SQL Serwer nie wyliczy predykatu CAST(propertyval AS INT) > 10. Bazując na tym założeniu zapytanie to nie powinno nigdy zawieść próbując konwertować wartość która nie może być skonwertowana. Niestety prawda jest inna. Bazując na koncepcie, że wszystkie wyrażenia pojawiające się w tej samej fazie przetwarzania logicznego zapytania są konceptualnie wyliczane w tym samym punkcie czasu SQL Serwer wewnętrznie nie musi przetwarzać predykatu od lewej do prawej. Może on zdecydować w zależności od kosztów aby zacząć wyliczanie predykatu od drugiego wyrażenia. Oznacza to, że dla wartości o typie innym niż INT zapytanie może wygenerować błąd konwersji. Taki problem można rozwiązać na kilka sposobów. Najprostszym jest użycie funkcji TRY_CAST zamiast CAST. Kiedy typ danych nie będzie mógł być skonwertowany to rezultatem będzie NULL. Zapytanie powinno być napisane z predykatem jak poniżej:
WHERE propertytype = 'INT' AND TRY_CAST(propertyval AS INT) > 10
Filtrowanie danych tekstowych
W wielu aspektach filtrowanie danych tekstowych jest takie samo jak filtrowanie innych typów danych. Istnieją jednak pewne specyficzne sposoby filtrowania danych tekstowych: prawidłowe formy literałów i predykat LIKE. Literały mają typ danych. Jeżeli piszemy wyrażenie które zawiera operandy różnych typów to SQL Serwer musi niejawnie skonwertować parametry tak aby były tego samego typu. W zależności od okoliczności konwersja niejawna może mieć wpływ na wydajność. Ważnym jest aby poprawnie formułować literały różnych typów i być pewnym, że używamy odpowiednich typów. Klasycznym przypadkiem jest niepoprany typ literału dla ciągu UNICODE (NVARCHAR i NCHAR). Poprawna forma literału UNICODE jest poprzedzona prefiksem N i ograniczona przez dwa pojedyncze cudzysłowy np N'literal'. Regularny ciąg znaków jest ograniczony tylko przez dwa pojedyncze cudzysłowy np 'literal'. Bardzo częstym złym nawykiem jest używanie regularnego ciągu znaków jako literał filtrujący kolumny UNICODE jak w poniższym przypadku:
SELECT empid, firstname, lastname FROM HR.Employees WHERE lastname = 'Davis';
Ponieważ kolumna lastname ma inny typ niż literał to SQL Serwer niejawnie konwertuje jeden z operandów do typu drugiego z nich. W przykładzie SQL Serwer konwertuje typ literału do typu kolumny więc szczęśliwie może skorzystać z indeksu jeżeli taki istnieje. Zalecana metodą jest używanie poprawnej formy jak poniżej:
SELECT empid, firstname, lastname FROM HR.Employees WHERE lastname = N'Davis';
T-SQL dostarcza predykatu LIKE którego możemy używać do filtrowania łańcuchów tekstowych bazując na dopasowaniu wzorca. Forma użycia predykatu LIKE jest następująca:
LIKE
Predykat LIKE używa symboli wieloznacznych których można używać we wzorcach wyszukiwania. Lista symboli wieloznacznych:
- % (procent) - dowolny ciąg znaków, również pusty ciąg np 'D%': oznacza ciąg zaczynający się na literę D.
- _ (podkreślenie) - dowolny pojedynczy znak np '_D%' oznacza ciąg którego drugą literą jest D.
- [] - dowolny znak z listy w nawiasie np '[AC]%' oznacza ciąg zaczynający się na A lub C.
- [] - dowolny znak z zakresu w nawiasie np '[0-9]%' oznacza ciąg którego pierwszym znakiem jest cyfra.
- [^] - dowolny znak który nie znajduje się na liście lub w zakresie np '[^0-9]%' oznacza ciąg który nie zaczyna się od cyfry.
Przykładowo aby pobrać listę pracowników których nazwisko zaczyna się literą D należy użyć zapytania:
SELECT empid, firstname, lastname FROM HR.Employees WHERE lastname LIKE N'D%';
Jeżeli chcemy wyszukać znaku uznawanego jako symbol wieloznaczny, możemy użyć go po znaku który oznaczymy jako znak ucieczki poprzez użycie słowa kluczowego ESCAPE po predykacie LIKE. Przykładowo col1 LIKE '!_%' ESCAPE '!' wyszuka ciągi zaczynające się od podkreślenia. Kiedy predykat LIKE zaczyna się od znanego prefiksu (np col like 'ABC%') to SQL Serwer potencjalnie potrafi użyć indeksu na filtrowanej kolumnie. Jeżeli wzorzec zaczyna się od symbolu wieloznacznego to SQL Serwer musi przeskanować wszystkie wartości filtrowanej kolumny.
Filtrowanie dnych typu Date i Time
Istnieje kilka ważnych zagadnień podczas filtrowania danych daty i czasu, które są związane zarówno z poprawnością kodu i jego wydajnością. Do tych zagadnień należy sposób zapisu literałów, filtrowanie zakresów i argumenty wyszukiwania. Rozważmy poniższe zapytanie:
SELECT orderid, orderdate, empid, custid FROM Sales.Orders WHERE orderdate = '02/12/07';
Jeżeli pochodzisz z Ameryki to prawdopodobnie data oznacza dla ciebie 12 lutego 2007. Dla Brytyjczyka taki zapis oznacza 2 grudnia 2007. Dla Japończyka oznacza 7 grudnia 2002. Więc ważne jest aby wiedzieć jak SQL Serwer skonwertuje ciąg znaków na typ daty i czasu aby dostosować go do typu filtrowanej kolumny i jak zinterpretuje wartość. jak się okazuje jest to zależne od języka użytkownika serwera który uruchamia kod. Każdy login ma domyślny język z nim powiązany, i wiele różnych opcji sesji jest zależnych od loginu, min DATEFORMAT. Login z ustawieniem us_english będzie miał ustawioną opcję DATEFORMAT na mdy, Brytyjczyk na dmy a Japończyk na ymd. Problemem jest to, jak jako programista należy napisać kod który będzie interpretował kto uruchamia kod. Istnieją dwa główne podejścia. Pierwszym jest używanie formy językowo neutralnej, np '20070212' jest zawsze interpretowane jako ymd. Format '2007-02-12' jest niezależny językowo dla typów DATE, DATETIME2 i DATETIMEOFFSET. Niestety z powodów historycznych jest zalężny językowo dla typów DATETIME i SMALLDATETIME. Więc rekomendowane jest pisanie zapytań jak to:
SELECT orderid, orderdate, empid, custid FROM Sales.Orders WHERE orderdate = '20070212';
Filtrowana kolumna jest typu DATETIME a literał jest tylko datą. Kiedy SQL Serwer skonwertuje literał na typu kolumny to jako części czasu użyje północy. Jeżeli chcemy odfiltrować wszystkie wiersze dla konkretnej daty należy być pewnym czy maja one ustawiony czas jako północ lub użyć zakresu. Drugim rozwiązaniem jest użycie funkcji CONVERT lub PARSE które pozwalają na określenie jak SQL Serwer ma interpretować literał. Funkcja CONVERT pozwala na użycie numeru stylu konwersji a funkcja PARSE pozwala na użycie nazwy kultury. Kolejnym ważnym aspektem jest używanie poprawnych argumentów wyszukiwania. Przykładowo chcemy odfiltrować tylko zamówienia z lutego 2007. Możemy użyć funkcji YEAR i MONTH np:
SELECT orderid, orderdate, empid, custid FROM Sales.Orders WHERE YEAR(orderdate) = 2007 AND MONTH(orderdate) = 2;
Ponieważ manipulujemy tutaj na kolumnie filtrowanej to predykat nie jest uważany za poprawny argument wyszukiwania i SQL Serwer nie będzie w stanie użyć indeksu. Można zmienić to zapytanie na takie które używa zakresu jak poniżej:
SELECT orderid, orderdate, empid, custid FROM Sales.Orders WHERE orderdate >= '20070201' AND orderdate < '20070301';
Kod ten celowo używa operatorów >= i < a nie operatora BETWEEN ponieważ trudno określić co jest końcową wartością zakresu konwertowanego w BETWEEN.
Ćwiczenia
I. Filtrowanie danych.
- Otwórz nowe okno SSMS i ustaw kontekst bazy danych na bazę danych TSQL2012.
USE TSQL2012 GO
- Napisz zapytanie pobierające zamówienia które nie zostały jeszcze wysłane. Takie zamówienia mają NULL w kolumnie z datą wysyłki.
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE shippeddate IS NULL;
II. Urzycie klauzuli WHERE do filtrowania zakresu danych.
- Napisz zapytanie które pobierze wszystkie zamwienia pomiędzy 11 lutym 2008 a 12 lutym 2008. Kolumna z datą zamówienia jest typu DATETIME. Użyj operatora BETWEEN zakładając, że czas w kolumnie nie zawsze musi wskazywać północ.
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate BETWEEN '20080211' AND '20080212 23:59:59.999';
Ponieważ 999 nie jest uwzględnianą częścią w precyzji typu DATETIME, to data zostanie zaokrąglona do następnej północy czyli '2008-02-13 00:00:00.000' i pobrane zostaną również zamówienia z tego dnia. - Użyj operatorów >= i < aby rozwiązać ten problem:
SELECT orderid, orderdate, custid, empid FROM Sales.Orders WHERE orderdate >= '20080211' AND orderdate < '20080213';
Podsumowanie
- Klauzula WHERE filtruje dane z użyciem predykatów. Predykaty T-SQL używają logiki trójwartościowej. Klauzula WHERE zwraca zawsze przypadki gdzie wartość predykatu to prawda i odrzuca resztę.
- Filtrowanie danych redukuje przesyłanie sieciowe i pozwala na użycie indeksów w celu minimalizacji odczytów i zapisów. Aby używać indeksów ważne jest żeby pisać poprawne predykaty.
- Filtrując różne typy danych jak typy znakowe i typy daty i czasu należy zwracać uwagę aby kod był wydajny i poprawnie napisany.