Język SQL. Rozdział 5a.
Połączenia – podstawy
Połączenie wewnętrzne równościowe i nierównościowe. Połączenie naturalne.
Składnia jawna i niejawna.
Połączenie zwrotne.
Składnia jawna a składnia niejawna
• Składnia jawna:
‒ element standardu języka SQL,
‒ pozwala na jawne określenie rodzaju połączenia przez zastosowanie odpowiedniego operatora,
‒ rozdziela warunki połączeniowe od warunków filtrujących,
‒ mniejsze prawdopodobieństwo popełnienia błędu,
‒ niewspierana przez wszystkie systemy bazodanowe.
• Składnia niejawna:
‒ brak osobnych konstrukcji dla różnych rodzajów połączeń,
‒ w klauzuli WHERE zarówno warunek połączeniowy jak i warunki filtrujące,
‒ niewspierana przez wszystkie systemy bazodanowe.
Połączenie
• Operacja, której wynikiem jest kombinacja tych rekordów z dwóch relacji, dla których spełniony jest warunek połączeniowy.
• Ogólna postać:
• składnia jawna:
• składnia niejawna:
SELECT lista atrybutów
FROM relacja_1 operator relacja_2 warunek_połączeniowy WHERE warunki_filtrujące ...;
SELECT lista atrybutów FROM relacja_1, relacja_2
WHERE warunek_połączeniowy AND warunki_filtrujące ...;
Podział połączeń
• Ze względu na postać warunku połączeniowego:
– równościowe (ang. equijoin):
• naturalne (ang. natural join),
• zwykłe,
– nierównościowe (ang. nonequijoin).
• Ze względu na sposób dopasowania rekordów łączonych relacji:
– wewnętrzne (ang. inner join, simple join) – w zbiorze wynikowym tylko te rekordy z obu relacji, dla których spełniony jest warunek połączeniowy,
– zewnętrzne (ang. outer join) – w zbiorze wynikowym rekordy obu relacji, dla których spełniony jest warunek połączeniowy + pozostałe rekordy (z jednej lub obu relacji).
• Ze względu na łączone relacje:
– zwykłe,
– zwrotne (ang. self join).
Połączenie wewnętrzne równościowe
NAZWISKO ID_ZESP
MAREK
ZAKRZEWICZ
ID_PRAC
180 200
10 30
NAZWA
ADMINISTRACJA
SYSTEMY EKSPERCKIE
ID_ZESP
10
210 BIAŁY 30 30
PRACOWNICY
ZESPOLY
NAZWISKO ETAT
BLAZEWICZ SLOWINSKI
ID_PRAC
180 200
PROFESOR PROFESOR 210 KOSZLAJDA ADIUNKT
PRACOWNICY
NAZWA
PROFESOR ADIUNKT
PLACA_MIN
800 510
ETATY
• Równościowe – warunek połączeniowy wykorzystuje operator równości.
• Wewnętrzne – w zbiorze wynikowym tylko te rekordu z obu relacji, dla których spełniony jest warunek połączeniowy.
• pozostałe rekordy obu relacji są pomijane.
Połączenie wewnętrzne równościowe – skł. jawna
• Operator połączenia w składni jawnej: INNER JOIN ... ON
• słowo INNER można pominąć
• Przykład:
SELECT lista_atrybutów
FROM relacja_1 INNER JOIN relacja_2 ON warunek połączeniowy;
SELECT nazwisko, placa_pod, nazwa, placa_min FROM pracownicy INNER JOIN etaty
ON etat = nazwa;
Połączenie równościowe – aliasy relacji
• Jeżeli w łączonych relacjach występują atrybuty o takich samych nazwach, to w zapytaniu muszą być poprzedzone nazwą relacji w celu uniknięcia dwuznaczności.
• Alternatywa: zdefiniowanie w klauzuli FROM aliasów – alterna- tywnych nazw relacji.
• Uwaga! Jeśli zdefiniowano alias dla relacji, nie wolno używać oryginalnej nazwy relacji!
SELECT nazwisko, p.id_zesp, z.id_zesp, nazwa FROM pracownicy p INNER JOIN zespoly z
ON p.id_zesp = z.id_zesp;
SELECT nazwisko, zespoly.id_zesp, pracownicy.id_zesp, nazwa FROM pracownicy INNER JOIN zespoly
ON pracownicy.id_zesp = zespoly.id_zesp;
Połączenie wewnętrzne równościowe – skł. niejawna
• Składnia niejawna:
• Uwaga! Pominięcie warunku w klauzuli WHERE generuje tzw. iloczyn kartezjański: każdy rekord z pierwszej tabeli zostaje połączony z każdym rekordem drugiej tabeli. Jest to wynik błędny.
• Przykłady:
SELECT nazwisko, p.id_zesp, z.id_zesp, nazwa FROM pracownicy p, zespoly z
WHERE p.id_zesp = z.id_zesp;
SELECT nazwisko, placa_pod, nazwa FROM pracownicy, etaty
WHERE etat = nazwa;
SELECT lista_atrybutów FROM relacja_1, relacja_2
WHERE warunek połączeniowy;
• Połączenie równościowe, w którym warunek połączeniowy tworzą atrybuty o takich samych nazwach.
• Składnia jawna: operator NATURAL INNER JOIN – warunek połączeniowy generowany automatycznie: jest koniunkcją
wszystkich par atrybutów o takich samych nazwach, należących do łączonych relacji.
• Uwaga! Konstrukcja niebezpieczna! Stosować rozważnie!
• Przykład:
Połączenie wewnętrzne naturalne (1)
SELECT lista_atrybutów
FROM relacja_1 NATURAL INNER JOIN relacja_2;
SELECT nazwisko, nazwa, adres
FROM pracownicy NATURAL INNER JOIN zespoly;
• Problem – co w sytuacji braku gdy połączymy operatorem NATURAL INNER JOIN relacje nie posiadające kolumn o identycznych nazwach?
• brak warunku połączeniowego,
• wynik niepoprawny: każdy rekord z pierwszej tabeli zostaje połączony z każdym rekordem drugiej tabeli – iloczyn kartezjański.
Połączenie wewnętrzne naturalne (2)
SELECT nazwisko, nazwa, placa_min, placa_max FROM pracownicy NATURAL INNER JOIN etaty;
• Składnia jawna: operator INNER JOIN ... USING () – warunek połączeniowy generowany automatycznie ale tylko z atrybutów wymienionych w klauzuli USING.
• Przykład:
Połączenie wewnętrzne naturalne (3)
SELECT lista_atrybutów
FROM relacja_1 INNER JOIN relacja_2 USING (lista_atrybutów);
SELECT nazwisko, nazwa, adres
FROM pracownicy INNER JOIN zespoly USING (id_zesp);
• Składnia niejawna – brak osobnej konstrukcji dla połączenia naturalnego.
Połączenie wewnętrzne naturalne (4)
SELECT nazwisko, z.id_zesp, nazwa FROM pracownicy p, zespoly z
WHERE p.id_zesp = z.id_zesp;
Połączenie naturalne – zbiór wynikowy (1)
• W zbiorze wynikowym operacji połączenia naturalnego atrybut połączeniowy występuje tylko raz. W wyniku połączenia równo- ściowego występują oba atrybuty połączeniowe z obu łączonych relacji.
SELECT * FROM pracownicy NATURAL INNER JOIN zespoly;
SELECT * FROM pracownicy INNER JOIN zespoly USING (id_zesp);
ID_ZESP ID_PRAC NAZWISKO ETAT ID_SZEFA ZATRUDNIO PLACA_POD PLACA_DOD NAZWA ADRES
10 100 WEGLARZ DYREKTOR 01-JAN-68 1730 420.5 ADMINISTRACJA PIOTROWO 3A
40 110 BLAZEWICZ PROFESOR 100 01-MAY-73 1350 210 ALGORYTMY WLODKOWICA 16
30 120 SLOWINSKI PROFESOR 100 01-SEP-77 1070 SYSTEMY EKSPERCKIE STRZELECKA 14
SELECT * FROM pracownicy p INNER JOIN zespoly z ON p.id_zesp = z.id_zesp;
ID_PRAC NAZWISKO ETAT ID_SZEFA ZATRUDNIO PLACA_POD PLACA_DOD ID_ZESP ID_ZESP NAZWA ADRES
100 WEGLARZ DYREKTOR 01-JAN-68 1730 420.5 10 10 ADMINISTRACJA PIOTROWO 3A
110 BLAZEWICZ PROFESOR 100 01-MAY-73 1350 210 40 40 ALGORYTMY WLODKOWICA 16
120 SLOWINSKI PROFESOR 100 01-SEP-77 1070 30 30 SYSTEMY EKSPERCKIE STRZELECKA 14
• Niepoprawne konstrukcje:
Połączenie naturalne – zbiór wynikowy (2)
SELECT nazwa, nazwisko, zespoly.id_zesp
FROM pracownicy NATURAL INNER JOIN zespoly;
SELECT nazwa, nazwisko, p.id_zesp
FROM pracownicy p INNER JOIN zespoly z USING (id_zesp);
Połączenie wewnętrzne nierównościowe (1)
• Połączenie, w którym w warunku połączeniowym zastosowano inny niż = operator języka SQL.
• ang. non-equijoin
NAZWISKO PLACA_POD
MAREK
ZAKRZEWICZ
ID_PRAC
180 200
410.2 208
PLACA_MIN NAZWA
SEKRETARKA
PRACOWNICY ETATY
PLACA_MAX
450 STAŻYSTA
270
150 250
Połączenie wewnętrzne nierównościowe (2)
• Przykłady:
SELECT nazwisko, nazwa, placa_pod, placa_min, placa_max FROM pracownicy, etaty
WHERE placa_pod BETWEEN placa_min AND placa_max;
SELECT nazwisko, nazwa, placa_pod, placa_min, placa_max FROM pracownicy INNER JOIN etaty
ON placa_pod BETWEEN placa_min AND placa_max;
NAZWISKO ID_SZEFA
BRZEZIŃSKI MORZY
ID_PRAC
130
MATYSIAK 140
190
100 130 140
Połączenie wewnętrzne zwrotne (1)
• Połączenie (ang. self join), w którym relacja łączy się "sama ze sobą" (występuje dwukrotnie w klauzuli FROM).
• Użycie aliasów w połączeniu zwrotnym jest obowiązkowe!
NAZWISKO ID_SZEFA
BRZEZIŃSKI MORZY
ID_PRAC
130
MATYSIAK 140
190
100 130 140
NAZWISKO ID_SZEFA
BRZEZIŃSKI MORZY
ID_PRAC
130
MATYSIAK 140
190
100 130 140
PRACOWNICY
P S
• Przykłady:
• składnia jawna:
• składnia niejawna:
Połączenie wewnętrzne zwrotne (2)
SELECT p.nazwisko AS pracownik, s.nazwisko AS szef FROM pracownicy p, pracownicy s
WHERE p.id_szefa = s.id_prac;
SELECT p.nazwisko AS pracownik, s.nazwisko AS szef FROM pracownicy p INNER JOIN pracownicy s
ON p.id_szefa = s.id_prac;
Połączenie wewnętrzne – warunki filtrujące
• Umieszczane w klauzuli WHERE.
• Przykłady:
• składnia jawna:
• składnia niejawna:
SELECT nazwisko, nazwa
FROM pracownicy INNER JOIN zespoly USING(id_zesp) WHERE nazwa IN ('ALGORYTMY','ADMINISTRACJA');
SELECT nazwisko, nazwa
FROM pracownicy p, zespoly z WHERE p.id_zesp = z.id_zesp
AND nazwa IN ('ALGORYTMY','ADMINISTRACJA');
