MySQL Subinterogare cu exemple

Ce este o subinterogare în SQL?

A subinterogare este o interogare SELECT conținută în interiorul unei alte interogări. Interogarea de selecție internă este de obicei utilizată pentru a determina rezultatele interogării de selecție externe, astfel încât baza de date evaluează mai întâi instrucțiunea internă și apoi transmite rezultatul său în sus.

Interogarea internă se numește interogare internă sau o interogare imbricată, iar instrucțiunea care o conține se numește interogare externăSă analizăm sintaxa subinterogării.

Diagrama de mai sus prezintă forma generală a instrucțiunii: instrucțiunea SELECT externă furnizează coloanele pe care doriți să le vedeți, iar instrucțiunea SELECT internă între paranteze furnizează valoarea sau lista de valori cu care se compară clauza WHERE.

De ce să folosim o subinterogare?

Înainte de a analiza diferitele tipuri, este util să știm când o subinterogare își câștigă locul într-o instrucțiune.

O subinterogare răspunde la o întrebare a cărei valoare a filtrului nu este cunoscută în avans. Trebuie calculată din datele în sine. O reclamație frecventă a clienților de la Biblioteca Video MyFlix este numărul mic de titluri de filme, iar conducerea dorește să cumpere filme din categoria care are cel mai mic număr de titluri. Nimeni nu știe în ce categorie este vorba până când nu se solicită accesul la baza de date, așa că valoarea trebuie calculată mai întâi și apoi utilizată ca filtru.

Subinterogările sunt latractiv din trei motive practice:

• lizibilitate: Fiecare parte a logicii se află în propriul bloc între paranteze, astfel încât afirmația se citește ca o secvență de întrebări mici, mai degrabă decât ca o expresie complexă.
• Izolare: O interogare internă poate fi rulată independent pentru a confirma că returnează valoarea așteptată, ceea ce facilitează mult testarea și depanarea.
• Flexibilitate: Același model funcționează în clauzele WHERE, HAVING, SELECT și FROM, precum și în interiorul instrucțiunilor INSERT, UPDATE și DELETE.

Compromisul este viteza, care este examinată în comparația JOIN mai târziu în acest articol.

Tipuri de subinterogări în MySQL

MySQL acceptă trei tipuri de subinterogări, iar tipul este decis în funcție de forma rezultatului returnat de interogarea internă. Fiecare tip este explicat mai jos cu un exemplu funcțional pentru baza de date myflixdb.

1) Subinterogare scalară

A subinterogare scalară returnează exact un rând și o coloană, ceea ce înseamnă că returnează o singură valoare. Deoarece rezultatul este o singură valoare, poate fi utilizat oriunde este permisă o valoare literală. Revenind la problema MyFlix de mai sus, puteți utiliza o interogare ca aceasta:

SELECT category_name FROM categories
WHERE category_id = (SELECT MIN(category_id) FROM movies);

Dă un rezultat:

Să vedem cum funcționează această interogare.

După cum arată diagrama de execuție, MySQL primele alergări SELECT MIN(category_id) FROM movies, primește o valoare și abia apoi rulează interogarea externă cu acea valoare. Deoarece este returnată o singură valoare, operatorii permiși sunt setul standard de comparare: =, <> (Sau !=), >, >=, < și <=.

💡 Sfat: Dacă o subinterogare plasată după = returnează mai mult de un rând, MySQL generează eroarea 1242, Subinterogarea returnează mai mult de un rândComutați operatorul la INsau strângeți clauza WHERE interioară.

2) Subinterogare pe rânduri

A subinterogare de rând returnează, de asemenea, un singur rând, dar acel rând poate conține mai multe coloane. Prin urmare, interogarea externă compară un rând de valori cu un constructor de rânduri, mai degrabă decât cu o singură valoare.

SELECT full_names, contact_number FROM members
WHERE (membership_number, gender) = (SELECT membership_number, gender FROM members WHERE full_names = 'Janet Jones');

Operatorii permiși sunt aceiași operatori de comparație enumerați mai sus, aplicați întregului rând simultan.

3) Subinterogare tabel

A subinterogare de tabel returnează mai multe rânduri și adesea mai multe coloane, deci interogarea externă trebuie să utilizeze un operator de mulțime, cum ar fi IN, NOT IN, ANY, ALL, EXISTS.

Să presupunem că doriți numele și numerele de telefon ale membrilor care au închiriat un film și nu l-au returnat încă, astfel încât să îi puteți suna pentru a le reaminti. Puteți utiliza o interogare ca aceasta:

SELECT full_names, contact_number FROM members
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

Să vedem cum funcționează această interogare.

În acest caz, interogarea internă returnează mai mult de un rezultat, așadar lista numerelor de membru este predată către IN operatorul și fiecare membru potrivit este returnat.

Imbricarea subinterogărilor la mai multe niveluri de adâncime

Până acum ați văzut două niveluri. O subinterogare poate conține și o altă subinterogare, care produce o instrucțiune imbricată triplă.

Să presupunem că conducerea dorește să recompenseze membrul care plătește cel mai mult. Putem rula o interogare de genul acesta:

SELECT full_names FROM members
WHERE membership_number = (SELECT membership_number FROM payments
    WHERE amount_paid = (SELECT MAX(amount_paid) FROM payments));

Interogarea cea mai interioară găsește cea mai mare plată, interogarea din mijloc convertește suma respectivă într-un număr de membru, iar interogarea exterioară convertește numărul de membru într-un nume. Interogarea de mai sus dă următorul rezultat:

Cum se utilizează subinterogările cu INSERT, UPDATE și DELETE

Subinterogările nu se limitează la instrucțiuni SELECT. Același model între paranteze funcționează în cadrul instrucțiunilor de modificare a datelor, ceea ce permite modificarea unui set întreg de rânduri într-o singură trecere, fără a crea un tabel temporar.

INSERT cu o subinterogare. O subinterogare poate furniza rândurile care sunt inserate, ceea ce copiază date dintr-un tabel în altul. Lista de coloane a comenzii SELECT trebuie să se alinieze cu lista de coloane a comenzii INSERT.

INSERT INTO vip_members (membership_number, full_names)
SELECT membership_number, full_names FROM members
WHERE membership_number IN (SELECT membership_number FROM payments WHERE amount_paid > 5000);

ACTUALIZARE cu o subinterogare. Aici, interogarea internă decide ce rânduri sunt atinse. Exemplul de mai jos semnalează fiecare membru a cărui închiriere este încă restantă.

UPDATE members
SET reminder_sent = 1
WHERE membership_number IN (SELECT membership_number FROM movierentals WHERE return_date IS NULL);

ȘTERGE cu o subinterogare. Aceeași idee elimină rândurile care îndeplinesc o condiție menținută într-un al doilea tabel.

DELETE FROM members
WHERE membership_number NOT IN (SELECT membership_number FROM movierentals);

⚠️ Atenție: MySQL nu permite unei instrucțiuni să modifice un tabel și să selecteze din același tabel în interiorul unei subinterogări din clauza FROM. Dacă apare eroarea 1093, încapsulează interogarea internă într-un tabel derivat, de exemplu SELECT * FROM (SELECT ...) AS t, Astfel încât MySQL materializează rezultatul înainte de aplicarea modificării. De asemenea, este înțelept să rulați mai întâi comanda SELECT internă de la sine și să confirmați numărul de rânduri înainte de a rula o comandă UPDATE sau un DELETE in productie.

Subinterogări vs. Unionări

Atât o subinterogare, cât și o JOIN pot combina informații din mai multe tabele, așa că întrebarea firească este pe care să o alegeți.

În comparație cu joncțiunile, subinterogările sunt simple de utilizat și ușor de citit. Nu sunt la fel de complicate ca Se alăturăși, prin urmare, sunt frecvent utilizate de SQL începători.

Însă subinterogările au probleme de performanță. Utilizarea unei joncțiuni în locul unei subinterogări poate uneori să ofere o creștere a performanței de până la 500 de ori, deoarece optimizatorul poate rezolva o joncțiune într-o singură trecere, în loc să evalueze în mod repetat instrucțiunea internă.

Având posibilitatea de a alege, este recomandat să utilizați un JOIN peste o subinterogare. Subinterogările ar trebui utilizate doar ca soluție de rezervă atunci când nu puteți utiliza o operație JOIN pentru a realiza cele de mai sus.

Subinterogările sunt, de asemenea, ușor de descompus în componente logice individuale, ceea ce este foarte util atunci când de testare și depanarea interogărilor.

Întrebări frecvente