Łączenie tabel i

podzapytania.

(Join, Union

, select

)

Prezentacja do ćwiczenia 9

Materiały wykorzystane w przykładach:

Materiały pomocnicze do ćwiczeń laboratoryjnych\Ćwiczenie 9

Zakres ćwiczeń

Prezentacja przybliży zagadnienia związane z tworzeniem zapytań łączących

kilka tablic w celu uzyskania jednego wynikowego zbioru danych.

Poruszono problem zagnieżdżonych instrukcji SELECT i stosowania instrukcji

SELECT jako uzupełniające kryterium wyszukiwania.

W celu utrwalenia wiedzy prezentację zakończy ćwiczenie do

samodzielnego

wykonania.

Dane w relacyjnych bazach danych podzielone są na tabele. Proces
normalizacji bazy danych również niesie za sobą konieczność
dzielenia tablic. Prędzej czy później zaistnieje potrzeba łączenia
tablic, w celu uzyskania interesującej nas tabeli wynikowej.

Proces taki nazywamy złączeniem (po anglelsku join). Ze
względu na sposób łączenia dzieli się operacje złączeń na trzy grupy:

• złączenia wewnętrzne
• złączenia zewnętrzne
• złączenia bezwarunkowe (samozłączenia)

Podsumowując:

Złączenia umożliwiają łączenie danych z różnych tabel w pojedynczy
wiersz wynikowy.

Złączenia tabel w języku SQL

Podstawowe pytanie: jak łączone są ze sobą informacje z dwóch tabel?

Złączenia bezwarunkowe = CROSS JOIN

Tabele mogą różnić się od siebie wszystkimi cechami, nie

narzucanymi przez model relacyjny, a więc: ilością wierszy, ilością

kolumn, oraz ich typami. Kluczem jest pojęcie znane z algebry iloczyn

kartezjański.
Operacje łączenia tabel zawierają w trakcie ich realizacji wstępny etap

utworzenia nowej tabeli, będącej iloczynem kartezjańskim wierszy z

łączonych tabel.

Najprostsze zapytanie, pozwalające się o tym przekonać, ma postać:

SELECT * FROM tabela1, tabela2

Składnia powyższego zapytania oparta jest o standard języka SQL-89.

Nowsza postać róznoważnego zapytania, w standardzie oznaczanym

jako SQL-92, przedstawia się następująco:

SELECT * FROM tabela1 CROSS JOIN tabela2

Złączenia bezwarunkowe

Złączenie to generuje pełny iloczyn kartezjański obu zbiorów. Każdy

wiersz z 1

zbioru tworzy wiersz wynikowy z każdym wierszem zbioru .

Przykład :

Jeżeli 1 zbiór zawiera 10 wierszy, a drugi zawiera 100 wierszy to zbiór

wynikowy ma 1000 wierszy.

Złączenia bezwarunkowe
cd.

Złączenia wewnętrzne

Złączenia wewnętrzne odnoszą jeden wiersz w tabeli do jednego

lub większej liczby wierszy w innej tabeli. Każdy wiersz wyniku

złączenia wewnętrznego stanowi połączenie wartości z dwóch

związanych ze sobą wierszy.

Składnia zapytania wykorzystującego złączenie wewnętrzne

(INNER JOIN) oparta o standard języka SQL-89 (obecnie używany

jest standard SQL-92) wygląda następująco:

SELECT t1.kolumna1, t2.kolumna2
FROM tabela 1 as t1 INNER JOIN tabela 2 as t2
ON t1.kolumna1 = t2.kolumna2;

Słowo kluczowe INNER JOIN między nazwami tabeli wskazuje, że

złączenie powinno być złączeniem wewnętrznym. Klauzula ON

definiuje natomiast warunek złączenia lub warunek, który musi

oddziaływać na dwa wiersze, aby można było je uznać za

połączone.

Kolejność występowania nazw tabel w złączeniu wewnętrznym nie

ma znaczenia. Słowo INNER jest opcjonalne, te samo zapytanie

bez słowa kluczowego INNER zwróci taki sam rezultat.

W zapytaniach wykorzystujących złączenia można zastosować klauzulę
WHERE zawierającą dodatkowe kryteria wyszukiwania. W tym

przypadku

zapytanie będzie miało następującą postać:

SELECT t1.kolumna1, t2.kolumna2
FROM tabela 1 as t1 INNER JOIN tabela 2 as t2
ON t1.kolumna1 = t2.kolumna2;
WHERE t1.id_wiersza = 1;

Złączenia wewnętrzne

Złączać możemy kilka tablic, zapytanie będzie w tym przypadku

następującej

postaci:

SELECT t1.kolumna1, t2.kolumna2
FROM tabela 1 as t1 INNER JOIN tabela 2 as t2
ON t1.kolumna1 = t2.kolumna2;
INNER JOIN tabela 3 as t3
ON t2.id_wiersza = t3.id_wiersza;

Złączenia takie są realizowane w kolejności od lewej do prawej strony

instrukcji

SQL.
Najpierw tworzy się zbiór wynikowy ze złączenia tablicy 1 oraz tablicy 2 a

on z

kolei łączy się z tablicą 3 dając ostateczny zbiór wynikowy.

Złączenia wewnętrzne

Przykład 1:

Wyświetlamy wypożyczenia pojazdów dla klienta o nazwisku Uler.

SELECT

imie,nazwisko,marka,model

FROM

pojazdy p

INNER JOIN

(klient k

INNER JOIN

wypozyczenie w

k.id_klienta =

w.id_klienta)

p.id_pojazdy = w.id_pojazdy

WHERE

nazwisko='Uler';

Złączenia wewnętrzne -
przykład

Rysunek 1

Jak widać z rysunku 1, aby uzyskać interesujący nas rezultat należy wyświetlić
informacje z 4 tablic a co za tym idzie dokonać ich złączenia.

Dla przykładu w tabeli wypożyczenia znajduje się tylko pole id_klienta a brakuje
niektórych informacji np: imienia, nazwiska. Po informacje te należy sięgnąć do
tabeli „klient” dokonując złączenia tablic „wypożyczenia” oraz „klient” złączenie
dokonujemy za pomocą pola „id_klienta” które jest wspólne dla obu złączanych
tabel.

Złączenia wewnętrzne – przykład cd.

Złączenie zewnętrzne jest typem złączenia, w którym wiersz

zbioru wynikowego nie musi zawierać wierszy pochodzących z

obydwu tabel objętych złączeniem. Jedna lub dwie tabele mają

charakter opcjonalny. Ten typ złączenia należy stosować w

przypadku gdy złączenie powinno zostać wykonane, jeśli tylko jest

możliwe. Bez względu na to czy dokonano złączenia czy nie,

muszą zostać zwrócone pewne wiersze.

Złączenia zewnętrzne dzielimy na:

•

lewostronne

•

prawostronna

•

pełne złączenia

Złączenia zewnętrzne

Lewostronne złączenia zewnętrzne są wykorzystywane w

przypadkach, gdy w wyniku muszą zostać zwrócone wszystkie

wiersze z jednej tabeli niezależnie od tego, czy w drugiej tabeli

znajdują się jakiekolwiek odpowiadające im wiersze.

Przykładem może być chęć wyświetlenia klientów i ich

wypożyczeń, którzy nie koniecznie muszą posiadać w danym

czasie jakiekolwiek wypożyczenia.

Lewostronne złączenia

zewnętrzne

Przykład 2:

Wyświetlamy klientów którzy na wypożyczenie pojazdów wydali

kwotę od 100 do 500 zł.

Lewostronne złączenia

zewnętrzne

Rysunek 2

SELECT k.imie, k.nazwisko, w.cena
FROM klient AS k
LEFT JOIN wypozyczenie AS w
ON k.id_klienta = w.id_klienta
WHERE w.cena>100 And w.cena<500;

Znaczenie prawostronnego złączenia zewnętrznego jest niemal identyczne jak i lewostronnego złączenia

zewnętrznego. Jedyna istotna różnica na umiejscowieniu tablicy z wymaganymi wierszami po prawej

stronie instrukcji złączenia.

Przykład 3:

--SELECT k.id_klienta, k.imie, k.nazwisko, w.id_wypozyczenie

--FROM wypozyczenie as w RIGHT OUTER JOIN klient as k

--ON k.id_klienta = w.id_klienta

--WHERE nazwisko ='Uler' ;

SELECT k.id_klienta, k.imie, k.nazwisko, w.id_wypozyczenie

FROM klient as k

LEFT OUTER JOIN wypozyczenie as w ON k.id_klienta = w.id_klienta

WHERE nazwisko ='Uler' ;

Prawostronne złączenia

zewnętrzne

Oba powyższe zapytania z zwracają ten sam wynik (są równoważne).

Aby się przekonać wykonaj zapytania zawarte w pliku Przykład 3.sql).

W celu uniknięcia niejasności zaleca się stosowane złączeń

lewostronnych.

Rysunek 3

Prawostronne złączenia

zewnętrzne cd.

Zapytanie z przykładu 3 zwraca następujący zbiór wynikowy:

UNION

Słowo kluczowe UNION służy do łączenia wyników wykonania

dwóch instrukcji SELECT w jeden zbiór wynikowy. Ze zbioru tego

eliminowane są wszystkie duplikaty wierszy. W celu zachowania

zduplikowanych wierszy stosujemy słowo kluczowe UNION ALL.

Przykład 4:

Rysunek 4

Pierwsze zapytanie pobiera
klientów którzy mają
jakiekolwiek wypożyczenia
drugie zapytanie pobiera
wszystkich klientów
Słowo kluczowe UNION łączy
oba zbiory wynikowe. Słowo
kluczowe DISTINCT jest tu
zbyteczne ponieważ UNION
i tak odrzuci powtarzające
się wyniki.

SELECT DISTINCT k.imie, k.nazwisko,
k.nr_tel
FROM klient as k
INNER JOIN wypozyczenie as w
ON k.id_klienta = w.id_klienta
UNION
SELECT k.imie, k.nazwisko, k.nr_tel
FROM klient as k

SELECT w SELECT

Powyższe zapytanie wyświetla klientów, którzy nie mają żadnych

wypożyczeń a jednak mimo to są w tabeli klient.

Przykład 5:

Zastosowanie zagnieżdżonej instrukcji SELECT (SELECT w SELECT)
Pozwala np. na eliminacje pewnego zbioru ze zbioru wynikowego.

Rysunek 5

SELECT imie, nazwisko
FROM klient
WHERE nazwisko NOT IN
(SELECT DISTINCT k.nazwisko
FROM klient as k, wypozyczenie as w
WHERE k.id_klienta = w.id_klienta);

Ćwiczenia

1. Wyświetl wszystkich pracowników, którzy wypożyczyli

skutery (s – skuter w tabeli pojazdy) marki Honda

(wykorzystaj inner join)

2. Wyświetl wszystkie motocykle (m – motocykl w tabeli

pojazdy) o pojemności różnej od 650 cm3 (wykorzystaj

zagnieżdżoną instrukcję select)

Document Outline

Slide 1
Slide 2
Slide 3
Slide 4
Slide 5
Slide 6
Slide 7
Slide 8
Slide 9
Slide 10
Slide 11
Slide 12
Slide 13
Slide 14
Slide 15
Slide 16
Slide 17
Slide 18

Prezentacja do cwiczenia 9 mssql

Document Outline