Bazy danych, Matematyka

Informacje z kilku tabel

Na dzisiejszych zajęcia dowiemy się jak łączyć informacje z kilku różnych tabel.

Dotychczas wszystkie nasze zapytania wybierały wiersze z jednej tabeli.

Jeżeli chcemy wybrać połączone (,,sklejone'') wiersze z wielu tabel naraz wystarczy, że wymienimy je wszystkie w sekcji FROM, rozdzielając przecinkami.

SELECT ... FROM Gatunki, Zwierzaki WHERE ...;

Bez dodatkowego warunku powstanie iloczyn kartezjański, czyli wszystkie połączenia wierszy z pierwszej tabeli z wierszami w drugiej.

Możemy jednak we frazie WHERE wybrać warunkiem tylko niektóre kombinacje. Jeżeli chcemy na przykład wypisać imię zwierzaka razem z nazwą gatunku i kontynentu z którego pochodzi, dodajemy warunek WHERE złączenia tabel:

SELECT imie, gatunek, kontynent FROM Gatunki, Zwierzaki
WHERE Gatunki.nazwa = Zwierzaki.gatunek;

Nazwę kolumny można poprzedzić nazwą tabeli, z której pochodzi. Powyżej zrobiono to tylko dla czytelności. Gdyby jednak tabele miały kolumny o tej samej nazwie, byłoby to konieczne.

Możemy także użyć dla tabel aliasów -- alternatywnych nazw, na przykład żeby odwoływać się do tabel w krótszy sposób. Aliasy są niezbedne, gdy chcemy dwukrotnie użyć w zapytaniu tej samej tabeli.

SELECT A.imie, A.gatunek, B.kontynent
FROM Zwierzaki A, Gatunki B
WHERE A.gatunek = B.nazwa;

Powyższe zapytanie może zwrócić mniej wierszy niż mamy zwierzaków. Stanie się tak wtedy, gdy dla któregoś zwierzaka nie podamy jego gatunku. Warunek wyeliminuje wszystkie wierszem, w których gatunek był NULL.

Jak radzić sobie z tym problemem? Opiszemy nowoczesną składnię do łaczenia tabel.

SELECT ... FROM tabela_1 JOIN tabela_2 ON warunek WHERE ...;

Zamiast oddzielać tabele przecinkami we frazie FROM, można określić sposób łączenia tabel specjalnym symbolem. Może to być:

JOIN: Łączy dwie tabele według warunku odrzucając wszystkie wiersze dla których warunek nie jest spełniony.
LEFT JOIN: Łączy dwie tabele w podobny sposób. Jednak jeśli dla któregoś wiersza z pierwszej tabeli nie istnieje ŻADEN pasujący wiersz z drugiej tabeli, to tworzy się fikcyjny wiersz, w którym w kolumnach z drugiej tabeli znajdzie się NULL. Nazywamy to złączeniem zewnętrznym.
RIGHT JOIN: To samo tylko w drugą stronę.
FULL JOIN: Kombinacja LEFT i RIGHT (w obie strony), bardzo rzadko używane.

Po określeniu tabel i sposobu łączenia musi jeszcze być podany warunek łączący, poprzedzany symbolem ON.

SELECT imie, gatunek, kontynent
FROM Zwierzaki LEFT JOIN Gatunki ON gatunek = nazwa;

Teraz otrzymaliśmy wszystkie zwierzaki, dla tych których gatunku nie znamy gatunek i kontynent będą puste (jest tam NULL).

Zadanie 1.

Które zwierzaki lubią siano? Podaj imiona.
Podaj imiona wszystkich zwierzaków z Europy.
Wypisz imię zwierzaka, który waży najwięcej.
Wypisz pary zwierzaków tego samego gatunku.
To samo, ale odmiennej płci i lubiących siano.

======================================================================

Odpowiedzi:

1.1 SELECT imie FROM Zwierzaki JOIN Gatunki ON gatunek = nazwa
    WHERE przysmak = 'siano';

1.2 SELECT imie FROM Zwierzaki JOIN Gatunki ON gatunek = nazwa
    WHERE kontynent = 'Europa';

1.3 SELECT imie FROM Zwierzaki
    WHERE waga = (SELECT MAX(waga) FROM Zwierzaki);

1.4 SELECT z1.imie, z2.imie
    FROM Zwierzaki z1 JOIN Zwierzaki z2 ON z1.gatunek = z2.gatunek
    WHERE z1.imie < z2.imie;

1.5 SELECT z1.imie, z2.imie
    FROM Zwierzaki z1 JOIN Zwierzaki z2 ON z1.gatunek = z2.gatunek
         JOIN Gatunki ON z1.gatunek = nazwa
    WHERE z1.plec < z2.plec AND przysmak = 'siano';

Podzapytania
============

Język SQL operuje wyrażeniami, dlatego w zapytaniach mogą być zawarte
inne zapytania -- podzapytania.

Na przykład w każdym miejscu, w którym można umieścić wartość, można
zamiast tego wstawić zapytanie, które zwraca tabelę z jedną komórką.

SELECT * FROM Zwierzaki
WHERE waga > (SELECT waga FROM Zwierzaki WHERE imie = 'Kropka');

W sekcji FROM także można oprócz tabeli podać zapytanie.  Wymagany jest
jednak alias, czyli nazwanie tego zapytania.  Może to się przydać przy 
konstruowaniu bardziej skomplikowanych zapytań.

SELECT Zwierzaki.*
FROM Zwierzaki, (SELECT waga FROM Zwierzaki WHERE imie = 'Kropeczka') K
WHERE Zwierzaki.waga > K.waga;

Za pomocą symbolu IN możemy sprawdzić przynależności wartości do zbioru.
Zbiór można podać jawnie w nawiasach wyliczając elementy albo
określić podzapytaniem z jedną kolumną.

SELECT * FROM Gatunki WHERE kontynent IN ('Europa', 'Azja');

SELECT * FROM Gatunki
WHERE nazwa IN (SELECT gatunek FROM Zwierzaki WHERE waga > 100);

A tu sprawdzimy, czy są gatunki, z których nie mamy żadnego zwierzaka

SELECT * FROM Gatunki WHERE nazwa NOT IN (SELECT gatunek FROM Zwierzaki);

Słowa ALL oraz ANY poprzedzone operatorem porównania pozwalają 
sprawdzić warunek dla każdego elementu podzapytania

SELECT * FROM Zwierzaki
WHERE wiek >= ALL (SELECT wiek FROM Zwierzaki WHERE wiek IS NOT NULL);

W podzapytaniach można używać wartości z bieżącego wiersza głównego
zapytania.  Mamy wtedy zapytania skorelowane

SELECT imie, (SELECT kontynent FROM Gatunki WHERE nazwa = gatunek) AS kontynent
FROM Zwierzaki;

Zadanie 2.
==========

Wykonaj poniższe zadania bez łączenia tabel we frazie FROM.

1. Podaj imiona zwierzaków z Afryki, które lubią siano.

2. Dla każdego zwierzaka podaj, jaką potrawę lubi.

3. Z jakiego kontynentu nie mamy zwierzaków?

--------------------------------------------------------------------

Operacje na wynikach zapytania
==============================

SQL zawiera operacje teoriomnogościowe na relacjach będących wynikiem zapytań. 
Zapytania możemy dodawać (UNION), odejmować (MINUS) oraz brać część 
wspólną (INTERSECT). Powyższe operacje eliminują powtarzające się wiersze, 
jeżeli chcemy je zachować musimy dodać słówko ALL po nazwie operacji.

Tabele na których wykonujemy operacje muszą mieć oczywiście zgodne typami kolumny.

(SELECT imie, gatunek FROM Zwierzaki WHERE waga < 100) 
INTERSECT 
(SELECT imie, gatunek FROM Zwierzaki JOIN Gatunki ON gatunek = nazwa
 WHERE kontynent = 'Afryka');

======================================================================

Odpowiedzi:


2.1 SELECT imie FROM Zwierzaki
    WHERE gatunek IN (SELECT nazwa FROM Gatunki
                      WHERE kontynent = 'Afryka' AND przysmak = 'siano');

2.2 SELECT imie, (SELECT przysmak FROM Gatunki WHERE nazwa = gatunek) AS lubi
    FROM Zwierzaki;

2.3 SELECT kontynent FROM Gatunki
    WHERE kontynent NOT IN
          (SELECT kontynent FROM Gatunki Join Zwierzaki ON nazwa = gatunek);