Język SQL. Rozdział 5b. Połączenia – konstrukcje zaawansowane

(1)

(c) Instytut Informatyki Politechniki Poznańskiej 1

Język SQL. Rozdział 5b.

Połączenia – konstrukcje zaawansowane

Połączenie zewnętrzne. Iloczyn kartezjański.

Operatory zbiorowe.

(2)

Połączenie zewnętrzne (1)

• Przy połączeniu wewnętrznym w zbiorze wynikowym brak rekordów z obu relacji, nie posiadających odpowiedników spełniających

warunek połączenia.

• Połączenie zewnętrzne (ang. outer join) zachowuje wszystkie rekordy wskazanej relacji, łącząc je z "wirtualnymi" rekordami z drugiej relacji. "Wirtualne" rekordy są wypełnione wartościami pustymi.

(3)

Połączenie zewnętrzne (2)

NAZWISKO ID_ZESP

MAREK

?

ID_PRAC

180

?

10

NAZWA ID_ZESP

40

ZESPOLY

50

ALGORYTMY

BADANIA OPERACYJNE

?

PRACOWNICY

NAZWISKO ID_ZESP

MAREK NOWICKI

ID_PRAC

180 300

10

NAZWA ID_ZESP

50

ZESPOLY

?

BADANIA OPERACYJNE null ?

PRACOWNICY

null null null

null null

(4)

Połączenie zewnętrzne – składnia

• Składnia jawna:

• Operator:

• LEFT – połączenie lewostronne, do wyniku połączenia wewnętrznego obu relacji zostaną dodane te rekordy z relacji z lewej strony operatora, które nie połączyły się z rekordami relacji z prawej strony operatora.

• RIGHT – połączenie prawostronne, do wyniku połączenia wewnętrznego obu relacji zostaną dodane te rekordy z relacji z prawej strony operatora, które nie połączyły się z rekordami relacji z lewej strony operatora.

• FULL – połączenie obustronne, wynik połączenia wewnętrznego obu relacji zostanie uzupełniony o rekordy obu relacji, dla których nie został spełniony warunek połączeniowy.

• Słowo OUTER jest opcjonalne.

SELECT lista_atrybutów

FROM relacja_1 [NATURAL]

{LEFT | RIGHT | FULL} OUTER JOIN relacja_2

[ON warunek połączeniowy | USING(lista_atrybutów)];

(5)

Połączenie zewnętrzne – przykłady

• Przykłady:

SELECT nazwa, nazwisko, etat

FROM pracownicy p RIGHT OUTER JOIN

ZESPOLY

z

ON z.id_zesp = p.id_zesp;

FROM

PRACOWNICY

p LEFT OUTER JOIN zespoly z ON z.id_zesp = p.id_zesp;

FROM

PRACOWNICY

p FULL OUTER JOIN

ZESPOLY

z ON z.id_zesp = p.id_zesp;

FROM pracownicy NATURAL RIGHT OUTER JOIN

ZESPOLY

;

(6)

Połączenie zewnętrzne – warunki filtrujące

• Jeśli oprócz warunku połączeniowego w zapytaniu umieszczono dodatkowy warunek filtrujący na kolumnie z relacji, w której może

"zabraknąć" rekordów, warunek powinien zostać umieszczony po operatorze AND w klauzuli FROM zapytania.

FROM

pracownicy p

RIGHT OUTER JOIN

p ZESPOLY z

ON z.id_zesp = p.id_zesp AND placa_pod > 1000;

FROM

pracownicy p

RIGHT OUTER JOIN

p ZESPOLY

z ON z.id_zesp = p.id_zesp

WHERE placa_pod > 1000;

(7)

Połączenie zewnętrzne – składnia niejawna (1)

• Operator (+) w warunku połączeniowym wskazuje relację, w której

"zabraknie" rekordów do połączenia z drugą relacją (relacja w warunku po stronie bez (+) zachowa wszystkie swoje rekordy).

• Przykłady:

• odpowiednik w składni jawnej:

• Brak możliwość wykonania obustronnego połączenia zewnętrznego.

SELECT z.nazwa, p.nazwisko FROM

ZESPOLY

z, pracownicy p WHERE p.id_zesp (+) = z.id_zesp;

FROM ZESPOLY z LEFT OUTER JOIN pracownicy p

SELECT z.nazwa, p.nazwisko FROM zespoly z,

PRACOWNICY

p WHERE p.id_zesp = z.id_zesp (+);

FROM zespoly z RIGHT OUTER JOIN PRACOWNICY p

(8)

Połączenie zewnętrzne – składnia niejawna (2)

• Ograniczenia:

• Jeśli warunek połączeniowy jest złożony, operator (+) musi zostać zastosowany do wszystkich części składowych warunku.

• Warunek związany z połączeniem zewnętrznym nie może używać operatora IN ani nie może być łączony z innymi warunkami za

pomocą operatora OR.

SELECT z.id_zesp, z.nazwa, p.nazwisko FROM

ZESPOLY

z, pracownicy p

WHERE p.id_zesp (+) = z.id_zesp AND p.placa_pod (+) > 1000;

(9)

9

Połączenia wielu relacji

• Połączenie jest operacją binarną, jeśli w zapytaniu więcej niż dwie relacje, wówczas połączenie jest realizowane parami, np.: relacja A z relacją B, wynik połączenia z relacją C, itd.

• Zasada: użycie N relacji w zapytaniu wymaga definicji N-1 połączeń.

(c) Instytut Informatyki Politechniki Poznańskiej

SELECT p.nazwisko, z.nazwa, e.nazwa, e.placa_min, e.placa_max FROM pracownicy p INNER JOIN zespoly z

ON p.id_zesp = z.id_zesp INNER JOIN etaty e

ON p.placa_pod BETWEEN placa_min AND placa_max;

SELECT p.nazwisko, z.nazwa, e.nazwa, e.placa_min, e.placa_max FROM pracownicy p, zespoly z, etaty e

WHERE p.id_zesp = z.id_zesp

AND p.placa_pod BETWEEN placa_min AND placa_max;

(10)

Iloczyn kartezjański

• Zbiór wszystkich możliwych kombinacji krotek z dwóch relacji.

• Składnia:

• jawna – operator CROSS JOIN,

• niejawna – brak osobnej konstrukcji. Uwaga! Często uzyskiwany jako wynik błędu w definicji połączenia – w sytuacji braku warunku

połączeniowego.

• Przykłady:

• składnia jawna:

• składnia niejawna:

SELECT nazwisko, etat, nazwa FROM pracownicy, etaty;

SELECT nazwisko, etat, nazwa

FROM pracownicy CROSS JOIN etaty;

(11)

Operatory zbiorowe

(12)

12

Operatory zbiorowe

• Zbiór wynikowy powstaje z wyników dwóch lub więcej zapytań.

• Składnia (dla dwóch zapytań):

• Zasady:

• Struktura zbiorów w zapytaniach musi być zgodna ze względu na typy danych odpowiadających pod względem pozycji kolumn: np. jeśli pierwsza kolumna w pierwszym zapytaniu to ciąg znaków, pierwsza kolumna w drugim zapytaniu też musi być ciągiem znaków (lub być konwertowalna do ciągu znaków).

• Nazwy kolumn zbioru wynikowego powstają z nazw kolumn pierwszego zapytania.

• Klauzula ORDER BY tylko w ostatnim zapytaniu.

SELECT … FROM … WHERE … operator_zbiorowy

SELECT … FROM … WHERE … ORDER BY …;

(13)

13

Suma zbiorów

• UNION – eliminuje powtórzenia.

• UNION ALL – pozostawia powtórzenia.

SELECT etat FROM pracownicy WHERE id_zesp = 10

UNION

ORDER BY etat;

UNION ALL

ORDER BY etat;

SELECT DISTINCT etat FROM pracownicy

WHERE id_zesp IN (10, 20) ORDER BY etat;

SELECT etat

FROM pracownicy

WHERE id_zesp IN (10, 20) ORDER BY etat;

• realizacja bez operatora zbiorowego:

(14)

14

Iloczyn zbiorów

• INTERSECT – eliminuje powtórzenia.

• INTERSECT ALL – pozostawia powtórzenia.

• Uwaga! Brak wsparcia w OracleDB.

INTERSECT

ORDER BY etat;

(15)

15

Różnica zbiorów

• EXCEPT – eliminuje powtórzenia.

• EXCEPT ALL – pozostawia powtórzenia.

• Uwaga! W OracleDB implementowane jako MINUS.

SELECT etat FROM pracownicy WHERE id_zesp = 20 EXCEPT

SELECT etat FROM pracownicy WHERE id_zesp = 30 ORDER BY etat;

SELECT etat FROM pracownicy WHERE id_zesp = 20 MINUS

(16)

16

Przykłady

• Zbiór wynikowy z różnych zbiorów wyjściowych.

• Trzy zbiory wyjściowe:

• Kolejność wykonania: od góry do dołu.

• Zmiana kolejności: użycie nawiasów.

SELECT nazwisko AS nazwiska_i_nazwy FROM pracownicy UNION ALL

SELECT nazwa FROM zespoly ORDER BY nazwiska_i_nazwy;

SELECT etat FROM pracownicy WHERE id_zesp = 10 UNION

SELECT etat FROM pracownicy WHERE id_zesp = 20 INTERSECT

SELECT … FROM … UNION

(SELECT … FROM … INTERSECT SELECT … FROM …) ORDER BY etat;