Alăturați-vă (SQL) - Join (SQL)

O clauză de asociere în SQL - corespunzătoare unei operații de asociere în algebră relațională - combină coloane dintr-una sau mai multe tabele într-un tabel nou. ANSI apartament standard SQL Specifică cinci tipuri de JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTERși CROSS.

Exemple de tabele

Pentru a explica tipurile de asociere, restul acestui articol folosește următoarele tabele:

Department.DepartmentIDeste cheia primară a Departmenttabelului, în timp ce Employee.DepartmentIDeste o cheie străină .

Rețineți că în Employee„Williams” nu a fost încredințat încă unui departament. De asemenea, niciun angajat nu a fost repartizat la departamentul „Marketing”.

Aceasta este declarația SQL pentru a crea tabelele de mai sus:

CREATE TABLE department(
    DepartmentID INT PRIMARY KEY NOT NULL,
    DepartmentName VARCHAR(20)
);

CREATE TABLE employee (
    LastName VARCHAR(20),
    DepartmentID INT REFERENCES department(DepartmentID)
);

INSERT INTO department
VALUES (31, 'Sales'),
       (33, 'Engineering'),
       (34, 'Clerical'),
       (35, 'Marketing');

INSERT INTO employee
VALUES ('Rafferty', 31),
       ('Jones', 33),
       ('Heisenberg', 33),
       ('Robinson', 34),
       ('Smith', 34),
       ('Williams', NULL);

Alăturați-vă încrucișat

CROSS JOINreturnează produsul cartezian al rândurilor din tabelele din îmbinare. Cu alte cuvinte, va produce rânduri care combină fiecare rând din primul tabel cu fiecare rând din al doilea tabel.

Exemplu de unire transversală explicită:

SELECT *
FROM employee CROSS JOIN department;

Exemplu de îmbinare încrucișată implicită:

SELECT *
FROM employee, department;

Îmbinarea încrucișată poate fi înlocuită cu o îmbinare interioară cu o condiție întotdeauna adevărată:

SELECT *
FROM employee INNER JOIN department ON 1=1;

CROSS JOINnu aplică el însuși niciun predicat pentru a filtra rândurile din tabelul alăturat. Rezultatele unui CROSS JOINpot fi filtrate folosind o WHEREclauză, care poate produce apoi echivalentul unei îmbinări interioare.

În standardul SQL: 2011 , îmbinările încrucișate fac parte din pachetul F401 opțional, „Tabel îmbinat extins”.

Utilizările normale sunt pentru verificarea performanței serverului.

Alăturare interioară

O îmbinare interioară necesită ca fiecare rând din cele două tabele îmbinate să aibă valori de coloană potrivite și este o operație de îmbinare utilizată în mod obișnuit în aplicații, dar nu ar trebui să se presupună că este cea mai bună alegere în toate situațiile. Unirea interioară creează un nou tabel de rezultate prin combinarea valorilor coloanei a două tabele (A și B) bazate pe predicatul de unire. Interogarea compară fiecare rând al lui A cu fiecare rând al lui B pentru a găsi toate perechile de rânduri care îndeplinesc predicatul de unire. Când unirea-predicat este satisfăcută prin potrivirea valorilor care nu sunt NULL , valorile coloanei pentru fiecare pereche de rânduri potrivite de A și B sunt combinate într-un rând de rezultate.

Rezultatul îmbinării poate fi definit ca rezultatul primului produs cartezian (sau Îmbinare încrucișată ) a tuturor rândurilor din tabele (combinând fiecare rând din tabelul A cu fiecare rând din tabelul B) și apoi returnând toate rândurile care satisfac alăturați predicatului. Implementările SQL actuale utilizează în mod normal alte abordări, cum ar fi îmbinările hash sau sort-merge , deoarece calculul produsului cartezian este mai lent și ar necesita adesea o cantitate de memorie prohibitiv de mare pentru a fi stocată.

SQL specifică două moduri sintactice diferite de exprimare a îmbinărilor: „notația explicită de îmbinare” și „notația de îmbinare implicită”. „Notarea implicită de asociere” nu mai este considerată o bună practică, deși sistemele de baze de date încă o acceptă.

„Notarea explicită de asociere” folosește JOINcuvântul cheie, opțional precedat de INNERcuvântul cheie, pentru a specifica tabelul de asociat și ONcuvântul cheie pentru a specifica predicatele pentru asociere, ca în exemplul următor:

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM employee 
INNER JOIN department ON
employee.DepartmentID = department.DepartmentID;

„Notarea implicită de asociere” listează pur și simplu tabelele de asociere, în FROMclauza SELECTdeclarației, folosind virgule pentru a le separa. Astfel, specifică o îmbinare încrucișată , iar WHEREclauza poate aplica filtre-predicate suplimentare (care funcționează comparabil cu unirile-predicate în notația explicită).

Următorul exemplu este echivalent cu cel precedent, dar de data aceasta folosind notația de asociere implicită:

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName 
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

Interogările date în exemplele de mai sus se vor alătura tabelelor Angajați și Departament folosind coloana DepartamentID din ambele tabele. În cazul în care ID-ul departamentului acestor tabele se potrivește (adică predicarea de asociere este îndeplinită), interogarea va combina coloanele LastName , DepartmentID și DepartmentName din cele două tabele într-un rând de rezultate. În cazul în care ID-ul departamentului nu se potrivește, nu se generează niciun rând de rezultat.

Astfel, rezultatul executării interogării de mai sus va fi:

Angajatul „Williams” și departamentul „Marketing” nu apar în rezultatele executării interogării. Niciunul dintre acestea nu are rânduri potrivite în celălalt tabel respectiv: „Williams” nu are departament asociat și niciun angajat nu are ID-ul departamentului 35 („Marketing”). În funcție de rezultatele dorite, acest comportament poate fi un bug subtil, care poate fi evitat prin înlocuirea îmbinării interioare cu o îmbinare exterioară .

Valori de îmbinare interioară și NULL

Programatorii ar trebui să aibă grijă deosebită atunci când se unesc tabele pe coloane care pot conține valori NULL , deoarece NULL nu se va potrivi niciodată cu nicio altă valoare (nici măcar NULL în sine), cu excepția cazului în care condiția de asociere utilizează în mod explicit un predicat de combinație care verifică mai întâi dacă sunt coloanele de asociere NOT NULLînainte de a aplica restul (condițiile) predicat (e). Unirea interioară poate fi utilizată în siguranță numai într-o bază de date care impune integritatea referențială sau în cazul în care coloanele de asociere sunt garantate să nu fie NULL. Multe baze de date relaționale de procesare a tranzacțiilor se bazează pe standarde de actualizare a datelor de atomizare, consistență, izolare, durabilitate (ACID) pentru a asigura integritatea datelor, făcând îmbinările interioare o alegere adecvată. Cu toate acestea, bazele de date ale tranzacțiilor au, de obicei, și coloane de îmbinare dorite, care pot fi NULL. Multe baze de date relaționale și depozite de date utilizează actualizări de lot de extragere, transformare, încărcare (ETL) de volum mare care fac dificilă sau imposibilă aplicarea integrității referențiale, rezultând coloane de îmbinare potențial NULL pe care un autor de interogare SQL nu le poate modifica și care determină omiterea îmbinărilor interioare date fără indicarea unei erori. Alegerea utilizării unei îmbinări interioare depinde de proiectarea bazei de date și de caracteristicile datelor. O îmbinare exterioară stângă poate fi înlocuită de obicei cu o îmbinare interioară atunci când coloanele de îmbinare dintr-un singur tabel pot conține valori NULL.

Orice coloană de date care poate fi NULL (goală) nu trebuie folosită niciodată ca o legătură într-o îmbinare interioară, cu excepția cazului în care rezultatul dorit este eliminarea rândurilor cu valoarea NULL. Dacă coloanele de asociere NULL urmează să fie eliminate în mod deliberat din setul de rezultate , o asociere interioară poate fi mai rapidă decât o asociere exterioară, deoarece asocierea tabelului și filtrarea se fac într-un singur pas. În schimb, o îmbinare interioară poate avea ca rezultat o performanță dezastruos de lentă sau chiar o blocare a serverului atunci când este utilizată într-o interogare de volum mare în combinație cu funcțiile bazei de date într-o clauză SQL Where. O funcție într-o clauză SQL Where poate duce la ignorarea bazei de date a indexurilor de tabel relativ compacte. Baza de date poate citi și uni interior coloanele selectate din ambele tabele înainte de a reduce numărul de rânduri folosind filtrul care depinde de o valoare calculată, rezultând o cantitate relativ mare de procesare ineficientă.

Când un set de rezultate este produs prin alăturarea mai multor tabele, inclusiv tabele master utilizate pentru a căuta descrieri cu text integral ale codurilor numerice de identificare (un tabel de căutare ), o valoare NULL în oricare dintre tastele străine poate duce la eliminarea întregului rând din setul de rezultate, fără indicații de eroare. O interogare SQL complexă care include una sau mai multe îmbinări interioare și mai multe îmbinări exterioare are același risc pentru valorile NULL din coloanele de legătură de îmbinare interioară.

Un angajament față de codul SQL care conține îmbinări interioare presupune că coloanele de asociere NULL nu vor fi introduse de viitoarele modificări, inclusiv actualizări ale furnizorilor, modificări de proiectare și procesare în bloc în afara regulilor de validare a datelor ale aplicației, cum ar fi conversiile de date, migrațiile, importurile în bloc și îmbinările.

Se pot clasifica în continuare îmbinările interioare ca echi-îmbinări, ca îmbinări naturale sau ca îmbinări încrucișate.

Equi-join

Un echi-join este un tip specific de join bazat pe comparator, care folosește doar comparații de egalitate în predicatul join. Utilizarea altor operatori de comparație (cum ar fi <) descalifică o asociere ca o asociere echi. Interogarea de mai sus a furnizat deja un exemplu de echi-join:

SELECT *
FROM employee JOIN department
  ON employee.DepartmentID = department.DepartmentID;

Putem scrie echi-join ca mai jos,

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID;

Dacă coloanele dintr-o echi-join au același nume, SQL-92 oferă o notație opțională de scurtare pentru exprimarea echi-join, prin intermediul USINGconstructului:

SELECT *
FROM employee INNER JOIN department USING (DepartmentID);

USINGConstruct este mai mult decât simpla de zahăr sintactic , cu toate acestea, din moment ce rezultatul stabilit diferă de setul de rezultate al versiunii cu predicatul explicit. Mai exact, orice coloane menționate în USINGlistă vor apărea o singură dată, cu un nume necalificat, mai degrabă decât o dată pentru fiecare tabel din unire. În cazul de mai sus, va exista o singură DepartmentIDcoloană și nu employee.DepartmentIDsau department.DepartmentID.

USINGClauza nu este acceptat de MS SQL Server și Sybase.

Alăturare naturală

Unirea naturală este un caz special de echi-unire. Unirea naturală (⋈) este un operator binar care este scris ca ( R ⋈ S ) unde R și S sunt relații . Rezultatul îmbinării naturale este setul tuturor combinațiilor de tupluri din R și S care sunt egale pe numele lor comune de atribute. Pentru un exemplu, luați în considerare tabelele Angajat și Departament și îmbinarea lor naturală:

Angajat Nume EmpId DeptName Harry 3415 Finanţa Sally 2241 Vânzări George 3401 Finanţa Harriet 2202 Vânzări

Departamentul DeptName Administrator Finanţa George Vânzări Harriet Producție Charles

Departamentul angajaților${\ displaystyle \ bowtie}$  Nume EmpId DeptName Administrator Harry 3415 Finanţa George Sally 2241 Vânzări Harriet George 3401 Finanţa George Harriet 2202 Vânzări Harriet

Acest lucru poate fi folosit și pentru a defini compoziția relațiilor . De exemplu, compoziția angajaților și a departamentului este unirea lor, așa cum se arată mai sus, proiectată pentru toate, în afară de atributul comun DeptName . În teoria categoriilor , îmbinarea este tocmai produsul din fibră .

Îmbinarea naturală este, fără îndoială, unul dintre cei mai importanți operatori, deoarece este omologul relațional al AND-ului logic. Rețineți că dacă aceeași variabilă apare în fiecare dintre cele două predicate conectate prin AND, atunci acea variabilă reprezintă același lucru și ambele apariții trebuie întotdeauna înlocuite cu aceeași valoare. În special, îmbinarea naturală permite combinarea relațiilor care sunt asociate de o cheie străină . De exemplu, în exemplul de mai sus, o cheie străină probabil o deține de la angajat . DeptName la Dept . DeptName și apoi alăturarea naturală a Employee și Dept combină toți angajații cu departamentele lor. Acest lucru funcționează deoarece cheia străină deține între atributele cu același nume. Dacă nu este cazul, cum ar fi în cheia străină de la Depart . manager la Angajat . Denumiți, apoi aceste coloane trebuie redenumite înainte de a lua unirea naturală. O astfel de asociere este uneori denumită și echi-asociere .

Mai formal, semantica îmbinării naturale este definită după cum urmează:

${\ displaystyle R \ bowtie S = \ left \ {t \ cup s \ mid t \ in R \ \ land \ s \ in S \ \ land \ {\ mathit {Fun}} (t \ cup s) \ right \ }}$,

unde Fun este un predicat care este adevărat pentru o relație r dacă și numai dacă r este o funcție. De obicei este necesar ca R și S să aibă cel puțin un atribut comun, dar dacă această constrângere este omisă, iar R și S nu au atribute comune, atunci îmbinarea naturală devine exact produsul cartezian.

Îmbinarea naturală poate fi simulată cu primitivele lui Codd după cum urmează. Hai c ₁ , ..., c _m fi numele de atribute comune R și S , r ₁ , ..., r _n sunt numele atributelor unice R si lasa s ₁ , ..., s _k fie atributele unic la S . Mai mult decât atât, să presupunem că numele atributului x ₁ , ..., x _m sunt nici în R , nici în S . Într-un prim pas, numele atributelor comune din S pot fi acum redenumite:

${\ displaystyle T = \ rho _ {x_ {1} / c_ {1}, \ ldots, x_ {m} / c_ {m}} (S) = \ rho _ {x_ {1} / c_ {1}} (\ rho _ {x_ {2} / c_ {2}} (\ ldots \ rho _ {x_ {m} / c_ {m}} (S) \ ldots))}$

Apoi luăm produsul cartezian și selectăm tuplurile care urmează să fie îmbinate:

${\ displaystyle U = \ pi _ {r_ {1}, \ ldots, r_ {n}, c_ {1}, \ ldots, c_ {m}, s_ {1}, \ ldots, s_ {k}} (P )}}$

O îmbinare naturală este un tip de echi-unire în care predicatul de îmbinare apare implicit prin compararea tuturor coloanelor din ambele tabele care au aceleași nume de coloane în tabelele unite. Tabelul combinat rezultat conține o singură coloană pentru fiecare pereche de coloane denumite în mod egal. În cazul în care nu se găsesc coloane cu aceleași nume, rezultatul este o îmbinare încrucișată .

Majoritatea experților sunt de acord că NATURAL JOINs sunt periculoase și, prin urmare, descurajează puternic utilizarea acestora. Pericolul vine din adăugarea accidentală a unei coloane noi, denumită la fel ca o altă coloană din celălalt tabel. O îmbinare naturală existentă ar putea folosi apoi „în mod natural” noua coloană pentru comparații, făcând comparații / potriviri folosind criterii diferite (din coloane diferite) decât înainte. Astfel, o interogare existentă ar putea produce rezultate diferite, chiar dacă datele din tabele nu au fost modificate, ci doar mărite. Utilizarea numelor de coloane pentru a determina automat legăturile de tabelă nu este o opțiune în bazele de date mari cu sute sau mii de tabele în care ar pune o constrângere nerealistă asupra convențiilor de numire. Bazele de date din lumea reală sunt de obicei proiectate cu date cu cheie străină care nu sunt populate în mod constant (sunt permise valori NULL), datorită regulilor și contextului de afaceri. Este o practică obișnuită să se modifice numele coloanelor de date similare în diferite tabele și această lipsă de consistență rigidă relegă îmbinările naturale de un concept teoretic pentru discuție.

Exemplul de interogare de mai sus pentru îmbinări interioare poate fi exprimat ca o îmbinare naturală în modul următor:

SELECT *
FROM employee NATURAL JOIN department;

Ca și în USINGclauza explicită , în tabela alăturată apare o singură coloană DepartmentID, fără calificativ:

PostgreSQL, MySQL și Oracle acceptă îmbinări naturale; Microsoft T-SQL și IBM DB2 nu. Coloanele utilizate în îmbinare sunt implicite, astfel încât codul de îmbinare nu arată ce coloane sunt așteptate, iar o modificare a numelor de coloane poate schimba rezultatele. În standardul SQL: 2011 , îmbinările naturale fac parte din pachetul F401 opțional, „Tabel îmbinat extins”.

În multe medii de baze de date, numele coloanelor sunt controlate de un furnizor extern, nu de dezvoltatorul de interogări. O îmbinare naturală presupune stabilitate și consistență în numele coloanelor care se pot schimba în timpul actualizărilor de versiune obligate de furnizor.

Alăturare exterioară

Tabelul alăturat păstrează fiecare rând - chiar dacă nu există niciun alt rând de potrivire. Îmbinările exterioare se împart în continuare la îmbinările exterioare stânga, la îmbinările exterioare dreapta și la îmbinările externe complete, în funcție de rândurile de tabel care sunt reținute: stânga, dreapta sau ambele (în acest caz stânga și dreapta se referă la cele două laturi ale JOINcuvântului cheie). La fel ca îmbinările interioare , se pot subcategoriza în continuare toate tipurile de îmbinări exterioare ca echi-îmbinări , îmbinări naturale , ( θ- îmbinare ) etc. ON <predicate>

În SQL standard nu există nicio notare implicită de asociere pentru îmbinările exterioare.

O diagramă Venn care reprezintă declarația SQL Join Left între tabelele A și B.

Îmbinarea exterioară stângă

Rezultatul unei îmbinări exterioare la stânga (sau pur și simplu la stânga ) pentru tabelele A și B conține întotdeauna toate rândurile tabelului „stânga” (A), chiar dacă condiția de asociere nu găsește niciun rând potrivit în tabelul „dreapta” (B). Aceasta înseamnă că, dacă ONclauza se potrivește cu 0 (zero) rânduri în B (pentru un rând dat în A), îmbinarea va returna în continuare un rând în rezultat (pentru acel rând) - dar cu NULL în fiecare coloană din B. A stânga unirea exterioară returnează toate valorile dintr-o îmbinare interioară plus toate valorile din tabelul din stânga care nu se potrivesc cu tabelul din dreapta, inclusiv rândurile cu valori NULL (goale) în coloana link-ului.

De exemplu, acest lucru ne permite să găsim un departament al unui angajat, dar afișează totuși angajații care nu au fost repartizați la un departament (contrar exemplului de asociere interioară de mai sus, unde angajații neatribuiți au fost excluși din rezultat).

Exemplu de îmbinare exterioară la stânga ( OUTERcuvântul cheie este opțional), cu rândul de rezultat suplimentar (comparativ cu îmbinarea interioară) cursiv:

SELECT *
FROM employee 
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

Sintaxele alternative

Oracle acceptă sintaxa depreciată:

SELECT *
FROM employee, department
WHERE employee.DepartmentID = department.DepartmentID(+)

Sybase acceptă sintaxa ( Microsoft SQL Server a depreciat această sintaxă de la versiunea 2000):

SELECT *
FROM employee, department
WHERE employee.DepartmentID *= department.DepartmentID

IBM Informix acceptă sintaxa:

SELECT *
FROM employee, OUTER department
WHERE employee.DepartmentID = department.DepartmentID

O diagramă Venn care reprezintă instrucțiunea SQL Right Join între tabelele A și B.

Îmbinarea exterioară dreaptă

O îmbinare exterioară dreaptă (sau îmbinarea dreaptă ) seamănă foarte mult cu o îmbinare exterioară stângă, cu excepția tratamentului tabelelor inversat. Fiecare rând din tabelul „dreapta” (B) va apărea în tabelul alăturat cel puțin o dată. Dacă nu există niciun rând de potrivire din tabelul „stânga” (A), NULL va apărea în coloanele din A pentru acele rânduri care nu au nicio potrivire în B.

O îmbinare exterioară dreaptă returnează toate valorile din tabelul din dreapta și valorile potrivite din tabelul din stânga (NULL în cazul în care nu există un predicat de îmbinare asociat). De exemplu, acest lucru ne permite să găsim fiecare angajat și departamentul său, dar totuși să afișăm departamente care nu au angajați.

Mai jos este un exemplu de îmbinare exterioară dreaptă ( OUTERcuvântul cheie este opțional), cu rândul de rezultat suplimentar cursiv:

SELECT *
FROM employee RIGHT OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

Îmbinările exterioare dreapta și stânga sunt echivalente din punct de vedere funcțional. Nici una nu oferă funcționalități pe care cealaltă nu le oferă, astfel încât îmbinările exterioare dreapta și stânga se pot înlocui reciproc, atâta timp cât comutați tabelul.

O diagramă Venn care reprezintă declarația SQL Full Join între tabelele A și B.

Îmbinarea exterioară completă

Conceptual, o îmbinare exterioară completă combină efectul aplicării ambelor îmbinări exterioare stânga și dreaptă. În cazul în care rândurile din tabelele FULL OUTER JOIN nu se potrivesc, setul de rezultate va avea valori NULL pentru fiecare coloană a tabelului căruia îi lipsește un rând potrivit. Pentru acele rânduri care se potrivesc, va fi produs un singur rând în setul de rezultate (care conține coloane populate din ambele tabele).

De exemplu, acest lucru ne permite să vedem fiecare angajat care se află într-un departament și fiecare departament care are un angajat, dar, de asemenea, să vedem fiecare angajat care nu face parte dintr-un departament și fiecare departament care nu are un angajat.

Exemplu de îmbinare exterioară completă ( OUTERcuvântul cheie este opțional):

SELECT *
FROM employee FULL OUTER JOIN department
  ON employee.DepartmentID = department.DepartmentID;

Unele sisteme de baze de date nu acceptă în mod direct funcționalitatea de îmbinare exterioară completă, dar o pot emula prin utilizarea unei îmbinări interioare și UNION ALL selectează „rândurile de tabel unic” din tabelele din stânga și respectiv din dreapta. Același exemplu poate apărea după cum urmează:

SELECT employee.LastName, employee.DepartmentID,
       department.DepartmentName, department.DepartmentID
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID

UNION ALL

SELECT employee.LastName, employee.DepartmentID,
       cast(NULL as varchar(20)), cast(NULL as integer)
FROM employee
WHERE NOT EXISTS (
    SELECT * FROM department
             WHERE employee.DepartmentID = department.DepartmentID)

UNION ALL

SELECT cast(NULL as varchar(20)), cast(NULL as integer),
       department.DepartmentName, department.DepartmentID
FROM department
WHERE NOT EXISTS (
    SELECT * FROM employee
             WHERE employee.DepartmentID = department.DepartmentID)

O altă abordare ar putea fi UNION TOATE din îmbinarea exterioară stângă și îmbinarea exterioară dreaptă MINUS îmbinarea interioară.

Auto-alăturați-vă

O auto-îmbinare înseamnă unirea unei mese la sine.

Exemplu

Dacă existau două tabele separate pentru angajați și o interogare care solicita angajații din primul tabel care au aceeași țară ca angajații din al doilea tabel, ar putea fi utilizată o operațiune normală de asociere pentru a găsi tabelul de răspuns. Cu toate acestea, toate informațiile despre angajați sunt conținute într-un singur tabel mare.

Luați în considerare un Employeetabel modificat , cum ar fi următorul:

Un exemplu de interogare a soluției ar putea fi după cum urmează:

SELECT F.EmployeeID, F.LastName, S.EmployeeID, S.LastName, F.Country
FROM Employee F INNER JOIN Employee S ON F.Country = S.Country
WHERE F.EmployeeID < S.EmployeeID
ORDER BY F.EmployeeID, S.EmployeeID;

Ceea ce duce la generarea tabelului următor.

Pentru acest exemplu:

• Fși Ssunt aliasuri pentru prima și a doua copie a tabelului angajaților.
• Condiția F.Country = S.Countryexclude perechile între angajați din diferite țări. Exemplul de întrebare dorea doar perechi de angajați din aceeași țară.
• Condiția F.EmployeeID < S.EmployeeIDexclude împerecherile în care EmployeeIDprimul angajat este mai mare sau egal cu EmployeeIDal celui de-al doilea angajat. Cu alte cuvinte, efectul acestei condiții este de a exclude împerecherile duplicate și auto-perechile. Fără acesta, următorul tabel mai puțin util ar fi generat (tabelul de mai jos afișează doar porțiunea „Germania” a rezultatului):

Doar una dintre cele două perechi de mijloc este necesară pentru a satisface întrebarea inițială, iar cele mai de sus și de jos nu prezintă niciun interes în acest exemplu.

Alternative

Efectul unei îmbinări exterioare poate fi obținut, de asemenea, folosind un UNION ALL între un INNER JOIN și un SELECT al rândurilor din tabelul „principal” care nu îndeplinesc condiția de îmbinare. De exemplu,

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
LEFT OUTER JOIN department ON employee.DepartmentID = department.DepartmentID;

poate fi scris și ca

SELECT employee.LastName, employee.DepartmentID, department.DepartmentName
FROM employee
INNER JOIN department ON employee.DepartmentID = department.DepartmentID

UNION ALL

SELECT employee.LastName, employee.DepartmentID, cast(NULL as varchar(20))
FROM employee
WHERE NOT EXISTS (
    SELECT * FROM department
             WHERE employee.DepartmentID = department.DepartmentID)

Implementare

Multă muncă în sistemele de baze de date a vizat implementarea eficientă a îmbinărilor, deoarece sistemele relaționale solicită în mod obișnuit îmbinările, dar se confruntă cu dificultăți în optimizarea executării lor eficiente. Problema apare deoarece îmbinările interioare operează atât comutativ, cât și asociativ . În practică, acest lucru înseamnă că utilizatorul furnizează doar lista de tabele pentru îmbinare și condițiile de asociere de utilizat, iar sistemul de baze de date are sarcina de a determina cel mai eficient mod de a efectua operația. Un optimizator de interogare determină modul de executare a unei interogări care conține îmbinări. Un optimizator de interogări are două libertăți de bază:

1. Comandă de asociere: Deoarece se alătură funcțiilor comutativ și asociativ, ordinea în care sistemul se alătură tabelelor nu modifică setul de rezultate finale ale interogării. Cu toate acestea, ordinul de asociere ar putea avea un impact enorm asupra costului operațiunii de asociere, astfel încât alegerea celei mai bune comenzi de asociere devine foarte importantă.
2. Metoda de asociere: Având în vedere două tabele și o condiție de asociere, mai mulți algoritmi pot produce setul de rezultate al asocierii. Algoritmul care rulează cel mai eficient depinde de dimensiunile tabelelor de intrare, de numărul de rânduri din fiecare tabel care corespund condiției de asociere și de operațiunile cerute de restul interogării.

Mulți algoritmi de asociere își tratează intrările în mod diferit. Se poate face referire la intrările unei îmbinări ca operanzi de îmbinare „exterioară” și „interioară” sau respectiv „stânga” și „dreapta”. De exemplu, în cazul buclelor imbricate, sistemul de baze de date va scana întreaga relație interioară pentru fiecare rând al relației externe.

Se pot clasifica planurile de interogare care implică îmbinări după cum urmează:

stânga-adânc

folosind o masă de bază (mai degrabă decât o altă asociere) ca operand interior al fiecărei asocieri în plan

dreapta-adânc

folosind o masă de bază ca operand exterior al fiecărei îmbinări în plan

stufos

nici stânga-adâncime, nici dreapta-adâncime; ambele intrări într-o îmbinare pot rezulta ele însele din îmbinări

Aceste nume derivă din apariția planului de interogare dacă este desenat ca un copac , cu relația de îmbinare exterioară în stânga și relația interioară în dreapta (așa cum dictează convenția).

Alăturați algoritmi

Trei algoritmi fundamentale pentru efectuarea unei operațiuni se alăture exit: bucla imbricate se alăture , sortare-îmbinare se alăture și hash se alăture .

Alăturați-vă indexurilor

Indexurile de asociere sunt indexuri de baze de date care facilitează procesarea interogărilor de asociere în depozite de date : acestea sunt în prezent (2012) disponibile în implementări de către Oracle și Teradata .

În implementarea Teradata, coloanele specificate, funcțiile agregate pe coloane sau componentele coloanelor de date dintr-unul sau mai multe tabele sunt specificate utilizând o sintaxă similară cu definiția unei vizualizări a bazei de date : până la 64 de coloane / expresii de coloane pot fi specificate într-o singură alăturați indexului. Opțional, o coloană care definește cheia primară a datelor compozite poate fi, de asemenea, specificată: pe hardware paralel, valorile coloanei sunt folosite pentru partiționarea conținutului indexului pe mai multe discuri. Când tabelele sursă sunt actualizate interactiv de către utilizatori, conținutul indexului de alăturare este actualizat automat. Orice interogare a cărei clauză WHERE specifică orice combinație de coloane sau expresii de coloană care sunt un subset exact al celor definite într-un index de asociere (așa-numita „interogare de acoperire”) va provoca indexul de asociere, mai degrabă decât tabelele originale și indexurile acestora, să fie consultat în timpul executării interogării.

Implementarea Oracle se limitează la utilizarea indexurilor bitmap . Un index de îmbinare bitmap este utilizat pentru coloane cu cardinalitate redusă (adică coloane care conțin mai puțin de 300 de valori distincte, conform documentației Oracle): combină coloane cu cardinalitate redusă din mai multe tabele conexe. Exemplul pe care îl folosește Oracle este cel al unui sistem de inventar, în care diferiți furnizori furnizează diferite părți. Schema are trei tabele legate: două „tabele de master“, partea și furnizor, precum și o „masă de detaliu“, Inventar. Ultimul este un tabel multi-la-mulți care leagă furnizorul de piesă și conține cele mai multe rânduri. Fiecare piesă are un tip de piesă și fiecare furnizor are sediul în SUA și are o coloană State. Nu există mai mult de 60 de state + teritorii în SUA și nu mai mult de 300 de tipuri de piese. Indexul de îmbinare bitmap este definit folosind o îmbinare standard cu trei tabele pe cele trei tabele de mai sus și specificând coloanele Part_Type și Supplier_State pentru index. Cu toate acestea, este definit în tabelul de inventar, chiar dacă coloanele Part_Type și Supplier_State sunt „împrumutate” de la Furnizor și respectiv Part.

În ceea ce privește Teradata, un index Oracle bitmap join este utilizat numai pentru a răspunde la o interogare atunci când clauza WHERE a interogării specifică coloane limitate la cele care sunt incluse în indexul join.

Alăturați-vă direct

Unele sisteme de baze de date permit utilizatorului să forțeze sistemul să citească tabelele într-o asociere într-o anumită ordine. Acesta este utilizat atunci când optimizatorul de asociere alege să citească tabelele într-o ordine ineficientă. De exemplu, în MySQL comanda STRAIGHT_JOINcitește tabelele exact în ordinea listată în interogare.

Vezi si

• Alăturați-vă (algebră relațională)
• Antijoin
• Set operații (SQL)

Referințe

Citații

Surse

linkuri externe

• Specific produselor
  • Sybase ASE 15 se alătură
  • Se alătură MySQL 5.7
  • PostgreSQL 9.3 Alătură-te
  • Se alătură în Microsoft SQL Server
  • Se alătură în MaxDB 7.6
  • Se alătură în Oracle 12c R1
  • Oracle SQL Joins