Clauza JOIN
In urma procesului de normalizare rezulta baze de date care contin tabele aflate in legatura. Din acest motiv, colectarea datelor dintr-un singur tabel nu este suficienta. Pentru a extrage date complete din tabelele unei baze de date, in instructiunea SELECT este utilizata clauza JOIN.
Prin intermediul clauzei JOIN, instructiunea SELECT permite regasirea si vizualizarea datelor din tabele intre care sunt stabilite legaturi. Forma simplificata a sintaxei instructiunii SELECT, in cazul utilizarii clauzei JOIN, este urmatoarea:
SELECT [ALL | DISTINCT] expresie_select [, expresie_select ...] FROM referinta_tabel [INNER] JOIN referinta_tabel [conditie_join] | referinta_tabel {LEFT|RIGHT} [OUTER] JOIN referinta_tabel conditie_join [WHERE [conditie_join AND] conditie_selectie] [GROUP BY {nume_coloana | expresie | pozitie_coloana} [ASC | DESC], ...] [HAVING conditie_grupare] [ORDER BY {nume_coloana | expresie | pozitie_coloana} [ASC | DESC], ...] [LIMIT {[pozitie,] numar_inregistrari | numar_inregistrari OFFSET pozitie}]
In acest material sunt avute in vedere doua tiputi de operatii de tip JOIN: INNER JOIN, respectiv OUTER JOIN. Operatia de tip INNER JOIN, denumita si jonctiune interna, include in rezultat doar inregistrarile care au corespondenta in toate tabele.
In sintaxa precedenta pot fi observate si cele doua tipuri de OUTER JOIN, jonctiuni externe care pot fi utilizate la colectarea datelor din mai multe tabele in cadrul sistemului MySQL: LEFT OUTER JOIN, respectiv RIGHT OUTER JOIN.
Operatia de tip LEFT OUTER JOIN include in rezultat si inregistrarile din tabelul din partea stanga care nu au corespondent in tabelul din partea dreapta a legaturii. Operatia de tip RIGHT OUTER JOIN include in rezultat si inregistrarile din tabelul din partea dreapta care nu au corespondent in tabelul din partea stanga a legaturii.
Jonctiuni interne – INNER JOIN
O prima modalitate de implementare a unei operatii de tip INNER JOIN presupune specificarea in clauza FROM a tabelelor din care se preiau datele si a coloanelor care fac parte din rezultat, in lista de selectie. Pentru inregistrarile de la nivelul tabelului film, din care sunt expuse coloanele title si release_year, se doreste atasarea coloanei name de la nivelul tabelului language. Ambele tabele sunt disponibile la nivelul bazei de date sakila. In acest fel, rezultatul va oferi pentru fiecare productie (film) informatii cu privire la titlu, anul lansarii si limba in care a fost realizata pelicula.
mysql> USE sakila Database changed mysql> SELECT film.title, film.release_year, language.name -> FROM film, language -> WHERE film.language_id = language.language_id -> LIMIT 5; +------------------+--------------+---------+ | title | release_year | name | +------------------+--------------+---------+ | ACADEMY DINOSAUR | 2006 | English | | ACE GOLDFINGER | 2006 | English | | ADAPTATION HOLES | 2006 | English | | AFFAIR PREJUDICE | 2006 | English | | AFRICAN EGG | 2006 | English | +------------------+--------------+---------+
In lista de selectie sunt specificate coloanele care fac parte din rezultat. Acestea pot apartine unuia din tabelele indicate in referinte tabele. Daca exista coloane in rezultat care au acelasi nume, ele trebuie prefixate cu numele tabelului din care provin pentru a evita coleziunile. In general, se prefera utilizarea alias-urilor pentru a simplifica sintaxa.
mysql> SELECT f.film_id, f.title, f.release_year, f.language_id, l.language_id, l.name -> FROM film AS f, language AS l -> WHERE f.language_id = l.language_id -> LIMIT 5; +---------+------------------+--------------+-------------+-------------+---------+ | film_id | title | release_year | language_id | language_id | name | +---------+------------------+--------------+-------------+-------------+---------+ | 1 | ACADEMY DINOSAUR | 2006 | 1 | 1 | English | | 2 | ACE GOLDFINGER | 2006 | 1 | 1 | English | | 3 | ADAPTATION HOLES | 2006 | 1 | 1 | English | | 4 | AFFAIR PREJUDICE | 2006 | 1 | 1 | English | | 5 | AFRICAN EGG | 2006 | 1 | 1 | English | +---------+------------------+--------------+-------------+-------------+---------+
Referintele la tabele indica structurile din care sunt preluate datele. Unele din tabelele precizate in referite pot stabili doar legatura intre tabele, neavand nici o coloana specificata in rezultat.
Pentru operatiile de tip INNER JOIN, legatura intre tabele poate fi realizata prin intermediul unei expresii logice care poate fi precizata inclusiv la nivelul clauzei WHERE. In general, expresia din conditia de legatura are urmatoarea forma: tabel1.cheie_externa = tabel2.cheie_primara. In cazul precedent, conditia de legatura este realizata prin intermediul atributelor denumite language_id, de la nivelul tabelelor film, respectiv language.
Instructiunea SELECT de mai sus poate fi rescrisa, astfel incat sa utilizeze clauza JOIN pentru a specifica explicit tipul de JOIN (INNER JOIN).
mysql> SELECT f.film_id, f.title, f.release_year, f.language_id, l.language_id, l.name -> FROM film AS f INNER JOIN language AS l -> WHERE f.language_id = l.language_id -> LIMIT 5; +---------+------------------+--------------+-------------+-------------+---------+ | film_id | title | release_year | language_id | language_id | name | +---------+------------------+--------------+-------------+-------------+---------+ | 1 | ACADEMY DINOSAUR | 2006 | 1 | 1 | English | | 2 | ACE GOLDFINGER | 2006 | 1 | 1 | English | | 3 | ADAPTATION HOLES | 2006 | 1 | 1 | English | | 4 | AFFAIR PREJUDICE | 2006 | 1 | 1 | English | | 5 | AFRICAN EGG | 2006 | 1 | 1 | English | +---------+------------------+--------------+-------------+-------------+---------+
Legatura intre tabele poate fi stabilita si prin intermediul clauzelor din conditia de join: ON si USING. Clauza USING este utilizata in cazul in care legatura intre doua tabele este realizata prin coloane care au acelasi nume in ambele tabele.
mysql> SELECT f.film_id, f.title, f.release_year, f.language_id, l.language_id, l.name -> FROM film AS f INNER JOIN language AS l -> USING(language_id) -> LIMIT 5; +---------+------------------+--------------+-------------+-------------+---------+ | film_id | title | release_year | language_id | language_id | name | +---------+------------------+--------------+-------------+-------------+---------+ | 1 | ACADEMY DINOSAUR | 2006 | 1 | 1 | English | | 2 | ACE GOLDFINGER | 2006 | 1 | 1 | English | | 3 | ADAPTATION HOLES | 2006 | 1 | 1 | English | | 4 | AFFAIR PREJUDICE | 2006 | 1 | 1 | English | | 5 | AFRICAN EGG | 2006 | 1 | 1 | English | +---------+------------------+--------------+-------------+-------------+---------+
Clauza ON este mult mai explicita, precizand o expresie logica in care sunt indicate coloanele care stabilesc legatura, astfel: tabel1.chaie_externa = tabel2.cheie_primara.
mysql> SELECT f.film_id, f.title, f.release_year, f.language_id, l.language_id, l.name -> FROM film AS f INNER JOIN language AS l -> ON f.language_id = l.language_id -> LIMIT 5; +---------+------------------+--------------+-------------+-------------+---------+ | film_id | title | release_year | language_id | language_id | name | +---------+------------------+--------------+-------------+-------------+---------+ | 1 | ACADEMY DINOSAUR | 2006 | 1 | 1 | English | | 2 | ACE GOLDFINGER | 2006 | 1 | 1 | English | | 3 | ADAPTATION HOLES | 2006 | 1 | 1 | English | | 4 | AFFAIR PREJUDICE | 2006 | 1 | 1 | English | | 5 | AFRICAN EGG | 2006 | 1 | 1 | English | +---------+------------------+--------------+-------------+-------------+---------+
Jonctiuni externe – OUTER JOIN
Operatia de tip OUTER JOIN, numita si jonctiune externa, include in rezultat toate inregistrarile, indiferent daca acestea au sau nu corespondent in toate tabelele. La nivelul sistemului MySQL sunt disponibile doua implementari ale acestei operatii: LEFT OUTER JOIN, respectiv RIGHT OUTER JOIN.
Am introdus doua inregistrari la nivelul tabelului actor, inregistrari care nu au corespondenta la nivelul tabelelor film_actor si film. De asemenea, la nivelul tabelului film am inserat o noua inregistrare pentru care nu am precizat pentru moment distributia (legaturi cu inregistrari de la nivelul tabelului actor, prin intermediul tabelului de lagatura film_actor).
Daca avem in vedere obtinerea ultimilor actori inregistrati la nivelul tabelului actor indiferent daca acestia au sau nu precizate productiile in care au aparut, putem implementa operatii de tip LEFT OUTER JOIN pentru tabelele actor, film_actor si film.
mysql> SELECT a.first_name, a.last_name, f.title -> FROM actor a LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id -> LEFT JOIN film f ON fa.film_id = f.film_id -> ORDER BY a.last_update DESC -> LIMIT 5; +------------+-----------+---------------------+ | first_name | last_name | title | +------------+-----------+---------------------+ | DAKOTA | WILLIS | NULL | | BRUCE | JOHNSON | NULL | | MORGAN | WILLIAMS | AGENT TRUMAN | | MORGAN | WILLIAMS | ALICE FANTASIA | | MORGAN | WILLIAMS | BAREFOOT MANCHURIAN | +------------+-----------+---------------------+
Putem chiar izola de la nivelul rezultatului precedent, rezultat obtinut prin intermediul unei operatii de tip LEFT OUTER JOIN, doar inregistrarile care corespund actorilor pentru care nu am precizat titlul unui film. In acest caz, vorbim de implementarea unui LEFT OUTER JOIN fara corespondenta.
mysql> SELECT a.first_name, a.last_name, f.title -> FROM actor a LEFT JOIN film_actor fa ON a.actor_id = fa.actor_id -> LEFT JOIN film f ON fa.film_id = f.film_id -> WHERE f.film_id IS NULL; +------------+-----------+-------+ | first_name | last_name | title | +------------+-----------+-------+ | BRUCE | JOHNSON | NULL | | DAKOTA | WILLIS | NULL | +------------+-----------+-------+
Daca in implementarea precedenta avem in vedere modificarea tipului jonctiunii din LEFT JOIN in RIGHT JOIN si filtrarea rezultatelor prin intermediul unei conditii a.actor_id IS NULL, vom obtine in rezultat si inregistrarea corespunzatoare filmului pentru care nu am precizat nici o asociere cu inregistrari de la nivelul tabelului actor. Putem observa faptul ca la nivelul rezultatului mai sunt prezente si alte filme, pentru care nu a fost stabilita distributia (nu a fost precizat cel putin un actor).
mysql> SELECT a.first_name, a.last_name, f.title -> FROM actor a RIGHT JOIN film_actor fa ON a.actor_id = fa.actor_id -> RIGHT JOIN film f ON fa.film_id = f.film_id -> WHERE a.actor_id IS NULL; +------------+-----------+------------------+ | first_name | last_name | title | +------------+-----------+------------------+ | NULL | NULL | DRUMLINE CYCLONE | | NULL | NULL | FLIGHT LIES | | NULL | NULL | SLACKER LIAISONS | | NULL | NULL | THE GAME | +------------+-----------+------------------+