Dotazy nad více tabulkami - Joiny

Spojení tabulek
STRANA

Spojení tabulek
STRANA

Spojování tabulek
STRANA

Spojení / Join
Spojení tabulek umožňuje v jednom dotazu získat data z více tabulek
Syntaxe
Alternativní Oracle syntaxe
Podmínka spojení je v klauzuli WHERE
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column1 = table2.column2;
STRANA
SELECT table1.column, table2.column
FROM table1
JOIN table2 ON table1.column1 = table2.column2;

Spojení tabulek
STRANA
SELECT zamestnanci.jmeno, zamestnanci.prijmeni, oddeleni.jmeno_odd
FROM zamestnanci JOIN oddeleni ON zamestnanci.oddeleni = oddeleni.oddeleni_id;

Příklad
Vypište seznam zaměstnanců a jmen oddělení, které sídlí v Praze
STRANA

Příklad - řešení
STRANA
SELECT zamestnanci.jmeno, zamestnanci.prijmeni, oddeleni.jmeno_odd
FROM zamestnanci JOIN oddeleni ON zamestnanci.oddeleni = oddeleni.oddeleni_id
WHERE oddeleni.lokalita = 'PRAHA';

Aliasy pro jména tabulek
STRANA
SELECT zamestnanci.jmeno, zamestnanci.prijmeni,
oddeleni.jmeno_odd
FROM zamestnanci
JOIN oddeleni ON zamestnanci.oddeleni = oddeleni.oddeleni_id;
SELECT zam.jmeno, zam.prijmeni,
odd.jmeno_odd
FROM zamestnanci zam
JOIN oddeleni odd ON zam.oddeleni = odd.oddeleni_id;

Příklad
Vypište seznam oddělení a jména jejich managerů.(Podle manager_id v tabulce oddeleni)
STRANA

Příklad řešení
STRANA
SELECT o.jmeno_odd, z.jmeno, z.prijmeni
FROM zamestnanci z
JOIN oddeleni o ON z.zamestnanec_id = o.manager_id;

Kartézský součin
Pokud podmínka spojení chybí nebo není platná, pak je výsledkem kartézský součin, který obsahuje všechny kombinace řádků
Kartézský součin tabulky s 20 záznamy s tabulkou s 30 záznamy obsahuje 600 záznamů
STRANA

Kartézský součin
STRANA
SELECT COUNT(*)
FROM zamestnanci, oddeleni;

Spojení tabulky se stejnou tabulkou
STRANA

Příklad
Vypište jména zaměstnanců a jména jejich přímých nadřízených
STRANA

Příklad - řešení
STRANA
SELECT z.jmeno || ' ' || z.prijmeni AS Zamestnanec, m.jmeno || ' ' || m.prijmeni AS Nadrizeny
FROM zamestnanci z
JOIN zamestnanci m ON z.nadrizeny_id = m.zamestnanec_id;

Příklad
Kteří zaměstnanci nastoupili dříve než jejich nadřízení?
STRANA

Příklad - řešení
STRANA
SELECT z.jmeno, z.prijmeni
FROM zamestnanci z
JOIN zamestnanci m ON z.nadrizeny_id = m.zamestnanec_id
WHERE z.datum_nastupu < m.datum_nastupu;

Spojení více tabulek
Spojení tří tabulek
Alternativní Oracle syntaxe
Pro spojení n tabulek potřebujeme n-1 spojovacích podmínek
SELECT table1.column, table2.column, table3.column
FROM table1
JOIN table2 ON table1.column1 = table2.column2
JOIN table3 ON table2.column3 = table3.column4;
STRANA
SELECT table1.column, table2.column, table3.column
FROM table1, table2, table3
WHERE table1.column1 = table2.column2 AND
table2.column3 = table3.column4;

Příklad
Napište seznam oddělení a celkových částek vyplacených na odměnách. Výsledek setřiďte podle odměn od nejvyšších po nejnižší.
STRANA

Příklad - řešení
STRANA
SELECT o.jmeno_odd, SUM (p.premie)
FROM zamestnanci z
JOIN oddeleni o ON o.oddeleni_id = z.oddeleni
JOIN platy p ON z.zamestnanec_id = p.id_zamestnance
GROUP BY o.jmeno_idd
ORDER BY SUM (p.premie) DESC;

Spojení více tabulek
Příklad:
Jak dlouho byli celkem v roce 2003 nemocní zaměstnanci, kteří pracují v Praze?
STRANA
SELECT SUM(p.dnu_nemoc)
FROM platy p
JOIN zamestnanci z ON z.zamestnanec_id = p.id_zamestnance
JOIN oddeleni o ON o.oddeleni_id = z.oddeleni
WHERE o.lokalita = 'PRAHA' AND p.rok = 2003;

Neekvivalentní vazba
Neekvivalentní spojení je například vazba mezi tabulkami ZAMESTNANCI a PLAT_TRIDY. Znamená to, že žádný sloupec tabulky ZAMESTNANCI neodpovídá přímo žádnému sloupci tabulky PLAT_TRIDY.
STRANA

Neekvivalentní vazba
Vztah mezi tabulkami je takový, že hodnota sloupce ZAKLADNI_PLAT v tabulce ZAMESTNANCI musí být mezi hodnotami MIN_PLAT a MAX_PLAT v tabulce PLAT_TRIDY. Takový vztah vzniká použitím jiného operátoru než rovnítka (=).
STRANA

OUTER JOIN
STRANA
SELECT z.jmeno, z.prijmeni, SUM (p.premie)
FROM zamestnanci z
LEFT JOIN platy p ON z.zamestnanec_id = p.id_zamestnance
GROUP BY z.jmeno, z.prijmeni
ORDER BY z.jmeno, z.prijmeni;

OUTER JOIN – Vnější vazba
Spojení dvou tabulek obsahuje pouze takové záznamy, pro které je splněna propojovací tabulka
Vnější vazba umožňuje do výsledku dotazu zařadit i takové záznamy, proto které neexistuje ekvivalentní záznam v jedné z tabulek
STRANA

OUTER JOIN – Vnější vazba
Syntax (SQL 99):
LEFT nebo RIGHT označuje stranu (tabulku), která má veškeré informace a tyto budou zobrazeny – nemusí být zobrazeny všechny informace z připojované tabulky (informace mohou chybět)
SELECT table1.column, table2.column
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;
STRANA

OUTER JOIN – Vnější vazba
Syntax (Oracle):
(+) je na té straně podmínky, kde předpokládáme nedostatek informací
SELECT table1.column, table2.column
FROM table1, table2
WHERE table1.column [ (+) ] = table2.column[(+)];
STRANA

Spojování tabulek pomocí SQL:
Syntax 99
SELECT table1.column, table2.column
FROM table1
[CROSS JOIN table2] |
[NATURAL JOIN table2] |
[JOIN table2 USING (column_name)] |
[JOIN table2
ON(table1.column_name = table2.column_name)] |
[LEFT|RIGHT|FULL OUTER JOIN table2
ON (table1.column_name = table2.column_name)];
STRANA

Cvičení
Kteří zaměstnanci mají vyšší nebo stejný plat jako jejich nadřízení?
STRANA
SELECT z.jmeno, z.prijmeni
FROM zamestnanci z
JOIN zametnanci m ON z.nadrizeny_id = m.zamestnanec_id
WHERE z.zakladni_plat >= m.zakladni_plat;

Cvičení
Kolik bylo v roce 2003 vyplaceno na odměnách zaměstnancům jednotlivých oddělení
STRANA
SELECT o.jmeno_odd, SUM (p.premie)
FROM oddeleni o
JOIN zamestnanci z ON o.oddeleni_id = z.oddeleni
JOIN platy p ON z.zamestnanec_id = p.id_zamestnance
WHERE p.rok = 2003
GROUP BY o.jmeno_odd;

Cvičení
Kolik dní dovolené si vybrali zaměstnanci Účtárny?
STRANA
SELECT SUM (p.dnu_dovolena)
FROM oddeleni o
JOIN zamestnanci z ON o.oddeleni_id = z.oddeleni
JOIN platy p ON z.zamestnanec_id = p.id_zamestnance
WHERE o.jmeno_odd = 'UCTARNA';

Cvičení
Jaký je průměrný plat zaměstnanců, kteří pracují v Praze?
STRANA
SELECT AVG (z.zakladni_plat)
FROM oddeleni o
JOIN zamestnanci z ON o.oddeleni_id = z.oddeleni
WHERE o.lokalita = 'PRAHA';

Cvičení
Kdo má nadřízeného z jiného oddělení než sám pracuje? Vypište celé jméno, pozici a jméno oddělení, ze kterého pochází on i jeho nadřízený.
STRANA
SELECT z.jmeno, z.prijmeni, z.pozice, oz.jmeno_odd, om.jmeno_odd
FROM zamestnanci z
JOIN zamestnanci m ON z.nadrizeny_id = m.zamestnanec_id
AND z.oddeleni <> m.oddeleni
JOIN oddeleni oz ON z.oddeleni = OZ.oddeleni_id
JOIN oddeleni om ON m.oddeleni = om.oddeleni_id;

Cvičení
Kolik je průměrný plat nadřízených zaměstnanců, kteří mají plat nižší než je průměrný plat na jejich oddělení. Každého nadřízeného započítejte do průměru jen jednou.
STRANA
SELECT AVG (plat)
FROM (
SELECT DISTINCT m.prijmeni, z.zakladni_plat AS plat
FROM zamestnanci a
JOIN zamestnanci m ON a.nadrizeny_id = m.zamestnanec_id
JOIN
(SELECT oddeleni, AVG (zakladni_plat) AS avgsal
FROM zamestnanci GROUP BY oddeleni) b
ON a.oddeleni = b.oddeleni
WHERE a.zakladni_plat < b.avgsal;