Złączenia i operatory zbiorowe

Dane, które chcemy uzyskać z bazy danych zazwyczaj znajdują się w więcej niż jednej tabeli bazodanowej. W takim przypadku nasze zapytanie musi się odpowiednio odwołać do więcej niż jednej tabeli. Mechanizm ten nazywa się w SQL złączeniami.

Złączenie kartezjańskie

Aby zapytanie czerpało dane z kilku tabel, należy po klauzuli FROM zamiast jednej tebeli wymienić wszystkie wymagane, rozdzielając je przecinkami.

Przykład

Wypisać adres klientów i opis zamówienia.


SELECT klt_adres, zam_opis
  FROM klienci, zamowienia;

Zapytanie takie zwróci iloczyn kartezjański wierszy z obu tabel.
Złączenia kartezjańskie stosuje się bardzo rzadko, gdyż produkują bardzo dużą ilość wierszy, które nie zawierają logicznie spójnych informacji.
Jeżeli w zapytaniu chcemy odwołać się do kolumny, która występuje w kilku tabelach, to, aby uniknąć niejednoznaczności, odwołanie to musi zawierać nazwę kolumny poprzedzoną nazwą tabeli.

Przykład

Wypisać adres klientów i opis zamówienia.


SELECT klienci.klt_adres,
       zamowienia.zam_opis
  FROM klienci, zamowienia;

Stosowanie pełnej nazwy kolumn (wraz z nazwą tabeli) w przypadku pisania zapytań do kilku tabel należy do dobrego zwyczaju, nawet gdy nie jest to konieczne ze względu na niejednoznaczność nazw. Zapytanie staje się dzięki temu czytelniejsze.

Złączenia wewnętrzne

Złączenia kartezjańskie stosuje się niezwykle rzadko. Najczęściej, kiedy zadajemy zapytanie do dwu tabel, zależy nam na uzyskaniu tylko takich kombinacji wierszy z obu tabel, które sobie w jakiś sposób odpowiadają, np. chcemy klientów i tylko zamówienia przez nich złożone.
Złączenia, które zwracają właśnie taki zbiór wierszy z obu tabel, które sobie w jakimś stopniu odpowiadają nazywamy złączeniami wewnętrznym (inner join).
Aby zrealizować złączenie wewnętrzne w Oracle SQL należy w klauzuli WHERE dodać tzw. warunek złączenia, czyli warunek, w którym określamy jakie wiersze z obu tabel odpowiadają sobie nawzajem.

Przykład

Wypisać nazwisko i nazwę klientów oraz opisy zamówień jakie złożyli.


SELECT klienci.klt_nazwisko, klienci.klt_nazwa, zamowienia.zam_opis
  FROM klienci, zamowienia
 WHERE klienci.klt_id = zamowienia.zam_klt_id;

Zamiast odwoływać się do kolumn poprzez pełne nazwy tabel można tabelom, podobnie jak kolumnom, nadać aliasy i odwoływać się do kolumn poprzez te aliasy.

Przykład

Wypisać e-maile klientów oraz opisy i daty złożonych przez nich zamówień.


SELECT K.klt_email,
       Z.zam_opis,
       Z.zam_data_zamowienia
  FROM klienci K, zamowienia Z
 WHERE K.klt_id = Z.zam_klt_id;

Jeżeli nadamy tabeli alias, to nie można się już będzie do niej odwoływać przez nazwę.

W złączeniu mogą brać udział więcej niż 2 tabele. W takim przypadku, aby uniknąć złączenia kartezjańskiego należy wyspecyfikować więcej warunków łączących tabele.

Przykład

Wypisać wszystkie zamówienia (ich opis) oraz nazwy towarów z każdego zamówienia wraz z ilością sztuk każdego towaru w zamówieniu. Wynik uporządkować według opisu zamówienia.


SELECT Z.zam_opis, P.pzm_ilosc, T.tow_nazwa
  FROM zamowienia Z, pozycje_zamowien P, towary T
 WHERE Z.zam_id = P.pzm_zam_id
   AND T.tow_id = P.pzm_tow_id
 ORDER BY Z.zam_opis;

W przypadku łączenia N tabel powinniśmy wyspecyfikować N-1 złączeń pomiędzy tabelami.

W przypadku zapytań odnoszących się do wielu tabel, oprócz warunku złączenia w klauzuli WHERE mogą pojawić się także inne warunki wpływające na selekcję wierszy.

Przykład

Wypisać nazwy towarów (bez powtórzeń) które są zawarte w zamówieniach, które zostały złożone a jeszcze nie zrealizowane.


SELECT DISTINCT T.tow_nazwa
  FROM zamowienia Z, pozycje_zamowien P, towary T
 WHERE Z.zam_id = P.pzm_zam_id
   AND T.tow_id = P.pzm_tow_id
   AND Z.zam_data_realizacji IS NULL;

We wszystkich złączeniach można również dokonywać agregacji i grupowań.

Przykład

Wypisać imiona, nazwiska klientów indywidualnych, oraz ilość zamówień jakie złożyli w każdym roku kalendarzowym.


SELECT K.klt_imie, K.klt_nazwisko, count(*)
  FROM zamowienia Z, klienci K
 WHERE K.klt_id = Z.zam_klt_id
   AND K.klt_typ = 'IND'
 GROUP BY K.klt_imie, K.klt_nazwisko;

W złączeniach można również korzystać z klauzuli HAVING do wprowadzania warunków zawierających funkcje grupowe.

Przykład

Wypisać opisy zamówień ale tylko tych, które zawierają więcej niż jedną pozycję.


SELECT Z.zam_opis
  FROM zamowienia Z, pozycje_zamowien P
 WHERE Z.zam_id = P.pzm_zam_id
 GROUP BY Z.zam_opis
HAVING count(*) > 1;

Warunek definiujący złączenie nie koniecznie musi się składać z jednego porównania. Może to być również warunek złożony z operatorów większości, zakresu, itp.

Przykład

Wypisać nazwy towarów, ich ceny oraz rabat jaki przysługiwałby przy zakupie 20 sztuk każdego towaru.


SELECT T.tow_nazwa, T.tow_cena, G.grc_rabat
  FROM towary T, grupy_cen G
 WHERE T.tow_cena*20 BETWEEN G.grc_cena_od AND G.grc_cena_do;

Złączenia zewnętrzne

W przypadku złączeń wewnętrznych, jeżeli w jednej z tabel istnieje rekord, który nie ma żadnego odpowiednika w drugiej tabeli, to rekord ten jest pomijany w końcowym wyniku.

Przykład

Wypisać nazwiska klientów indywidualnych oraz opisy zamówień przez nich złożonych.


SELECT K.klt_nazwisko,
       Z.zam_opis
  FROM klienci K, zamowienia Z
 WHERE K.klt_id = Z.zam_klt_id
   AND K.klt_typ = 'IND';

Czasami taka sytuacja nas nie zadawala. W takim wypadku możemy wyróżnić jedną z tabel i wykonać tzw. złączenie zewnętrzne (outer join), aby otrzymać z wyróżnionej tabeli wszystkie rekordy, nawet te, które nie mają swojego odpowiednika w drugiej z tabel.
Aby wykonać złączenie zewnętrzne musimy skorzystać z operatora złączenia zewnętrznego (+). Umieszczamy go w warunku złączenia po nazwie kolumny z tabeli która jest „uboższa”, czyli tam, gdzie „brakuje” rekordów.

Przykład

Wypisać nazwiska wszystkich klientów indywidualnych oraz opisy zamówień przez nich złożonych.


SELECT K.klt_nazwisko,
       Z.zam_opis
  FROM klienci K, zamowienia Z
 WHERE K.klt_id = Z.zam_klt_id(+)
   AND K.klt_typ = 'IND';

Nie jest obojętne przy której kolumnie postawimy operator złączenia zewnętrznego. W przypadku zamiany miejsc, zapytanie nabierze zupełnie innego kształtu.

Samozłączenia

Czasami zachodzi potrzeba odwołania się w jednym zapytaniu dwukrotnie do tej samej tabeli.

Przykład

Wypisać nazwy wszystkich towarów oraz nazwy towarów, których są składnikami


SELECT SK.tow_nazwa,
       T.tow_nazwa
  FROM towary SK, towary T
 WHERE SK.tow_tow_id = T.tow_id;

W przypadku samozłączeń tę samą tebelę traktuje się jako dwie niezależne tabele. W związku z tym można traktować takie złączenie jak każde inne, stosując dowolne poznane konstrukcje.

Przykład

Wypisać nazwy i ceny wszystkich towarów ale pod warunkiem, że cały towar jest tańszy od sumy cen swoich składników. Wypisać również te sumy.


SELECT T.tow_nazwa,
       T.tow_cena,
       sum(SK.tow_cena)
  FROM towary SK, towary T
 WHERE SK.tow_tow_id = T.tow_id
 GROUP BY T.tow_nazwa, T.tow_cena
HAVING T.tow_cena < sum(SK.tow_cena);

Operatory zbiorowe

Operatory zbiorowe służą do odpowiedniego łączenia kilku zbiorów wierszy wygenerowanych przez niezależne zapytania.
Operatory zbiorowe stosuje się w następującej składni:


SELECT ...
  operator_zbiorowy
SELECT ...

W Oracle SQL istnieją cztery rodzaje operatorów zbiorowych:

UNION ALL

Operator ten powoduje, że w wyniku zapytania otrzymujemy sumę wszystkich wierszy, które znajdowały się w obu zapytaniach składowych.

Przykład

Wypisać imiona i nazwiska klientów indywidualnych oraz nazwy i ceny towarów objętych podatkiem 7%.


SELECT klt_imie, klt_nazwisko
  FROM klienci
 WHERE klt_typ = 'IND'
 UNION ALL
SELECT tow_nazwa, to_char(tow_cena)
  FROM towary
 WHERE tow_podatek = 7;

UNION

Operator ten działa podobnie jak UNION ALL, ale z wyniku końcowego eliminowane są wiersze, które powtarzają się w obu zbiorach składowych.

Przykład

Wypisać zamówienia złożone w 2000 roku oraz zamówienia niezrealizowane do tej pory. Wyeliminować powtórzenia.


SELECT *
  FROM zamowienia
 WHERE to_char(zam_data_zamowienia, 'yyyy') = '2000'
UNION
SELECT *
  FROM zamowienia
 WHERE zam_data_realizacji IS NULL;

INTERSECT

Operator ten powoduje, że w ostatecznym zbiorze wynikowym znajdują się tylko wiersze, które znajdowały się jednocześnie w obu zbiorach składowych (zbiór wspólny).

Przykład

Znaleźć nazwiska klientów, którzy nazywają się tak jak nazwa któregokolwiek towaru.


SELECT tow_nazwa
  FROM towary
INTERSECT
SELECT klt_nazwisko
  FROM klienci;

MINUS

Operator ten powoduje wyselekcjonowanie wierszy, które znajdują się w zbiorze wierszy zwróconym przez pierwsze zapytanie, a nie znajdują się w zbiorze wierszy zwróconym przez drugie zapytanie.

Przykład

Znaleźć klientów, którzy nie złożyli ani jednego zrealizowanego zamówienia.


SELECT *
  FROM klienci
 MINUS
SELECT K.*
  FROM klienci K, zamowienia Z
 WHERE K.klt_id = Z.zam_klt_id
   AND Z.zam_data_realizacji IS NOT NULL;

Różnymi operatorami zbiorowymi można jednocześnie traktować więcej niż dwa zapytania.

Przykład

Wypisać klientów gospodarczych oraz tych, którzy nie złożyli ani jednego zrealizowanego zamówienia.


SELECT *
  FROM klienci
 WHERE klt_typ = 'GSP'
 UNION
 (SELECT *
    FROM klienci
   MINUS
  SELECT K.*
    FROM klienci K, zamowienia Z
   WHERE K.klt_id = Z.zam_klt_id);

W przypadku stosowania operatorów zbiorowych należy pamiętać o następujących rzeczach:

Wszystkie zapytania składowe muszą zwracać tę samą ilość kolumn
Kolumny na tych samych pozycjach muszą być tego samego typu we wszystkich zapytaniach składowych
Nazwy kolumn różnych zapytań składowych mogą być inne, natomiast nazwy kolumn zapytania końcowego pobierane są z pierwszego zapytania składowego
Poszczególne zapytania składowe nie mogą być sortowane. Sortować można tylko wynik całego zapytania złożonego