LEFT INNER JOIN (en)
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.
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
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)
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
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.
SELECT name, surname, age FROM Staff WHERE age >= 30;
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:
SELECT * FROM Authors, Books;
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.
SELECT title, surname, name
FROM Books
JOIN
Authors
ON Books.author_id = Authors.id
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:
SELECT title, surname, name
FROM Books
JOIN
Authors
ON Books.author_id = Authors.id
WHERE surname = 'Capek'
AND name = 'Jan'
There are two predicates in two clauses. Older notation used only WHERE clause for predicates:
SELECT title, surname, name
FROM Books, Authors
WHERE Books.author_id = Authors.id
AND surname = 'Capek'
AND name = 'Jan'
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:
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;
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:
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;
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.
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;
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:
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;
Č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:
SELECT jmeno, prijmeni, pocet
FROM Zamestnanci z
LEFT JOIN
PracovniNeschopnosti p
ON z.id = p.zam_id
WHERE pocet IS NULL;
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ší:
-- 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);
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.
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;
nebo Jelikož se JOIN použil pouze k filtrování záznamů, lze předchozí dotaz převést do tvaru s poddotazem:
-- 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);
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).
Staff CROSS JOIN Departments ~ Staff JOIN Departments ON true
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ů).
SELECT count(*), z1.jmeno, z1.prijmeni
FROM Zamestnanci z1
JOIN
Zamestnanci z2
ON z1.id >= z2.id
GROUP BY z1.jmeno, z1.prijmeni;
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".
-- 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;
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.