Lekcja 19. Podzapytania skorelowane.

Podzapytanie skorelowane

Podzapytanie skorelowane odwołuje się do jednej lub kilku kolumn z zewnętrznej instrukcji SQL. Takie zapytanie nazywamy skorelowanym, ponieważ są związane z zewnętrzną instrukcją SQL za pośrednictwem tych samych kolumn. Podzapytania skorelowane są zwykle używane, gdy chcemy uzyskać odpowiedź na pytanie dotyczące wartości w każdym wierszu znajdującym się w zewnętrznym zapytaniu. Na przykład możemy chcieć sprawdzić, czy występuje relacja między danymi, ale nie interesuje nas, ile wierszy zostało zwróconych przez podzapytanie. To znaczy, że chcemy jedynie sprawdzić, czy zostały zwrócone jakiekolwiek wiersze. Podzapytanie skorelowane jest wykonywane raz dla każdego wiersza w zapytaniu zewnętrznym, co odróżnia je od podzapytania nieskorelowanego, które jest uruchamiane jeden raz, przed uruchomieniem zapytania zewnętrznego.

Przykład podzapytania skorelowanego

Poniższe podzapytanie skorelowane pobiera pracowników, których wynagrodzenie jest wyższe niż średnie w grupie o danym kodzie:

Zewnętrzne zapytanie zostało oznaczone aliasem emp1, natomiast podzapytanie zostało oznaczone aliasem emp2. Odwołanie do kolumny emp_no w części zewnętrznej i wewnętrznej sprawia, że wewnętrzne podzapytanie jest skorelowane z zapytaniem zewnętrznym. Poza tym podzapytanie zwraca jeden wiersz zawierający średnie wynagrodzenie pracowników o danym kodzie. W podzapytaniu skorelowanym każdy wiersz z zapytania zewnętrznego jest przesyłany po kolei do podzapytania. Podzapytanie odczytuje i przetwarza każdy wiersz z zapytania zewnętrznego. Następnie zwracane są wyniki całego zapytania. W powyższym przykładzie zewnętrzne zapytanie pobiera wszystkie wiersze z tabeli empolyee i przesyła je do zapytania wewnętrznego. Każdy wiersz jest odczytywany przez zapytanie wewnętrzne. Oblicza ono średnie wynagrodzenie pracownika, dla którego job­­_code w zapytaniu wewnętrznym jest równy job_code w zapytaniu zewnętrznym.

Użycie operatorów EXISTS i NOT EXISTS z podzapytaniem skorelowanym

Operator EXISTS służy do sprawdzenia, czy podzapytanie zwróciło jakiekolwiek wiersze. Choć można korzystać z tego operatora z podzapytaniem nieskorelowanymi, zwykle używamy go jednak z podzapytaniami skorelowanymi. Operator NOT EXISTS stanowi logiczne zaprzeczenie EXISTS: sprawdza, czy podzapytanie nie zwróciło żadnych wierszy.

Użycie operatora EXISTS z podzapytaniem skorelowanym

W poniższym przykładzie użyto operatora EXISTS do pobrania nazw działów będących nadrzędnymi innych działów. Należy zauważyć, że nie interesuje nas liczba zwróconych wierszy przez zapytanie. Chcemy się jedynie dowiedzieć, czy w ogóle zostały zwrócone jakieś wiersze:

Ponieważ operator EXISTS jedynie sprawdza, czy podzapytanie zwróciło jakieś wiersze, nie musi ono zwracać kolumny – może po prostu zwracać literał. Ta właściwość może poprawić jego wydajność. Zmieniono powyższe zapytanie tak, aby podzapytanie zwracało literał 1:

Jeżeli podzapytanie zwraca choć jeden wiersz, EXISTS zwraca wartość prawda. Jeżeli podzapytanie nie zwraca żadnych wierszy, EXISTS zwraca fałsz. W powyższych przykładach nie interesowała nas liczba wierszy zwracanych przez podzapytanie. Chcieliśmy się jedynie dowiedzieć, czy w ogóle zostały zwrócone jakieś wiersze, więc użyliśmy operatora EXISTS zwracającego wartość prawda lub fałsz. Ponieważ zapytanie zewnętrzne wymaga przynajmniej jednej kolumny, w powyższym przykładzie podzapytanie zwraca literał 1.

Użycie operatora NOT EXISTS z podzapytaniem skorelowanym

W poniższym przykładzie użyto operatora NOT EXISTS do pobrania nazw działów nie będących nadrzędnymi innych działów:

Kod źródłowy

Lekcja 9. Instrukcja SELECT wykorzystująca klika tabel.

Instrukcje SELECT wykorzystujące dwie tabele

Schematy bazy danych zawierają zwykle więcej niż jedną tabelę. Na przykład w schemacie employee znajdują się tabele przechowujące informacje o pracownikach, klientach, zarobkach itd. Jak dotąd wszystkie zapytania przedstawione w tym kursie pobierały wiersze tylko z jednej tabeli. W rzeczywistości często chcemy pobrać dane z kilku tabel. Możemy na przykład chcieć uzyskać nazwę klienta, kraj oraz walutę w obowiązującą w tym kraju.

W tej lekcji nauczysz się tworzyć zapytania wykorzystujące dwie tabele. Dowiesz się także, jak wykorzystywać zapytania pracujące na jeszcze większej liczbie tabel.

Powróćmy do naszego przykładu. Załóżmy, że chcemy pobrać nazwę klienta o numerze 1007 oraz kraj i walutę obowiązującą w tym kraju. Nazwa oraz kraj klienta jest przechowywana w kolumnie customer oraz country tabeli customer, a waluta – w kolumnie currency tabeli country. Tabele są ze sobą powiązane za pośrednictwem kolumny klucza obcego country. Kolumna ta (klucz obcy) w tabeli customer wskazuje na kolumnę country (klucz główny) tabeli country.

Poniższe zapytanie pobiera z tabeli customer kolumny customer i country dla klienta o numerze 1007:

Następne zapytanie pobiera z tabeli country kolumnę currnecy dla country równego USA:

Dowidzieliśmy się, że klient numer 1007 mieszka w USA, a obowiązująca tam walutą jest dolar. Musieliśmy w tym celu wykonać dwa zapytania. Możemy jednak otrzymać takie same informacje stosując jedno zapytanie. W takiej sytuacji należy zastosować w zapytaniu złączenie tabel. Aby to zrobić, należy dołączyć obie tabele do klauzuli FROM zapytania, a także uwzględnić odpowiednie kolumny ze wszystkich tabel w klauzuli WHERE.

W naszym przykładzie klauzula FROM będzie miała postać:

Natomiast klauzula WHERE:

Złączenie jest pierwszym warunkiem klauzuli WHERE (customer.country = country.country). Najczęściej w złączeniu są stosowane kolumny będące kluczem głównym jednej tabeli i kluczem obcym drugiej tabeli. Drugi warunek w klauzuli WHERE (customer.cust_no = 1007) pobiera klienta o numerze 1007.

Jak możemy zauważyć, w klauzuli WHERE są umieszczone zarówno nazwy kolumn, jak i tabel. Jest to spowodowane tym, że kolumna ­country znajduje się i w tabeli customer, i country, musimy więc w jakiś sposób określić tabelę z kolumną, której chcemy użyć. Gdyby kolumny miały różne nazwy, moglibyśmy pominąć nazwy tabel, należy jednak zawsze je umieszczać, aby było jasne, skąd pochodzi dana kolumna.

Klauzula SELECT w naszym zapytaniu będzie miała postać:

Zapytanie zatem ma postać:

To jedno zapytanie zwraca nazwę klienta, kraj i walutę obowiązującą w tym kraju. Kolejne zapytanie pobiera wszystkich klientów i porządkuje ich według kolumny customer.customer:

Używanie aliasów tabel

Powyżej utworzyliśmy następujące zapytanie:

Możemy zauważyć, że nazwy tabel customer i country zostały użyte zarówno w klauzuli SELECT, jak i WHERE. Możliwe jest zdefiniowanie aliasów tabel w klauzuli FROM i korzystanie z nich, gdy odwołujemy się do tabeli w innych miejscach w zapytaniu.

Na przykład w poniższym zapytaniu użyto aliasu cu dla tabeli customer  i co dla tabeli country. Należy zauważyć, że aliasy są definiowane w klauzuli  FROM i umieszczane przed nazwami kolumn w innych fragmentach zapytania:

Aliasy tabel zwiększają czytelność zapytań, zwłaszcza gdy piszemy długie zapytania, wykorzystujące wiele tabel.

Iloczyny kartezjańskie

Jeżeli warunek złączenia nie zostanie zdefiniowany, złączone zostanę wszystkie wiersze z jednej tabeli ze wszystkimi wierszami drugiej. Taki zestaw wyników nazywamy iloczynem kartezjańskim.

Załóżmy, że w jednej tabeli znajduje się 30 wierszy, a w drugiej 20. Jeżeli wybierzemy kolumny z tych tabel bez warunku złączenia, otrzymamy w wyniku 600 wierszy (30 * 20), ponieważ każdy wiersz z pierwszej tabeli zostanie złączony z każdym wierszem z drugiej tabeli.

Poniższy przykład przestawia fragment iloczynu kartezjański tabel customer i country:

Zapytanie zwróciło 240 wierszy, ponieważ tabela ­customer zawiera 15 wierszy,  a tabela country zawiera 16 wierszy. Wynika to z prostych obliczeń 15 * 16 = 240.

Instrukcje SELECT wykorzystujące więcej niż dwie tabele

Złączenia mogą obejmować dowolną liczbę tabel. Liczba złączeń potrzebnych w klauzuli WHERE jest równa liczbie tabel wykorzystywanych w zapytaniu – 1.

Rozważmy bardziej skomplikowany przykład, wykorzystujące cztery tabele, który pobierze:

•          imię oraz nazwisko pracownika (z tabeli employee),
•          nazwę działu, w którym pracuje (z tabeli department),
•          stanowisko, na którym pracuje (z tabeli job) ,
•          walutę kraju, w którym pracuje (z tabeli country).

Korzystamy z czterech tabel i dlatego potrzebujemy trzech złączeń. Poniżej zostały wymienione konieczne złączenia:

•        Aby uzyskać informacje na temat działu, w którym pracuje dany pracownik, musimy złączyć tabele employee i department, wykorzystują kolumny dept_no (employee.dept_no = department.dept_no).
•         Aby dowiedzieć się na jakim stanowisku pracuje dany pracownik, musimy złączyć tabele employee i job, wykorzystując kolumny job_code (employee.job_code = job.job_code).
•          Aby uzyskać walutę kraju w którym pracuje dany pracownik, musimy złączyć tabele job i country, wykorzystując kolumny job­_country i country (job.job_country = country.country).

Te złączenia zostały zastosowane w poniższym zapytaniu, które pobiera wszystkie ww. dane dla pracownika o numerze 2 (employee.emp_no = 2) :

Prezentowane dotychczas zapytania pobierające dane z wielu tabel wykorzystywały w warunkach złączenia operator równości (=), były to więc równozłączenia. 

Kod źródłowy

Lekcja 7. Instrukcja SELECT.

Polecenie SELECT służy do pobierania informacji z tabel bazy danych. W najprostszej postaci tej instrukcji należy określić tabelę i kolumny, z których chcemy pobrać dane.

Wykonywanie instrukcji SELECT

Poniższa instrukcja SELECT pobiera kolumny cust_no, customer oraz phone_no z tabeli customer:

Tuż za słowem kluczowym SELECT należy wpisać nazwy kolumn, z których mają być pobrane dane. Za słowem kluczowym FROM należy wpisać nazwę tabeli. Instrukcję SQL trzeba zakończyć średnikiem (;).

Jeżeli chcemy pobrać wszystkie kolumny z tabeli, możemy użyć gwiazdki (*) zamiast listy kolumn. W poniższym przykładzie gwiazdka została użyta do pobrania wszystkich kolumn z tabeli department:

Za pomocą słowa kluczowego FIRST możemy ograniczyć ilość pobieranych rekordów.  W poniższym przykładzie ilość pobranych rekordów z tabeli department została ograniczona do 5:

Wykorzystanie klauzuli WHERE do wskazywania wierszy do pobrania

Klauzula WHERE ogranicza zakres pobieranych wierszy. Klauzulę WHERE umieszczamy za klauzulą FROM:

SELECT lista elementów

FROM lista tabel

WHERE lista warunków;

W poniższym zapytaniu użyto klauzuli WHERE do pobrania z tabeli customer wiersza, w którym kolumna cust_no ma wartość 1002.

Używanie aliasów kolumn

Gdy pobieramy kolumnę z tabeli, w wynikach jako nagłówek kolumny Firebird wpisuje nawę kolumny wielkimi literami. Na przykład gdy pobieramy kolumnę cust_no, nagłówkiem w wynikach jest CUST_NO. Możemy określić własny nagłówek , stosując alias. W poniższym zapytaniu kolumna cust_no otrzymuje alias NUMBER:

Jeżeli chcemy użyć spacji i zachować wielkość liter w aliasie, należy jego tekst umieścić w cudzysłowie:

Przed tekstem aliasu można również umieścić opcjonalne słowo kluczowe AS:

Łączenie wartości z kolumn za pomocą konkatenacji

Za pomocą konkatenacji można łączyć wartości z kolumn pobrane przez zapytanie, co umożliwia uzyskanie bardziej sensownych wyników. Poniższe zapytanie łączy kolumny first_name oraz last_name z tabeli employee za pomocą operatora konkatenacji (||). Nalży zauważyć, że po kolumnie first_name został dodany znak spacji, a dopiero później kolumna last_name:

Wartości kolumn first_name i last_name zostały połączone w wynikach pod aliasem Employee name.

Wartość null

Wartość specjalna – null – oznacza, że wartość dla danej kolumny jest nieznana. Nie jest ona jednak pustym wpisem.

Jeżeli pobieramy kolumnę, która zawiera wartość null, w jej wynikach zobaczymy <null> .

Można również sprawdzić występowanie wartości null, umieszczając w zapytaniu IS NULL. W poniższym przykładzie zostały zwrócone informacje o projekcie Translator upgrade, ponieważ w tym wierszu w kolumnie team_leader występuje null:

Zdarza się, że chcemy wyświetlić inną wartość w miejsce null. Aby to zrobić musimy posłużyć się funkcją COALESCE(). Funkcja COALESCE() przyjmuje dwa lub więcej parametrów i zwraca pierwszą wartość, która okażę się nie być null. W poniższym zapytaniu COALESCE() zwraca wyrażenie  ‘lack’, jeżeli kolumna team_leader zawiera wartość null:

Wyświetlanie unikatowych wierszy

Załóżmy, że chcemy uzyskać listę walut obowiązujących w krajach znajdujący się w tabeli country. Możemy to zrobić za pomocą poniższego zapytania, które pobiera kolumnę currency z tabeli country:

Jak widzimy w wynikach zwróconych przez zapytanie, niektóre waluty obowiązują w więcej niż jednym kraju i w związku z tym się powtarzają. Można usunąć powtarzające wiersze za pomocą słowa kluczowego DISTINCT. W poniższym zapytaniu użyto go, aby pominąć powtarzające się wiersze:

Na tej liście łatwiej zauważyć, że wszystkich walut jest 11; powtarzające się wiersze zostały pominięte.

Kod źródłowy