Tipuri de operații Join în limbajul SQL de interogarea datelor

Mai devreme am văzut un exemplu de rezultat de join în care unele rânduri conțineau nume de persoane, dar nicio informație de contact – arătând în schimb un semn de întrebare în acea din urmă coloană. Dacă ar fi fost folosită sintaxa de bază a operației JOIN, acele rânduri (cu semn de întrebare) ar fi fost filtrate. Aceasta (sintaxă de bază cu rezultat exclusiv) este cunoscută sub numele de join INTER. Există, de asemenea, trei tipuri diferite de join OUTER. Rezultatele unei join OUTER vor conține nu numai toate rândurile de date complete pe care le-ar avea rezultatele unei join INNER, ci și rânduri de date parțiale, adică acelea în care nu s-au găsit date în unul sau ambele tabele stocate; astfel, se numesc join LEFT OUTER, RIGHT OUTER și FULL OUTER.

Deci, putem extinde sintaxa de bază JOIN la cele patru opțiuni:

[INNER] JOIN
LEFT [OUTER] JOIN
RIGHT [OUTER] JOIN
FULL [OUTER] JOIN

Cuvintele cheie înconjurate de [ ] sunt opționale. Analizorul deduce OUTER din LEFT, RIGHT sau FULL, iar un simplu JOIN (adică, sintaxă de bază) este implicit la INNER.

Inner Join

Inner Join este probabil cea mai frecvent utilizată dintre cele patru tipuri. După cum am văzut, rezultă exact acele rânduri care se potrivesc exact cu criteriul după ON. Mai jos este un exemplu care arată cum să creați o listă de persoane și contactele acestora.

-- O listă de persoane și contactele acestora
SELECT p.firstname, p.lastname, c.contact_type, c.contact_value
FROM   person p
JOIN   contact c ON p.id = c.person_id  -- sens identic: INNER JOIN ...
ORDER BY p.lastname, p.firstname, c.contact_type DESC, c.contact_value;

Ceea ce este cel mai semnificativ este că înregistrările pentru persoanele fără nicio informație de contact nu fac parte din rezultat.

Left (outer) Join

Uneori avem nevoie de puțin mai mult; de exemplu, am putea dori o listă cu toate înregistrările persoanelor, care să includă orice înregistrări cu informații de contact care ar putea fi disponibile și pentru persoana respectivă. Rețineți că acest lucru diferă de exemplul de mai sus: de data aceasta, rezultatele vor conține toate înregistrările persoanelor, chiar și cele pentru persoanele care nu au înregistrări cu informații de contact.

-- O listă cu TOATE persoanele plus contactele acestora
SELECT    p.firstname, p.lastname, c.contact_type, c.contact_value
FROM      person p
LEFT JOIN contact c ON p.id = c.person_id  -- sens identic: LEFT OUTER JOIN ...
ORDER BY  p.lastname, p.firstname, c.contact_type DESC, c.contact_value;

În acele cazuri în care informațiile de contact nu sunt disponibile, SGBD-ul le va înlocui cu „null value” sau cu „null special marker” (a nu se confunda cu string (-tip) „null value” sau „null”, nici cu binarul 0. Cu toate acestea, detaliile de implementare nu sunt importante aici. Markerul special null va fi discutat într-un capitol ulterior).

În rezumat, left (outer) join este o inner join, plus un rând pentru fiecare potrivire din partea stângă fără o contrapartidă pe partea dreaptă.

Luați în considerare cuvântul „left”. Se referă la partea stângă a formulei, „FROM <tabel_1> LEFT JOIN <tabel_2>”, sau mai precis, tabelul notat în partea stângă (aici: table_1); indicând că fiecare rând al acelui tabel va fi reprezentat cel puțin o dată în rezultat, indiferent dacă o înregistrare corespunzătoare se găsește în tabelul din dreapta (aici: table_2) sau nu.

Alt exemplu:

SELECT    p.firstname, p.lastname, c.contact_type, c.contact_value
FROM      contact c
LEFT JOIN person p  ON p.id = c.person_id  -- sens identic: LEFT OUTER JOIN ...
ORDER BY  p.lastname, p.firstname, c.contact_type DESC, c.contact_value;

Care este diferența? Am schimbat ordinea numelor tabelelor. Rețineți că încă folosim o LEFT join, dar deoarece contactul este acum referentul „left” (obiectul din clauza FROM), datele de contact vor fi acum considerate ca fiind de importanță primordială; prin urmare, toate rândurile de contact vor apărea în rezultat – împreună cu orice informații corespunzătoare care ar putea exista în tabelul de persoane. După cum se întâmplă, în baza de date pe care o folosim, fiecare înregistrare de contact corespunde unei înregistrări de persoană, așa că, în acest caz, rezultă că rezultatele sunt echivalente cu ceea ce ar fi fost dacă am fi folosit o inner join. Cu toate acestea, ele sunt diferite de cele din exemplul anterior de alăturare stânga.

Right (outer) Join

Right (outer) Join respectă aceleași reguli ca și left join, dar invers. Acum, fiecare înregistrare din tabelul la care se face referire în clauza join va apărea în rezultat, inclusiv cele care nu au nicio înregistrare corespunzătoare în celălalt tabel. Din nou, DBMS furnizează fiecarei celule goale din coloana din dreapta cu marcatorul special nul. Singura diferență este că succesiunea de evaluare a tabelelor se realizează invers sau, cu alte cuvinte, cu rolurile celor două tabele schimbate.

-- O listă cu TOATE înregistrările de contact cu orice date corespunzătoare despre persoană, chiar dacă s
SELECT     p.firstname, p.lastname, c.contact_type, c.contact_value
FROM       person p
RIGHT JOIN contact c ON p.id = c.person_id  -- la fel ca RIGHT OUTER JOIN ...
ORDER BY   p.lastname, p.firstname, c.contact_type DESC, c.contact_value;

Full (outer) Join

Full (outer) Join preia fiecare rând atât din tabelul din stânga, cât și din tabelul din dreapta, indiferent dacă există o înregistrare corespunzătoare în tabelul opus respectiv.

SELECT    p.firstname, p.lastname, c.contact_type, c.contact_value
FROM      person p
FULL JOIN contact c ON p.id = c.person_id  -- sens identic: FULL OUTER JOIN ...
ORDER BY  p.lastname, p.firstname, c.contact_type DESC, c.contact_value;

Fiind date table_1 and table_2 de mai jos,

table_1

ID	X
1	11
2	12
3	13

table_2

ID	TABLE_1_ID	Y
1	1	21
2	5	22

full join:

SELECT    *
FROM      table_1 t1
FULL JOIN table_2 t2 ON t1.id = t2.table_1_id;

va da:

T1.ID	T1.X	T2.ID	T2.TABLE_1_ID	T2.Y
1	11	1	1	21
2	12	null	null	null
3	13	null	null	null
null	null	2	5	22

Aceste rezultate conțin rândul (singur) potrivit, plus câte un rând pentru toate celelalte înregistrări ale ambelor tabele originale. Deoarece fiecare dintre aceste alte rânduri reprezintă date găsite doar într-unul dintre tabele, fiecăruia le lipsesc unele date, astfel încât celulele reprezentative pentru acele date lipsă conțin markerul special nul.

Notă: Full join nu este acceptată de toate SGBD. Cu toate acestea, deoarece nu este o operație atomică, este întotdeauna posibil să se creeze rezultatul dorit printr-o combinație de mai multe SELECT-uri cu operații SET.

(Include texte din Wikibooks traduse și adaptate de Nicolae Sfetcu)