imported>Pavel: /* INNER JOIN (vnitřní spojení) */

2012-02-24T09:45:11Z

INNER JOIN (vnitřní spojení)

Nová stránka

[[Category:Articles]]
OUTER JOIN (LEFT or RIGHT) is probably most complex clause of SELECT statement. A beginners
that can use this clause can use SQL well. Little bit more difficult corelated subqueries are
not necessary now and with some luck anobody doesn't use it. But using of JOINs is every day
work for SQL coder. This article is oriented to beginners (with really basic knowleadge of SQL).

==Prolog, Statement SELECT==
SELECT statement is basic fundament of SQL. It is hard to believe what wide spectrum of tasks should
be solved with this simple statement. SELECT is divided into optional clauses. Order of clauses is
important. Some clause is used when it is necessary.
<pre>
SELECT <expression list> -- expression can be reference to some column
FROM <source relations> -- tables, derivated tables
WHERE <predicates> -- only rows with true predicates are showed
GROUP BY <expression list> -- specify subsets for agrregates
</pre>
There are some other clauses (HAVING, ORDER BY, LIMIT, ...), but these not used in this article,
and it is reason why it missing here.

==Normalisation==
One from basic principles of relation databases is a saving data in separated
tables. This is described by so called normal forms. There was more motivations,
some should be effective using of persistent media (there are no redundant data)
and some unnecessary overwriting is minimized. Current hardware is really different,
but these old requests and principles are same.

Not well normalized tables can break a performance and it is signal not well designed
databases. Using of wide tables can decrease speed of sequential scan, because it
implicate a reading of unwanted data. Using too wide tables can increase a complexity
of updates data.

These requests are not valid for OLAP (analytical) databases. There are some special
engines (column databases) usually and there are a minimal requests for random updating of data.

So data in relation databases are in more tables usually. And we have to be able to
join data to one result. SELECT statement can do it simply and effectively.

==Primary Keys and Foreign keys==
There are simple and composite keys. Simple keys are related to one column, composite keys are based on more columns.
In this article we will use only simple keys. Primary key is column, that identifies exactly one row in table. Foreign
key is column with values that can specify rows from other table (usual use case) or from same table.

Example (Author, Book)
<pre>
Relation Authors (id, name, surname) .. id is primary key of relation Authors
Relation Books(id, author_id, name) ... id is primary key of relation Authors, author_id is foreign key to relation Authors
</pre>
When author_id's value is 10, then this book was written by author with id 10.

Older relation databases enables join of tables only on PK,FK relations. SQL is not too strict. JOIN is not limited. It
is strong tool, but it should be source of some strange errors too. So beginners should be used on JOINs based on PK,FK
keys.

==Predicate==
Predicate is a expression with result true or false. You can use it in WHERE clause usually.
<pre>
SELECT name, surname, age
FROM Staff
WHERE age >= 30;
</pre>
Predicate "age >= 30" is true for some subset of employees. This is filter for result. Only these
employees will be in result.

Predicates can be composed with boolean operators AND, OR. When predicates is in form (column operator value),
then planner can use it as index parameter. So:
* well a > b, wrong a + 1 > b + 1,
* well a > 10, wrong a + 5 > 15

==Cartesian product==
Cartesian product is one form of merging two relations (some other are: sum, difference, intersection, ..). Result of Cartesian product are all possible combination items from related sets. Cartesian product is possible for tables too, but only subset of Cartesian product has sense. SQL creates Cartesian product for all tables (relations) from WHERE clause. Example: When table Author has 5 rows and table Books 10 rows, then result will have 50 rows:
<pre>
SELECT *
FROM Authors, Books;
</pre>
that is complete Cartesian product of tables Author and Books.

==INNER JOIN==
Is good style to separate predicates related to table's joining and predicates related to
WHERE clause. This separation is available via JOIN keyword.

Note: SQL has not tools for query semantic validation. Badly created query can be correct
from syntax view, but wrong from semantic view - it can returns wrong (messy) result. Sometimes
final user can identify these problems - because he is missing some data, or he see some data
more times. Structured notation (like Joe Celko's notation) can decrease a number of errors.

INNER JOIN is most simple variant of JOIN (Note: INNER and OUTER keywords are optional and these keywords
are not used usually). The result of JOIN is Cartesian product filtered by predicate.
<pre>
SELECT title, surname, name
FROM Books
JOIN
Authors
ON Books.author_id = Authors.id
</pre>
Look on predicate. This predicate shows a relation between primary key and foreign key. The query
shows book's title and name and surname of Book's author. When we would to books written by one
author, we can to modify query:
<pre>
SELECT title, surname, name
FROM Books
JOIN
Authors
ON Books.author_id = Authors.id
WHERE surname = 'Capek'
AND name = 'Jan'
</pre>
There are two predicates in two clauses. Older notation used only WHERE clause for predicates:
<pre>
SELECT title, surname, name
FROM Books, Authors
WHERE Books.author_id = Authors.id
AND surname = 'Capek'
AND name = 'Jan'
</pre>
This query is simple and readable. But more complex queries was badly written sometimes. Missing
predicates for joining can produce very slow and expensive query - using JOIN keyword is good
protection again this kind of errors.

==Rychlost==
Ohledně efektivity operace slučování tabulek koluje neuvěřitelné množství mýtů a fám.
Většina programátorů používala SQL, aniž by do hloubky rozuměli technologii a docela
často zaměňovali příčinu s důsledkem. Na vině byly i ne zcela dotažené a odladěné
databáze.

* není žádný rozdíl ve výkonnosti mezi použitím JOINu a původním zápisem pro spojení tabulek. Pamatuji doby, kdy se JOIN nepoužíval právě z obavy o ztrátu výkonu. To je nesmysl. Liší se pouze zápis. Smysl a provádění je stejné.
* nehraje roli jaké sloupce spojujete (jestli jsou nebo nejsou primárním či cizím klíčem), ale je důležité, jestli na těchto sloupcích je index, a zda-li si typově odpovídají spojované sloupce v predikátu (jinak se musí v každém řádku provádět konverze, což má nemalou režii). Nad sloupcem primárního klíče se vždy vytváří unikátní index. Některé databáze ještě automaticky vytváří index i nad sloupci s cizími klíči. Indexy zásadně urychlují spojení tabulek. To nezůstalo bez povšimnutí programátorů. Výsledkem je fáma, že efektivita spojení závisí na referenční integritě. Nezávisí. Závisí na indexech. Indexy na cizím klíči automaticky vytváří např. Firebird (naopak PostgreSQL je nevytváří).

Databázový administrátor by neměl podlehnout pokušení indexovat každý sloupec dat
v databázi. Hledá se kompromis, který je platný pro určitou aplikaci, firmu, dobu.
Platí totiž, že každý index způsobí zpomalení operací INSERT, UPDATE, DELETE. Kromě
vlastní změny dat se ještě musí aktualizovat všechny indexy nad tabulkou. Čím jich
je více, tím to déle trvá.

Pozn.: Ještě dlouho, co už to dávno nebyla pravda, se tradovalo, že JOIN není
přenositelný. RDBMS Oracle totiž, kromě JOINu, nabídla a nabízí vlastní zápis pro
OUTER JOIN. Nicméně jak INNER, tak OUTER JOIN je (už skoro deset let) součástí
standardu a všechny významné SQL RDBMS obsahují podporu této klauzule.

==OUTER JOIN (vnější spojení)==
Představte si databázi, kde máte tabulku zaměstnanců a tabulku obsahující evidenci
pracovní neschopnosti zaměstnanců. Pokud Vás zajímá, který zaměstnanec byl v pracovní
neschopnosti, tak použijete INNER JOIN. Ten zobrazuje spárované záznamy. Mám záznam
jak v tabulce zaměstnanců, tak v tabulce pracovní neschopnosti. Pokud by Vás zajímali
všichni zaměstnanci nebo jen ti, kteří byli stále zdraví (tudíž nemají záznam v tabulce
pracovní neschopnosti), INNER JOIN Vám nepomůže. Prostě Vám záznamy těchto zaměstnanců
nezobrazí. Příklad: Chceme zobrazit všechny zaměstnance a jejich počet hodin v
pracovní neschopnosti:
<pre>
Zamestanci(id, jmeno, prijmeni)
PracovniNeschopnosti(id, zam_id, den, pocet)

-- jednoduchá varianta, zobrazí záznamy pro ty, kteří mají záznam
-- v tabulce PracovniNeschopnosti
SELECT jmeno, prijmeni, pocet
FROM Zamestnanci z
JOIN (SELECT zam_id, sum(pocet) AS pocet
FROM PracovniNeschopnosti
GROUP BY zam_id) s
ON z.id = s.zam_id;
</pre>
Tuto úlohu řeší OUTER JOIN. Jednoduše doplní výsledek tak, aby obsahovala celou
tabulku uvedenou nalevo od klíčového slova JOIN (LEFT JOIN) nebo napravo (RIGHT JOIN).
Nově přidané řádky zákonitě musí někde obsahovat jakousi vatu. Například Zaměstnanec,
který nikdy nebyl nemocný, nemůže mít přiřazenou hodnotu sum(pocet). Tou vatou je
hodnota NULL. Takže v další iteraci by náš dotaz vypadal takto:
<pre>
SELECT jmeno, prijmeni, pocet
FROM Zamestnanci z
LEFT JOIN (SELECT zam_id, sum(pocet) AS pocet
FROM PracovniNeschopnosti
GROUP BY zam_id) s
ON z.id = s.zam_id;
</pre>
Kdybyste si dali tu práci a příklad přepsali a vyzkoušeli, tak zjistíte, že to ještě
není ono. U trvale zdravých zaměstnanců se nezobrazuje nula, ale prázdný řetězec
neboli NULL. To už je drobnost. Použijeme funkci COALESCE, která slouží k náhradě
NULL za libovolnou jinou hodnotu.
<pre>
SELECT jmeno, prijmeni, coalesce(pocet, 0)
FROM Zamestnanci z
LEFT JOIN (SELECT zam_id, sum(pocet) AS pocet
FROM PracovniNeschopnosti
GROUP BY zam_id) s
ON z.id = s.zam_id;
</pre>
Je důležité mít na paměti, že OUTER JOIN je podstatně náročnější na provádění než
INNER JOIN, a tudíž jej používat jen tehdy, když je třeba. OUTER JOIN zapsaný bez
klauzule JOIN může mít tuto podobu:
<pre>
SELECT jmeno, prijmeni, 0 -- vždy zdraví zaměstnanci
FROM Zamestnanci
WHERE id NOT IN (SELECT zam_id
FROM PracovniNeschopnosti)
UNION ALL
SELECT jmeno, prijmeni, pocet -- zaměstnanci se záznamem
FROM Zamestnanci z,
(SELECT zam_id, sum(pocet) AS pocet
FROM PracovniNeschopnosti
GROUP BY zam_id) s
WHERE z.id = s.zam_id;
</pre>
Často se OUTER JOIN používá pro vyhledání něčeho co někde není v kombinaci s operátorem
IS NULL. Výpis všech vždy zdravých zaměstnanců lze provést dotazem:
<pre>
SELECT jmeno, prijmeni, pocet
FROM Zamestnanci z
LEFT JOIN
PracovniNeschopnosti p
ON z.id = p.zam_id
WHERE pocet IS NULL;
</pre>
Tento dotaz lze také přepsat s použitím poddotazu (případně korelovaného poddotazu).
Opět záleží na aplikaci a datech, která varianta bude nejefektivnější:
<pre>
-- klasický dotaz
SELECT jmeno, prijmeni
FROM Zamestnanci
WHERE id NOT IN (SELECT zam_id
FROM PracovniNeschopnosti);

SELECT jmeno, primeni
FROM Zamestnanci
WHERE id <> ALL (SELECT zam_id
FROM PracovniNeschopnosti);

-- korelovaný poddotaz (pouze pro úplnost, až na výjimky nebude tím optimálním)
SELECT jmeno, prijmeni
FROM Zamestnanci
WHERE NOT EXISTS (SELECT id
FROM PracovniNeschopnosti
WHERE zam_id = Zamestnanci.id);
</pre>
Pozn.: V některých databázích je zjevná asymetrie
v rychlosti zpracování LEFT OUTER JOINu a RIGHT OUTER JOINu (výhodnější je používat
LEFT JOIN). Některé databáze RIGHT JOIN nemají implementován (SQLite). Jiné databáze
tyto problémy nemají (PostgreSQL).

Několik poznámek na konec. Přesun GROUP BY do derivované tabulky je jedním z triků,
které urychlují provádění dotazů. Platí, že čím víc sloupců klauzule GROUP BY obsahuje, tím je její zpracování pomalejší a vyžaduje více paměti. V příkladu navíc
dochází ke spojení skutečné a derivované (odvozené) tabulky. To ve starších před SQL
databázích nešlo. Následující příklad demonstruje právě tuto vlastnost SQL. Spojují
se dvě tabulky na základě složeného predikátu. Jedná se o "klasický" příklad zobrazení
zaměstnanců s nejvyšší mzdou v rámci oddělení. Tento vzor se opakuje v řadě SQL
úloh.
<pre>
Zamestanci(id, oddeleni_id, jmeno, prijmeni, mzda)
Oddeleni(id, nazev)

-- složený predikát
SELECT jmeno, prijmeni, mzda, nazev
FROM Zamestnanci z
JOIN (SELECT oddeleni_id, max(mzda) AS max_mzda
FROM Zemestnanci
GROUP BY oddeleni_id) s
ON z.oddeleni_id = s.oddeleni_id AND z.mzda = s.max_mzda
JOIN
Oddeleni o
ON o.id = z.oddeleni_id;
</pre>
nebo
Jelikož se JOIN použil pouze k filtrování záznamů, lze předchozí dotaz převést
do tvaru s poddotazem:
<pre>
-- jednoduchý predikát s binárním operátorem rovnost řádků
SELECT jmeno, prijmeni, mzda, nazev
FROM Zamestnanci z
JOIN
Oddeleni o
ON o.id = z.oddeleni_id
WHERE (z.oddeleni_id, z.mzda) = (SELECT oddeleni_id, max(mzda)
FROM Zamestnanci
GROUP BY oddeleni_id);
</pre>
Konečně další možností je použití korelovaného poddotazu. Výsledky obou výše uvedených
dotazů jsou stejné. Odlišný je však jejich prováděcí plán, tudíž i doba zpracování.
A opět záleží na skutečných datech, která varianta bude optimální. Volba optimální
varianty je jedním z mála způsobů jak ovlivnit zpracování SQL příkazu. Svou roli tu
hraje i použitá databáze. To co je optimální pro MySQL, nemusí být optimální pro
PostgreSQL nebo Firebird (a naopak). A aby to bylo ještě o něco komplikovanější,
záleží i na konfiguraci serveru (na velikosti přidělené paměti).

==FULL JOIN and CROSS JOIN==
FULL JOIN ensures so all rows from both related relations are included in result of join operation. A usage of this variant of JOIN is not often. CROSS JOIN is inner JOIN where any rows from one table is joined with any row from second table. This form of JOIN was used in old times for some very special tasks (row numbering). These old patterns are not readable and fast usually - SQL has a better tools now (in almost all cases - window analytic functions).
<pre>
Staff CROSS JOIN Departments ~ Staff JOIN Departments ON true
</pre>

==SELF JOIN==
Jako SELF JOIN se označuje obyčejně INNER JOIN, který má na obou stranách tutéž
tabulku. Je to technika, která se používala k obejití chybějící funkcionality v SQL
(takovou typickou úlohou, kterou v SQL92 jednoduše nevyřešíte, je výpočet klouzavých průměrů).
Výhodou řešení založených na SELF JOINech je přenositelnost, nevýhodou rychlost.
Moderní databáze podporují tzv. analytické dotazy, které umožňují efektivně řešit
úlohy, které se řešily SELF JOINem. Pokud nejsou k dispozici analytické dotazy,
pak se obvykle vyplatí použít uložené procedury. Náročnost
na zpracování není možná ani tou hlavní nevýhodou. Jedná se o absolutní alchymii
jakoby opsanou z testů Mensy. Zdaleka nejvíc se s touto technikou setkáte v SQL
kvízech Joe Celka (asi jediná osoba na planetě, která tyto věci dokáže vymyslet).

Rozšířenou ukázkou je číslování řádků ve výpisu. Předpokládejme, že id zaměstnanců
netvoří souvislou řadu, nicméně požadujeme, aby každý řádek měl své pořadové číslo
(Ve starších verzích SQL bylo striktně dodržováno pravidlo, že o zobrazení se stará
aplikace. Tudíž i o číslování řádků).
<pre>
SELECT count(*), z1.jmeno, z1.prijmeni
FROM Zamestnanci z1
JOIN
Zamestnanci z2
ON z1.id >= z2.id
GROUP BY z1.jmeno, z1.prijmeni;
</pre>
==Suquery, correlated subquery , derivated tables==
SELECT statement can includes other (nested) SELECT statements. When nested SELECT is used in FROM clause,
then we call it as "derivated table". For other usage we use a "subquery" term. Nested SELECT can has a link
(alias) to outer query - then we use a term "correlated subquery".
<pre>
-- show all employees from Administration department
SELECT *
FROM Staff
WHERE dep_id = (SELECT id -- subquery
FROM Departments
WHERE name = 'Administration');

-- returns a employees with biggest salary from each department
-- with using correlated subquery
SELECT *
FROM Staff o - - alias
WHERE salary = (SELECT max(salary)
FROM Staff
WHERE dep_id = o.dep_id); -- correlated subquery

-- returns a employees with biggest salary from each department
-- with using derivated table
SELECT s.*
FROM Staff s
JOIN (SELECT dep_id, max(salary) AS max_salary,
FROM Staff
GROUP BY dep_id) d
ON s.dep_id = d.dep_id AND s.salary = d.max_salary;
</pre>
In almost all cases SELECT with correlated subqueries will be slower, but there are a few tasks
where correlated subqueries are necessary (depends on database system and supported features. Windows
analytical functions or derivated tables can replace correlated subqueries usually).

Queries with JOINs are simply. It is not hard work. There are not reason why use a some sophisticated
designers or libraries usually. So use it.

LEFT INNER JOIN (en) - Historie editací

imported>Pavel: /* INNER JOIN (vnitřní spojení) */