Subinterogare corelată în SQL: cum funcționează, cu exemple

În bazele de date relaționale, rândurile sunt adesea interdependente, iar pentru a răspunde la o întrebare complexă o interogare trebuie adesea să revină la tabelul pe care îl procesează în acel moment.

Pentru a interoga astfel de tabele, SQL permite folosirea subinterogărilor corelate, care definesc o relație specifică în care interogarea interioară depinde de valorile interogării exterioare. În timp ce o subinterogare standard rulează o singură dată și se încheie, o subinterogare corelată este dinamică, executându-se repetat pentru fiecare rând evaluat de interogarea principală.

În acest tutorial, voi explica modul în care funcționează o subinterogare corelată în SQL, aspectele privind performanța și când este alegerea potrivită în comparație cu join-urile și funcțiile de fereastră. Dacă sunteți la început cu SQL, începeți cu cursul nostru Introduction to SQL sau cu Intermediate SQL dacă aveți deja ceva experiență. 

Ce este o subinterogare corelată?

O subinterogare corelată este un tip de subinterogare care depinde de valorile din interogarea exterioară pentru a rula. 

În loc să se execute o dată și să returneze un rezultat fix, subinterogarea este evaluată pentru fiecare rând procesat de interogarea exterioară. Acest lucru se întâmplă deoarece interogarea interioară face referire la o coloană din interogarea exterioară, creând o legătură directă între cele două interogări.

Prin comparație, o subinterogare necorelată rulează independent de interogarea exterioară. Se execută o dată, returnează un set de rezultate sau o valoare, iar interogarea exterioară folosește acel rezultat fără a relansa subinterogarea pentru fiecare rând.

Cum funcționează o subinterogare corelată

O subinterogare corelată tipică în SQL are următorul flux de lucru:

Cum funcționează o subinterogare corelată. Imagine de Gemini.

• Interogarea exterioară alege un rând: SQL începe să scaneze tabelul interogării exterioare și selectează primul rând. 
• Referențiere: Interogarea interioară preia o valoare din acel rând specific, adesea folosind un alias.
• Execuție: Interogarea interioară rulează folosind acea valoare.
• Filtrare/actualizare: Rezultatul este trimis înapoi la interogarea exterioară pentru a decide dacă rândul este inclus.
• Iterație: Procesul se repetă pentru rândul următor până la finalizarea tabelului.

Exemplu de subinterogare corelată în SQL

Până acum, ce am spus este conceptual. Cel mai bun mod de a învăța este prin exemple.

Exemplul 1: Angajați care câștigă peste media departamentului

Să presupunem că aveți un tabel employees cu salariile angajaților și ID-urile departamentelor. Doriți să găsiți angajații care câștigă mai mult decât salariul mediu din departamentul lor.

Veți folosi interogarea de mai jos, unde:

• Interogarea exterioară selectează angajații din tabelul employees.

• Subinterogarea calculează salariul mediu pentru același departament.

• Condiția e2.department_id = e.department_id face referire la aliasul interogării exterioare e.

 -- Fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)                 -- Calculate the average salary
    FROM employees e2
    WHERE e2.department_id = e.department_id  
    -- Correlation: references the outer query's department_id
);

Exemplul 2: Folosirea EXISTS() cu subinterogare corelată

Puteți folosi și operatorul EXISTS() împreună cu o subinterogare corelată pentru a verifica dacă există în alt tabel înregistrări înrudite.

Să spunem că aveți înregistrări în tabelele customers și orders. Doriți să listați clienții care au plasat cel puțin o comandă. Veți folosi interogarea de mai jos, unde:

• Interogarea exterioară scanează rândurile din tabelul customers.

• Subinterogarea verifică dacă există cel puțin o comandă pentru acel client.

• Condiția o.customer_id = c.customer_id leagă subinterogarea de interogarea exterioară.

-- Fetch customers with at least one order
SELECT 
    c.customer_id,
    c.customer_name
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id  
    -- Correlation: references the outer query customer_id
);

În interogarea de mai sus, SQL verifică dacă există un rând care se potrivește în tabelul orders. Dacă da, operatorul EXISTS() returnează adevărat, iar clientul este inclus în rezultat.

Subinterogări corelate vs. necorelate

După cum am învățat anterior, subinterogările în SQL se împart în subinterogări necorelate sau corelate. Diferența cheie este dacă interogarea interioară depinde de interogarea exterioară.

Pentru o subinterogare necorelată, baza de date o execută o dată, apoi folosește rezultatul în interogarea exterioară.

De exemplu, interogarea de mai jos găsește angajații care câștigă mai mult decât salariul mediu general.

-- Query employees who earn more than the overall average salary
SELECT 
    employee_id,
    employee_name,
    salary
FROM employees
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees
);

În interogarea de mai sus, subinterogarea calculează salariul mediu pentru întregul tabel și rulează o singură dată. Interogarea exterioară compară apoi salariul fiecărui angajat cu acea valoare unică.

Deoarece subinterogările necorelate rulează o dată, ele sunt de obicei mai rapide când rezultatul poate fi reutilizat. Sunt cele mai potrivite pentru comparații globale, precum medii și totaluri generale.

Totuși, subinterogările corelate pot fi mai lente pe tabele mari. Devine utile când condițiile trebuie evaluate relativ la fiecare rând, precum comparații la nivel de departament sau verificări de existență.

Recomand să urmați cursul nostru  Introduction to SQL Server pentru a afla mai multe despre grupare și agregarea datelor și despre îmbinarea tabelelor.

Subinterogare corelată vs. JOIN

Multe subinterogări corelate pot fi rescrise folosind JOIN-uri. În bazele de date relaționale, JOIN-urile au performanțe mai bune deoarece baza de date poate procesa relațiile set-cu-set, nu rând-cu-rând.

Analizați interogarea de mai jos, care folosește o subinterogare corelată. Această interogare listează angajații plătiți peste salariul mediu din departamentul lor

-- Use subquery to fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Puteți rescrie interogarea folosind clauza JOIN pentru a obține aceleași rezultate.

-- Use JOIN to fetch employees earning more than the average salary in dept
SELECT 
    e.employee_id,
    e.employee_name,
    e.salary,
    e.department_id
FROM employees e
JOIN (
    SELECT 
        department_id,
        AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
    -- Precompute the department average once per department
) dept_avg
ON e.department_id = dept_avg.department_id
-- Match employees with their department averages
WHERE e.salary > dept_avg.avg_salary;
-- Compare salary with the computed department average

Tabelul de mai jos rezumă diferența dintre subinterogările corelate și JOIN în SQL.

Recomand să urmați cursul nostru Joining Data in SQL pentru a învăța diferitele tipuri de join-uri în SQL și cum să lucrați cu diverse tabele înrudite din baza de date.

Subinterogare corelată vs. funcții de fereastră

În SQL modern, funcțiile de fereastră precum AVG() și OVER (PARTITION BY) pot calcula agregate per rând într-o singură scanare. 

De exemplu, interogarea de mai jos returnează angajații al căror salariu este mai mare decât media salariilor din departamentul lor. În interiorul subinterogării, folosește OVER () pentru a transforma agregarea într-o funcție de fereastră și PARTITION BY department_id pentru a împărți tabelul în grupuri (partiții) după departament.

-- Use window function to get employees earning more than dept average salary
SELECT 
    employee_id,
    employee_name,
    salary,
    department_id
FROM (
    SELECT 
        employee_id,
        employee_name,
        salary,
        department_id,
        AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary
        -- Window function calculates department average once per partition
    FROM employees
) t
WHERE salary > dept_avg_salary;

Totuși, subinterogările corelate rămân utile când doriți să folosiți EXISTS() sau NOT EXISTS() pentru a testa relațiile dintre tabele. De asemenea, puteți folosi subinterogări corelate când lucrați în baze de date sau situații în care funcțiile de fereastră nu sunt disponibile.

Performanța subinterogărilor corelate

Deși subinterogările corelate sunt puternice, ele vin adesea cu unele probleme de performanță. 

Subinterogările corelate se execută repetat

Deoarece interogarea rulează o dată per rând al interogării exterioare, poate încetini interogările pe tabele mari prin rescănarea datelor interioare de mai multe ori. Dacă tabelul exterior are 100.000 de rânduri, baza de date efectuează 100.000 de sub-sarcini.

Blocaje

Dacă nu aplicați optimizarea corespunzătoare, interogările corelate pot duce la utilizare ridicată a CPU și timpi mari de așteptare, mai ales dacă interogarea interioară efectuează calcule complexe sau scanează tabele mari.

Indexarea coloanelor

Indexarea coloanelor folosite în corelare va ajuta baza de date să găsească aproape instantaneu rândul înrudit în subinterogare, în loc să scaneze întregul tabel interior de fiecare dată.

Optimizarea de către Query Planner

Bazele de date moderne optimizează adesea intern subinterogările corelate. Planificatorul de interogări poate transforma interogarea într-o formă mai eficientă, cum ar fi un JOIN sau o agregare memorată în cache, reducând semnificativ timpul de execuție.

Când să folosiți o subinterogare corelată

Puteți folosi subinterogări corelate dacă doriți să efectuați următoarele:

• Filtrare pe baza agregatelor specifice rândului: Folosiți-o când trebuie să comparați o valoare relativ la fiecare rând, cum ar fi angajați care câștigă peste media departamentului.

• Verificarea datelor înrudite cu EXISTS(): Puteți folosi subinterogări corelate cu EXISTS() pentru a testa dacă există rânduri înrudite.

• Exprimarea logicii imbricate complexe: Subinterogările corelate pot ajuta la exprimarea mai clară a condițiilor complexe decât lanțuri lungi de JOIN-uri.

Totuși, evitați să folosiți subinterogări corelate atunci când:

• Funcționează un JOIN simplu: Dacă puteți obține același rezultat cu un LEFT JOIN sau INNER JOIN, folosiți-l, deoarece va fi întotdeauna mai rapid.

• Lucrați cu volume mari de date: Dacă condiția corelată face referire la tabele mari fără indici, evaluările repetate pot încetini semnificativ interogarea.

Greșeli frecvente cu subinterogările corelate

Mai jos sunt câteva probleme comune pe care le puteți întâlni când folosiți subinterogări corelate și cum să le depanați:

• Uitarea condiției de corelare: O subinterogare corelată trebuie să facă referire la o coloană din interogarea exterioară. Dacă lipsește această condiție, subinterogarea devine independentă și poate produce rezultate incorecte.
• Neînțelegerea ordinii de execuție: Țineți minte mereu că interogarea exterioară rulează prima, iar cea interioară urmează. Inversarea acestei logici în gândire poate duce la rezultate greșite.
• Imbricarea inutilă: Uneori puteți înfășura o valoare simplă într-o subinterogare corelată când ar fi suficientă o subinterogare standard. Dacă interogarea interioară nu are nevoie de rândul exterior pentru a funcționa, eliminați corelarea pentru a economisi performanță.
• Ignorarea impactului asupra performanței: Subinterogările corelate pot funcționa perfect pe seturi mici de date, dar devin lente pe măsură ce tabelele cresc. Pentru a evita acest lucru, testați-vă întotdeauna interogările cu dimensiuni realiste ale datelor și luați în considerare indexarea sau rescrierea interogării dacă performanța devine o problemă.

Concluzie

Să învățați când și cum să folosiți subinterogările corelate și când să le înlocuiți cu alte tehnici este o abilitate importantă pentru a scrie interogări SQL clare și eficiente.

Ca pas următor, recomand să obțineți certificarea SQL Associate pentru a vă demonstra stăpânirea SQL în analiza datelor și pentru a vă evidenția în rândul profesioniștilor din domeniu. În final, recomand cursul nostru Database Design, unde veți învăța să creați și să gestionați baze de date și să alegeți SGBD-ul potrivit pentru nevoile dumneavoastră.