Typy złączeń

Złączenie krzyżowe

{ T1 } CROSS JOIN { T2 }

Złączenie krzyżowe bierze dwie tabele T1 i T2 mające N i M osobnych wierszy i zwraca złączoną tabelę zawierającą N*M wszystkich złączonych wierszy. Dla każdego wiersza R1 z T1, każdy wiersz R2 z T2 jest łączony z R1 i daje w wyniku wiersz JR złączonej tabeli zawierający wszystkie pola z R1 i R2. Złączenie krzyżowe CROSS JOIN jest równoważne poleceniu INNER JOIN ON TRUE.

Złączenia warunkowe

{ T1 } [ NATURAL ] [ INNER | { LEFT | RIGHT | FULL } [ OUTER ] ] JOIN { T2 } { ON search condition | USING ( join column list ) }

Złączenie warunkowe wprowadza warunki złączenia przez podanie klauzuli NATURAL, ON albo USING. Klauzula ON zawiera warunki złączenia (search condition) i działa tak jak klauzula WHERE. USING zawiera listę nazw kolumn oddzielonych przecinkami, które zostaną użyte do złączenia. NATURAL jest skrótem odpowiadającym USING z nazwami wszystkich kolumn z obu tabel. Działaniem ubocznym USING i NATURAL jest zwracanie tylko jednej kopii każdej łączonej kolumny (porównaj to z definicją JOIN z relacyjnej algebry, podaną wcześniej).

[ INNER ] JOIN

Dla każdego wiersza R1 z T1, złączona tabela ma wiersz dla każdego wiersza z T2, który spełnia warunki złączenia z R1.

Uwaga: Słowa INNER i OUTER są opcjonalne dla wszystkich złączeń JOIN. INNER jest domyślne. LEFT, RIGHT oraz FULL dotyczą OUTER JOIN.

LEFT [ OUTER ] JOIN

Najpierw wykonuje się INNER JOIN. Później dla każdego wiersza z T1, który nie spełnia warunków złączenia z żadnym wierszem z T2, zwracany jest dodatkowo wiersz zawierający wartości null w kolumnach z T2.

Uwaga: Złączona tabela bezwarunkowo zawiera wiersz dla każdego wiersza z T1.

RIGHT [ OUTER ] JOIN

Najpierw wykonuje się INNER JOIN. Później dla każdego wiersza z T2, który nie spełnia warunków złączenia z żadnym wierszem z T1, zwracany jest dodatkowo wiersz zawierający wartości null w kolumnach z T1.

        Uwaga: Złączona tabela bezwarunkowo zawiera wiersz dla każdego wiersza z T2.

FULL [ OUTER ] JOIN

Najpierw wykonuje się INNER JOIN. Później dla każdego wiersza z T1, który nie spełnia warunków złączenia z żadnym wierszem z T2, zwracany jest dodatkowo wiersz z polami null w kolumnach z T2. Dodatkowo dla każdego wiersza z T2, który nie spełnia warunków złączenia z żadnym wierszem z T1, zwracany jest wiersz zawierający wartości null w kolumnach z T1.

        Uwaga: Złączona tabela bezwarunkowo zawiera wiersz dla każdego wiersza z T2 oraz dla każdego wiersza z T1.

Złączenia wszystkich typów mogą być łączone lub zagnieżdżone, w taki sposób, że jedno lub oba z T1 i T2 może być złączoną tabelą. W celu kontroli kolejności wykonania złączeń otacza się je nawiasami, w przeciwnym wypadku są wykonywane od lewej do prawej strony.

1.4.1.3. Funkcje (operatory) agregujące

SQL wprowadza funkcje agregujące (tj. AVG, COUNT, SUM, MIN, MAX). Argumentem funkcji jest wyrażenie wykonywane dla każdego wiersza, który spełnia warunki klauzuli WHERE, więc obliczenia są wykonywane na przefiltrowanym zbiorze. Zazwyczaj funkcje zwracają pojedynczy rezultat dla całego polecenia SELECT, ale gdy użyto w zapytaniu grupowania, wtedy obliczenia wykonywane są oddzielnie dla każdej grupy (patrz następny punkt). 

Przykład 1-5. Funkcje agregujące

Jeśli chcemy znać średnią cenę wszystkich produktów z tabeli PART użyjemy następującego zapytania:

SELECT AVG(PRICE) AS AVG_PRICE

FROM PART;

Wynik:

AVG_PRICE

-----------

14.5

Jeśli chcemy liczbę części zapiszemy to tak:

SELECT COUNT(PNO)

FROM PART;

i otrzymamy:

COUNT

-------

4

1.4.1.4. Funkcje agregujące i grupowanie

SQL pozwala podzielić krotki w tabeli na grupy. Wtedy funkcje agregujące można stosować osobno dla każdej grupy. 

Grupowania dokonuje się przy pomocy klauzuli GROUP BY z podaniem atrybutów definiujących grupy. Jeśli mamy GROUP BY A₁, ⃛, A_k dzielimy relację na grupy tak, że krotki są w tej samej grupie wtedy i tylko wtedy, gdy pasują do wszystkich atrybutów A₁, ⃛, A_k.

Przykład 1-6. Funkcje agregujące

Jeśli chcemy znać liczbę produktów sprzedanych przez każdego dostawcę, zapisujemy zapytanie:

SELECT S.SNO, S.SNAME, COUNT(SE.PNO)

FROM SUPPLIER S, SELLS SE

WHERE S.SNO = SE.SNO

GROUP BY S.SNO, S.SNAME;

i otrzymujemy:

SNO | SNAME | COUNT

-----+-------+-------

1 | Smith | 2

2 | Jones | 1

3 | Adams | 2

4 | Blake | 3

Teraz zobaczmy jak to się dzieje. Najpierw następuje złączenie tabel SUPPLIER i SELLS:

S.SNO | S.SNAME | SE.PNO

-------+---------+--------

1 | Smith | 1

1 | Smith | 2

2 | Jones | 4

3 | Adams | 1

3 | Adams | 3

4 | Blake | 2

4 | Blake | 3

4 | Blake | 4

następnie grupowanie przez umieszczenie razem krotek z pasującymi atrybutami S.SNO i S.SNAME:

S.SNO | S.SNAME | SE.PNO

-------+---------+--------

1 | Smith | 1

| 2

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

2 | Jones | 4

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

3 | Adams | 1

| 3

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

4 | Blake | 2

| 3

| 4

W naszym przykładzie mamy cztery grupy i teraz możemy zastosować funkcję COUNT dla każdej grupy otrzymując rezultat podany wcześniej.

Zauważ, że zapytanie z GROUP BY i funkcjami agregującymi ma sens, gdy odwołujemy się do atrybutów grupowanych. Inne atrybuty mogą być użyte tylko wewnątrz argumentu funkcji. Inaczej nie będzie unikalnej wartości do połączenia z innymi atrybutami.

Nie ma sensu użycie funkcji na przykład w takiej formie AVG(MAX(sno)), ponieważ SELECT robi tylko jeden przebieg grupowania i agregacji. Można otrzymać wynik tego typu przy użyciu tabeli tymczasowej lub podzapytania z pierwszym stopniem agregacji w klauzuli WHERE.

SELECT (wiele tabel)

W przypadku wyświetlania danych z kilku tabel może wystąpić sytuacja, w której kolumny kilku tabel, a nawet same tabele (w przypadku związków rekurencyjnych) będą się powtarzać. W przypadku kolumn problem ten rozwiązywany jest poprzez poprzedzenie ich nazw zakończonej kropką nazwą tabeli, do której należą. (np. Osoby.Imie). W przypadku tabel możemy zastosować klauzulę AS, które zamienia nazwę danej tabeli na inną (np. Osoby AS Rodzice).

Jeśli chcemy wyświetlić dane z wielu tabel konieczne jest określenie sposobu łączenia krotek występujących w tych tabelach. W domyślnej postaci tworzony jest bowiem iloczyn kartezjański wszystkich krotek, co zazwyczaj nie jest pożądane.

Rozwiązaniem tego problemu może być określenie warunku, który będzie wymagał aby wartości kolumn w dwóch tabelach były identyczne (np. Osoby.PESEL = Adresy.PESEL). To rozwiązanie nie jest jednak doskonałe, gdyż w wyniku zostaną pominięte krotki, których wartość (lub zbiór wartości) dla łączonych atrybutów nie występuje w łączonej tabeli.

W języku SQL występuje specjalna klauzula JOIN, która pozwala rozwiązać problem tego rodzaju. Składnia polecenia SELECT z klauzulą JOIN jest następująca:

SELECT * FROM table1 JOIN table2 ON condition ...

condition określa sposób łączenia krotek. Zazwyczaj w warunku będzie wymagało się, żeby wartość kolumny w jednej tabeli była taka sama jak wartość kolumny w innej tabeli (table1.columnA = table2.columnB). Występują trzy wersje klauzuli JOIN:

• INNER

• LEFT [OUTER]

• RIGHT [OUTER]

W przypadku INNER JOIN dana krotka zostanie uwzględniona wyłącznie w wypadku, gdy w drugiej tabeli występuje krotka (lub krotki), których wartość dla kolumn określonych w warunku jest taka sama. Klauzula INNER jest opcjonalna (tzn. klauzula JOIN bez modyfikatorów działa jak INNER JOIN).

W przypadku LEFT JOIN dana krotka zostanie uwzględniona w wyniku, nawet jeżeli w drugiej tabeli nie będzie krotek, które mogłyby być z nią połączone (dla których były spełniony warunek ON…).

W przypadku RIGHT JOIN uwzględniane są krotki z drugiej tabeli, które nie posiadają odpowiedników wśród krotek tabeli pierwszej.

Pozostałe klauzule opisane w poprzednim punkcie (np. ORDER BY, LIMIT, etc.) działają również w przypadku łączenia wielu tabel.

Przykład

Tabela Osoby(Imie, Nazwisko, PESEL)

Imie	Nazwisko	PESEL
Jan	Andrzejewski	345
Jan	Wojtkiewicz	123
Wojciech	Kowalski	123

Tabela Adresy(PESEL, Ulica, Nr)

PESEL	Ulica	Nr
123	Sobieskiego	17
123	Zielonego	15
111	Abecadło	1

Dla zapytania:

SELECT Imie, Nazwisko, Osoby.PESEL, Adresy.PESEL, Ulica, Nr FROM

Osoby JOIN Adresy ON Osoby.PESEL = Adresy.PESEL

otrzymamy wynik:

Imie	Nazwisko	Osoby.PESEL	Adresy.PESEL	Ulica	Nr
Jan	Wojtkiewicz	123	123	sobieskiego	17
Wojciech	Kowalski	123	123	sobieskiego	17
Jan	Wojtkiewicz	123	123	zielonego	15
Wojciech	Kowalski	123	123	zielonego	15

Dla zapytania:

SELECT Imie, Nazwisko, Osoby.PESEL, Adresy.PESEL, Ulica, Nr FROM

Osoby LEFT JOIN Adresy ON Osoby.PESEL = Adresy.PESEL

otrzymamy wynik:

Imie	Nazwisko	Osoby.PESEL	Adresy.PESEL	Ulica	Nr
Jan	Andrzejewski	345	NULL	NULL	NULL
Jan	Wojtkiewicz	123	123	sobieskiego	17
Jan	Wojtkiewicz	123	123	zielonego	15
Wojciech	Kowalski	123	123	sobieskiego	17
Wojciech	Kowalski	123	123	zielonego	15

Dla zapytania:

SELECT Imie, Nazwisko, Osoby.PESEL, Adresy.PESEL, Ulica, Nr FROM

Osoby RIGHT JOIN Adresy ON Osoby.PESEL = Adresy.PESEL

otrzymamy wynik:

Imie	Nazwisko	Osoby.PESEL	Adresy.PESEL	Ulica	Nr
Jan	Wojtkiewicz	123	123	sobieskiego	17
Wojciech	Kowalski	123	123	sobieskiego	17
Jan	Wojtkiewicz	123	123	zielonego	15
Wojciech	Kowalski	123	123	zielonego	15
NULL	NULL	NULL	111	abecadlo	1