Bazy Danych

Opis zajęć i zasad

Zajęcia 4

Złączenia tabel (JOIN) *

CROSS JOIN

Na zajęciach drugich pojawił się już przykład zapytania korzystającego z dwóch tabel:

select pracownicy.nazwisko, oddzialy.Nazwa miasto
from Pracownicy, Oddzialy

Połączenie tabel tego typu nazywamy naturalnym połączeniem, jako wynik otrzymujemy iloczyn kartezjański obu tabel, tzn. każda krotka jednej tabeli połączona jest z każdą krotką drugiej.

Połączenie kartezjańskie możemy zapisać także w następującej postaci:

SELECT p.nazwisko, o.Nazwa
FROM Pracownicy AS p
CROSS JOIN Oddzialy AS o

Żeby pozbyć się nieinteresujących nas połączeń możemy wykorzystać także klauzulę where

select *
from Pracownicy, Oddzialy
where Pracownicy.ID_Oddz = Oddzialy.ID

Tego typu zapytanie nazywane jest też "old-style inner join".

INNER JOIN

Połączenie dwóch tabel definiujemy w sekcji FROM. Połączenie nazwiemy inner join kiedy opiera się na porównaniu kolumn dwóch tabel za pomocą operatora porównania (<,=,>).

SELECT p.nazwisko, o.Nazwa
FROM Pracownicy AS p
INNER JOIN Oddzialy AS o
ON p.Id_Oddz = o.ID

Zapytanie z warunkiem nierównościowym:

SELECT p.nazwisko, o.Nazwa
FROM Pracownicy AS p
INNER JOIN Oddzialy AS o
ON p.Id_Oddz = o.ID AND p.Nazwisko > o.Nazwa

SELF JOIN

Tabela może być połączona z samą sobą, takie połączenie nazywamy SELF JOIN. Na przykład zapytanie zliczające ilu każda osoba ma współpracowników w oddziale:

SELECT p.nazwisko, count(*)
FROM Pracownicy AS p
INNER JOIN Pracownicy AS p2
ON p.Id_Oddz = p2.ID_Oddz AND p.ID <> p2.ID
group by p.nazwisko

LEFT JOIN

Zawiera jako wynik wszystkie wiersze lewej tabeli, a nie tylko te, które znajdują dopasowanie do warunku połączenia tabel. W przypadku, gdy jakiś wiersz nie ma odpowiednika przypisywane mu są jako join wartości null.

SELECT o.id, o.Nazwa, p.nazwisko
FROM Oddzialy AS o
LEFT JOIN Pracownicy AS p
ON o.Id = p.ID_Oddz

Gdzie w przypadku miasta GDANSK otrzymujemy wartość NULL.

RIGHT JOIN

Zawiera jako wynik wszystkie wiersze prawej tabeli, a nie tylko te, które znajdują dopasowanie do warunku połączenia tabel. W przypadku, gdy jakiś wiersz nie ma odpowiednika przypisywane mu są jako join wartości null.

SELECT o.id, o.Nazwa, p.nazwisko
FROM Oddzialy AS o
RIGHT JOIN Pracownicy AS p
ON o.Id = p.ID_Oddz

RIGHT JOIN różni się od LEFT join tylko tym, którą tabelę traktuję jako połączeniową.

FULL JOIN

Zawiera wszystkie wiersze wynikowe, w przypadku, gdy nie ma odpowiednika jakiejś z wartości przypisywane są do nich wartośći null.

SELECT o.id, o.Nazwa, p.nazwisko
FROM Oddzialy AS o
FULL JOIN Pracownicy AS p
ON o.Id = p.ID_Oddz

LEFT, RIGHT i FULL JOIN zwane są także połączeniami zewnętrznymi (OUTER JOIN).

Większa liczba tabel

Połączenie trzech, czy też większej liczby tabel realizujemy w następujący sposób:

SELECT p.nazwisko, o.Nazwa, s.Placa_min, s.Placa_max, p.Placa_pod
FROM Pracownicy AS p
INNER JOIN Oddzialy AS o
on p.ID_Oddz = o.ID
INNER JOIN Stanowiska as s
on s.Stanowisko = p.Stanowisko

Operacje na zapytaniach

UNION, EXCEPT i INTERSECT są operacjami na zbiorach wyników. Aby operacje te zadziałały liczba kolumn wszystkich wyników zapytań musi być taka sama.

UNION

SELECT Nazwisko
FROM Pracownicy
UNION
SELECT Nazwa
FROM Oddzialy

Typ danych odpowiednich kolumn w wynikach także muszą się zgadzać, przykład nieprawidłowego zpaytania:

SELECT Placa_pod
FROM Pracownicy
UNION
SELECT Nazwa
FROM Oddzialy

W przypadku, gdy interesują nas powtórzenia wyników stosujemy operator UNION ALL:

SELECT Nazwisko, Placa_pod, Placa_dod
FROM Pracownicy
WHERE Placa_dod > 100
UNION ALL
SELECT Nazwisko, Placa_pod, Placa_dod
FROM Pracownicy
WHERE Placa_pod > 2000
order by Nazwisko

EXCEPT

Zwraca różnice zbiorów:

SELECT Nazwisko
FROM Pracownicy
EXCEPT
SELECT Nazwisko
FROM Pracownicy
WHERE Nazwisko like 'S%'

INTERSECT

Zwraca część wspólną wyników:

SELECT Nazwisko, Placa_pod, Placa_dod
FROM Pracownicy
WHERE Placa_dod > 100
INTERSECT
SELECT Nazwisko, Placa_pod, Placa_dod
FROM Pracownicy
WHERE Placa_pod > 2000

Zadanie Domowe

Termin wykonania zadania: Sobota 29.10.2016 do godziny 24.00.

Wykonać zadania 9-16

Rozwiązania proszę przesłać przez stronę:

Logujemy się jak na komputery Wydziałowe, przesyłamy plik (jeden wspólny dla wszystkich) z rozwiązaniami.

UWAGA! Rozwiązania można przesłać tylko raz.

*

Wykorzystano materiały z

http://www.sql-kursy.pl/ms-sql-kurs-join-union-except-2.html

https://technet.microsoft.com/en-us/library/ms191472(v=sql.105).aspx

https://msdn.microsoft.com/pl-pl/library/ms180026(v=sql.110).aspx