5b

/*-------------------------------------------------------------------------------*/
/*Część II - Pobieranie danych*/
...

/*Odczytywanie danych z wielu tabel*/


/*
- Jak za pomocą jednego zapytania odczytać dane z wielu tabel?
- Co to jest złączenie naturalne? Czym różni się złączenie naturalne od nienaturalnego?
- Dlaczego łącząc tabele powinniśmy używać aliasów ich nazw?
*/


/*Złączenia naturalne i nienaturalne*/

/*Największy atut relacyjnych baz danych to łączenie odczytywanych tabel, wyników zapytań
i wyników wyrażeń tabelarycznych (podzapytania, widoki oraz funkcje). Jest możliwość
odtwarzania powiązanych ze sobą danych, które uprzednio zostały podzielone między
różne tabele. Można również na różne sposoby te dane łączyć.

Następujące zapytanie do tabeli dbo.Zamowienia pozwala poznać identyfikatory zamówień
i realizujących je spedytorów, ale nie można poznać nazw spedytorów.*/

/********************************************************************************************************/
      1
/********************************************************************************************************/
SELECT [ID zamowienia], [ID spedytora]
FROM dbo.Zamowienia
WHERE [ID spedytora]=1;

/*Wiemy, że kolumna kluczy obcych w jakiejś tabeli przechowuje duplikaty identyfikatorów
wierszy (kluczy podstawowych (głównych)) innej tabeli, dzięki czemu możemy powiązać między
sobą przechowywane w tych tabelach dane. Wartości klucza podstawowego (głównego) muszą być
niepowtarzalne. Natomiast w kolumnie klucza obcego mogą się powtarzać dowolną ilość razy.

Klucze obce umożliwiają w ten sposób powiązanie dwóch tabel związkiem (relacją) "jeden do wielu".

Powiązanie jest wykonywane poprzez złączenie od strony "wiele" do strony "jeden". Tabela
ze strony "wiele" zawierająca wiersze z kluczem obcym jest złączana krotkowo (przez wykonanie referencji)
z tabelą ze strony "jeden" zawierającą klucz główny - dla identycznych wartości klucza obcego
i głównego. W wyniku otrzymamy tabelę wynikową ze skontaktenowanymi ekstensjami dwóch tabel.

Ponieważ identyfikatory spedytorów są zapisane w obu tabelach (w tabeli dbo.Spedytorzy w kolumnie
klucza podstawowego (głównego), a w tabeli dbo.Zamowienia w kolumnie klucza obcego), to możemy ich użyć
do złączenia obu tabel i odczytania numerów zamówień zrealizowanch przez firmę wysyłkową.

Złączamy (konkatenujemy) wiersze o równych wartości klucza obcego i głównego.

Takie złączenie nazywamy złączeniem naturalnym (ogólnie nazywane złączeniem: klucz obcy-klucz główny.
Akademicka definicja złączenia naturalnego jest inna... To złączenie nazywamy teta-złączeniem.*/

/********************************************************************************************************/
      2
/********************************************************************************************************/
SELECT dbo.Zamowienia.[ID zamowienia], dbo.Spedytorzy.Firma --Nazwy kolumn poprzedzone nazwami tabel.
                                                            --Zapobiega to błędom. Aliasy będą omówione dalej.
FROM dbo.Spedytorzy JOIN dbo.Zamowienia                     --Nazwy odczytywanych (złączanych) tabel.
ON dbo.Zamowienia.[ID spedytora] = dbo.Spedytorzy.ID        --Warunki łączenia tabel. Do wyniku zapytania
WHERE dbo.Spedytorzy.Firma='Firma wysylkowa A';             --trafią tylko te wiersze z obu tabel, dla
                                                            --których kolumny [ID spedytora] są takie jak
                                                            --wartości kolumny ID.
                                                            --Wygenerowanie wszystkich możliwych kombinacji
                                                            --łączonych tabel, a następnie usunięcie z tak otrzymanego
                                                            --wierszy zbioru pośredniego, wierszy niespełniających
                                                            --warunku złączenia.

/*UWAGA: W ekstensji tabel złączanych nie ma krotek osieroconych. DLATEGO pozycje tabel (operandów) operatora JOIN
można zamienić i otrzymane wyniki zapytania nie zmienią się. Innym powodem takiej możliwości jest wpływ
optymalizatora algebraicznego DBMSu na analizę leksykalną zapytań i generowanie poprawnego (choć nie zawsze) kodu
algebraicznego do wykonywania zapytań. Zamiana pozycyjna tabel NIE JEST ZATEM ZALECANA (czasami występują błędy
logiczne w tak zapisanych złączeniach) i znacznie utrudnia to ich wykrywanie.*/

/*Operator JOIN ... ON można zastąpić warunkiem WHERE:*/

SELECT dbo.Zamowienia.[ID zamowienia], dbo.Spedytorzy.Firma --Można złączać bez użycia operatora JOIN
FROM dbo.Spedytorzy, dbo.Zamowienia                         --i słowa kluczowego ON, stosując klauzulę WHERE
                                                            --z (odpowiednim) warunkiem selekcji krotek złączanych.
                                                            --Silnik DBMS wie, że ma złączać od strony jeden do wiele.
WHERE dbo.Zamowienia.[ID spedytora] = dbo.Spedytorzy.ID AND dbo.Spedytorzy.Firma='Firma wysylkowa A';


SELECT dbo.Zamowienia.[ID zamowienia], dbo.Spedytorzy.Firma --Nazwy kolumn poprzedzone nazwami tabel.
                                                            --Zapobiega to błędom. Aliasy będą omówione dalej.
FROM dbo.Spedytorzy JOIN dbo.Zamowienia                     --Nazwy odczytywanych (złączanych) tabel
ON dbo.Zamowienia.[ID spedytora] = dbo.Spedytorzy.ID
WHERE dbo.Spedytorzy.Firma='Firma wysylkowa A';

/********************************************************************************************************/
      3
/********************************************************************************************************/
SELECT dbo.Zamowienia.[ID zamowienia], dbo.Spedytorzy.Firma
FROM dbo.Zamowienia, dbo.Spedytorzy
WHERE dbo.Zamowienia.[ID spedytora] = dbo.Spedytorzy.ID
AND dbo.Spedytorzy.Firma = 'Firma wysylkowa B'; --WHERE jest wykonywane po FROM. Warunki te (złączenia i wyboru)
                                                --nie powinny być stosowane zamiennie (tu w celu filtrowania raz
                                                --po firmie Firma wysyłkowa A, a innym razem po firmie
                                                --Firma wysyłkowa B - co jest "zmieszaniem" złączania i filtrowania
                                                --wynikow).
                                                --Lepiej zatem używać jawnie JOIN ... ON.
                                                --Zatem zastępowanie złączeniowego operatora JOIN ... ON warunkiem
                                                --WHERE nie jest zalecane.
/*
Klauzula WHERE jest wykonywana po klauzuli FROM, a więc możemy w niej wyeliminować dowolne, zwracane przez
klauzulę FROM wiersze, ale operacja odwrotna jest niemożliwa. Dlatego warunki złączenia i wyboru nie powinny
być stosowane zamiennie.

Zastępowanie złączania filtrowaniem ma kilka zasadniczych wad:

1. Jest niezgodne ze standardem SQL.
2. Pogarsza czytelność zapytań, szczególnie tych z rozbudowaną klauzulą WHERE.
3. W złączeniach zewnętrznych (omówimy je później) może być przyczyną trudnych do wykrycia błędów logicznych.


"Naturalny" związek między danymi...

Dane klienta (zapisane w tabeli dbo.Klienci), który złożył zamówienie o podanym numerze (zapisanym w tabeli
dbo.Zamówienia):
*/
/********************************************************************************************************/
      4
/********************************************************************************************************/
SELECT Firma
FROM dbo.Klienci JOIN dbo.Zamowienia
ON dbo.Klienci.ID = dbo.Zamowienia.[ID klienta]
WHERE [ID zamowienia]=31;


/*
DYGRESJA (dla bardziej zaawansowanych): Serwer SQL 11 (i wersje wyższe) nie obsługuje stadardowego operatora NATURAL JOIN.
Nie obsługuje też skróconego JOIN ... USING (kolumna) spotykanych w innych dialektach SQL. Zamiast raz podać nazwę kolumny
występującej w obu tabelach, trzeba w klauzuli ON dwukrotnie ją powtórzyć. Nie stosuje się tu tzw. kompatybilności
typów kolumn...


Język SQL pozwala też na wykonywanie złączeń na podstawie wartości niekluczowych.

Ponieważ wynik takiego złączenia nie odzwierciedla naturalnego połączenia danych,
takie złączenie nazywa się złączeniem nienaturalnym (ale nazwa akademicka to: złączenie naturalne...! vide wykład).


Im bardziej znormalizowana baza danych, tym mniej zawiera duplikatów danych i tym rzadziej używane są
w niej złączenia nienaturalne. W bazie Northwind_bezplzn, w tabelach dbo.Klienci i dbo.Pracownicy, są
zapisane nazwiska (odpowiednio klientów i pracowników, oraz nazwy miast, w ktorych mieszkają. Zatem można
złączyć obie te tabele w nienaturalny sposób, na podstawie nazw miast. Wynikiem takiego złączenia jest
lista nazwisk klientów i pracowników mieszkających w tych samych miastach.
*/

/*Lista nazwisk klientów i pracowników mieszkających w tych samych miastach;
*/
/********************************************************************************************************/
      5
/********************************************************************************************************/
SELECT dbo.Klienci.Nazwisko, dbo.Pracownicy.Nazwisko,
dbo.Klienci.Miasto, dbo.Pracownicy.Miasto
FROM dbo.Klienci JOIN dbo.Pracownicy
ON dbo.Klienci.Miasto = dbo.Pracownicy.Miasto; --Analiza: W bazach relacyjnych do łączenia tabel należy
                                               --używać kluczy. Złączenia nienaturalne odwołujące się do
                                               --kolumn, które mogą zawierać powtarzające się wartości,
                                               --są niejednoznaczne. Na przykład, krotki dla nazwisk
                                               --Obwarzanek, Ciesielska, Bochenek, Radzikowska są
                                               --kilkakrotnie powtórzone.

/*Żeby się o tym upewnić, wystarczy dodać do wyniku poprzedniego zapytania identyfikatory pracowników i
klientów:*/

/********************************************************************************************************/
      6
/********************************************************************************************************/
SELECT dbo.Klienci.ID, Klienci.Nazwisko, dbo.Pracownicy.ID, dbo.Pracownicy.Nazwisko,
dbo.Klienci.Miasto, dbo.Pracownicy.Miasto
FROM dbo.Klienci JOIN dbo.Pracownicy
ON dbo.Klienci.Miasto = dbo.Pracownicy.Miasto;

/*Ponieważ dwóch klientów i jeden pracownik mieszka w Gdańsku, dane pracownika z Gdańska musiały być powtórzone.
Jest też sporo innych powtórzeń, bo dwóch klientów i czterech pracowników mieszka w Pruszkowie. Dane klienta
z Pruszkowa zostały powtórzone czterokrotnie, a dane pracowników z Gdańska dwukrotnie.*/


/*Aliasy*/

/*
Nie stosujemy, jeśli zapytanie odwołuje się tylko do jednej tabeli, poprzedzanie nazw kolumn nazwą tabeli jest
niepotrzebne (nazwy kolumn są jednoznaczne).
Gdy zapytanie odwołuje się do wielu tabel, to nazwy kolumn mogą występować w różnych tabelach i serwer bazodanowy
nie jest w stanie tylko na podstawie nazw określić, do której kolumny chcieliśmy się odwołać:
*/

/********************************************************************************************************/
      7
/********************************************************************************************************/
SELECT ID                                                   --Zapytanie błędne. Wielznaczność nazwy kolumny ID.
FROM dbo.Produkty JOIN dbo.[Szczegoly zamowienia zakupu]    --Ambiguous column name 'ID'.
ON dbo.Produkty.[ID dostawcow]= dbo.[Szczegoly zamowienia zakupu].[ID produktu];

/*Za pomocą aliasów można polepszyć czytelność zapytań z długimi nazwami tabel, czasami także wobec wielokrotnych
wystąpień tych nazw w zapytaniu. Powinniśmy im nadać aliasy a potem tych aliasów konsekwentnie używać w całym
zapytaniu.*/

/********************************************************************************************************/
      8
/********************************************************************************************************/
SELECT P.ID
FROM dbo.Produkty AS P JOIN dbo.[Szczegoly zamowienia zakupu] AS S
ON P.ID = S.[ID produktu];

/*Stosując aliasy unikamy też trudnych do wykrycia błędów wynikających ze sposobu sprawdzania nazw obiektów przez
serwer. Gdybyśmy się pomylili wpisując nazwę kolumny, a w którejś ze złączanych tabel istniałaby kolumna
o wprowadzonej przez nas nazwie, serwer nie zgłosiłby błędu, tylko odczytałby dane z innej kolumny, niż chcieliśmy.*/


/*Złączenia równościowe i nierównościowe*/
/********************************************************************************************************/
      9
/********************************************************************************************************/
SELECT Z.[ID zamowienia], SZ.[ID produktu], SZ.[ID zamowienia]
FROM dbo.Zamowienia AS Z JOIN dbo.[Szczegoly zamowien] AS SZ
ON Z.[ID zamowienia] = SZ.[ID produktu]; --Od razu widać po wyniku, że jest to wynik niepożądany (niepoprawny).
--                     xxxxxxxxxxxxxxxx  --Powód: wartości kluczy podstawowych z reguły są takie same, co wykazuje
                                         --to złączenie równościowe. Ale przecież chodziło o złączenie równościowe
                                         --klucz obcy - klucz podstawowy, co widać w zapytaniu poprawnym poniżej...,
                                         --również w wynikach zapytania.
/********************************************************************************************************/
      10
/********************************************************************************************************/
SELECT Z.[ID zamowienia], SZ.[ID produktu], SZ.[ID zamowienia]
FROM dbo.Zamowienia AS Z JOIN dbo.[Szczegoly zamowien] AS SZ
ON Z.[ID zamowienia] = SZ.[ID zamowienia];
--                     xxxxxxxxxxxxxxxxxx

/*Warunki złączeń mogą być inne niż równościowe. Wtedy stosujemy inne operatory relacyjne.*/
/********************************************************************************************************/
      11
/********************************************************************************************************/
SELECT P.[Kod produktu], P.[Cena katalogowa], SZ.[Cena jednostkowa]
FROM dbo.Produkty AS P JOIN dbo.[Szczegoly zamowien] AS SZ
ON P.[Cena katalogowa] > SZ.[Cena jednostkowa];

/*Intencją zapytania było sprawdzenie, które ceny dla konsumenta mają narzut.

Po wynikach złączeń z powyższego zapytania widać, że zawierają one mmnóstwo powtórzonych wierszy. Jednym
z nielicznych zastosowań tych złączeń jest analiza danych polegająca na wyszukiwaniu istniejących między tymi danymi
zależności. Złączenia nierównościowe z reguły występują razem ze złączeniami naturalnymi - jak w następującym
zapytaniu:*/
/********************************************************************************************************/
      12
/********************************************************************************************************/
SELECT P.[Kod produktu], P.[Cena katalogowa], SZ.[Cena jednostkowa]
FROM dbo.Produkty AS P JOIN dbo.[Szczegoly zamowien] AS SZ
ON P.[Cena katalogowa] >= SZ.[Cena jednostkowa] AND P.ID =SZ.[ID produktu];

--Widzimy, że wszystkie ceny dla konsumenta mają narzut (choć mogłyby go nie mieć)...

--UWAGA: Cena katalogowa (hurtowa) jest zwykle większa (o narzut) od ceny jednostkowej (detalicznej, dla konsumenta).

/*Złączenia zewnętrzne OUTER.*/

/*-------------------------------------------------------------------------------*/