Introducerea unei interogări în interiorul alteia

La sfârșitul capitolului 9, am spus că cererile pot gestiona alte solicitări. În acest capitol veți afla cum se face acest lucru (în majoritatea cazurilor) prin plasarea unei interogări în predicatul unei alte interogări și utilizând rezultatul interogării interne în condiția predicatei adevărate sau false.

Puteți afla ce tipuri de operatori pot utiliza subdotare și să vedeți cum funcționează subcheile cu instrumentele SQL, cum ar fi DISTINCT, cu funcții combinate și expresii de ieșire.

Veți învăța cum să utilizați subcheile cu clauza HAVING și obțineți câteva instrucțiuni cu privire la modul în care să utilizați în mod corespunzător subcheile.

FACĂ O LUCRU SUB-OPERARE?

Cu SQL, puteți adăuga cereri de prietenie unui prieten. De obicei, interogarea internă generează o valoare care este bifată în predicatul unei interogări externe care determină dacă este adevărată sau nu. De exemplu, să presupunem că știm numele vânzătorului: Motika, dar nu cunoaștem valoarea câmpului lui snum și dorim să preluăm toate comenzile din tabelul Comenzi. Iată o modalitate de a face acest lucru (ieșirea este prezentată în Figura 10.1):

Pentru a evalua o interogare externă (principală), SQL trebuie să evalueze mai întâi interogarea internă (sau subchetă) în cadrul clauzei WHERE. Ea face acest lucru modul în care ar trebui să facă o interogare care are un singur scop - de a căuta prin tabelul Vânzări pentru toate liniile unde câmpul sname este egal cu valoarea Motika și apoi extrageți valorile câmpului snum al acestor linii.

Singura linie găsită este, desigur, snum = 1004. Cu toate acestea, SQL nu dă doar această valoare, ci pune în predicatul interogării principale în locul subchetei propriu-zise, astfel încât predicatul citește că

Interogarea principală este apoi executată ca de obicei cu rezultatele de mai sus. Desigur, subcheierea trebuie să selecteze una și numai una, o coloană și tipul de date al acestei coloane trebuie să se potrivească cu valoarea cu care va fi comparată în predicat.
Adesea, după cum se arată mai sus, câmpul selectat și valoarea lui vor avea aceleași nume (în acest caz snum), dar acest lucru nu este necesar. Desigur, dacă știm deja numărul vânzătorului de Motika, am putea să tipăm WHERE snum = 1004 și să lucrăm mai departe cu subcheierea ca întreg, dar nu ar fi atât de universală. Aceeași interogare va continua să funcționeze, chiar dacă numărul Motica sa schimbat și prin schimbarea pur și simplu a numelui în subchetă, îl puteți folosi pentru orice.

MODURI DE CREȘTERE A UNEI SUBCOTATE

Cel mai probabil, ar fi mai convenabil pentru subansamblul nostru din exemplul anterior să ne întoarcem una și numai o singură valoare.

Având snum câmp selectat „unde orașul =“ Londra „în loc de“ UNDE SNAME = „Motika“, poate fi preparat un număr de valori diferite. Acest lucru se poate face în evaluare interogare predicatul principal de fidelitate imposibilă sau infidelitatea, iar comanda va returna o eroare.

Atunci când se utilizează un predicate subinterogarea bazate pe operații relaționale (inegalități sau ecuații așa cum se explică în capitolul 4), trebuie să vă asigurați că o subinterogare folosit, care va da unul, și numai unul, linie de ieșire. Dacă utilizați o subchetare care nu afișează nicio valoare, comanda nu va eșua, dar interogarea care stă la baza nu va afișa nici o valoare. Subqueries care nu produc nici o ieșire (sau ieșire nulă) provoca predicat să fie considerate fie ca fiind adevărat sau ca incorecte, dar ca necunoscut. Cu toate acestea, predicatul necunoscut are același efect ca și predicatul greșit: prin interogarea principală nu sunt selectate rânduri (vedeți capitolul 5 pentru detalii despre predicatul necunoscut).

Iată un exemplu de strategie rău:

Deoarece avem doar un singur vânzător în Barcelona-Rifkin, subcontractul va alege o singură valoare de ninsoare și, prin urmare, va fi acceptat. Dar numai în acest caz. Cele mai multe baze de date SQL au mai mulți utilizatori, iar dacă un alt utilizator adaugă un alt vânzător de la Barcelona la masă, subcheierea va selecta două valori, iar echipa dvs. va eșua.

DISTANȚĂ CU ASPECTELE

Iată o modalitate de a face acest lucru (ieșirea este prezentată în Figura 10.2):

Subinterogare a constatat că valoarea câmpului snum a coincis cu Hoffman - 1001, și apoi interogarea principal selectat toate comenzile cu această snum valoare din tabelul de comenzi (nu cauta, din care fac parte Hoffman sau nu). Deoarece fiecare client este alocat vânzătorului, știm că fiecare linie din tabelul Comenzi cu această valoare cnum ar trebui să aibă aceeași valoare înghițită. Cu toate acestea, deoarece poate exista un număr de astfel de linii, subdotarea ar putea genera mai multe (deși identice) valori snum pentru câmpul cnum dat. Argumentul DISTINCT împiedică acest lucru. Dacă subrogierul nostru returnează mai mult de o valoare, aceasta va indica o eroare în datele noastre - un lucru bun pentru cei care știu despre el.

Trebuie să existe o abordare alternativă pentru a face referire la tabelul Client, și nu la tabelul Comenzi din subdotare. Deoarece câmpul cnum este cheia primară a tabelului Clienți, interogarea care o selectează trebuie să producă o singură valoare. Acest lucru este rațional numai dacă, în calitate de utilizator, aveți acces la tabelul Comenzi, dar nu la tabelul Client. În acest caz, puteți utiliza soluția pe care am arătat-o mai sus. (SQL are mecanisme care determină cine are privilegiile de a efectua acțiuni într-un tabel specific, care va fi explicat în Capitolul 22.)

Rețineți că metodologia utilizată în exemplul anterior este valabilă numai atunci când știți că două câmpuri diferite din tabel trebuie să se potrivească mereu, ca în cazul nostru. Această situație nu este tipică în bazele de date relaționale (RDB), este o excepție de la reguli.

REDICATELE CU SUBSECURITĂȚI SUNT UNOR

Ar trebui să fiți atenți la faptul că predicatele, inclusiv subcheile, utilizează expresia

Cu alte cuvinte, nu trebuie să scrieți exemplul anterior ca acesta:

Într-o implementare strictă a ANSI, aceasta va duce la eșec, deși unele programe vă permit să faceți astfel de lucruri. ANSI previne, de asemenea, apariția în rezultatele de subcotare a ambelor valori atunci când se compară.

ȘI UTILIZAREA FUNCȚIILOR AGREGATE ÎN SUBIȚIUNI

Un tip de funcție care poate produce automat o singură valoare pentru orice număr de rânduri, desigur, este o funcție agregată.

De exemplu, următoarea interogare care ar trebui să găsească valoarea medie a comisionului vânzătorului în Londra,

nu poate fi folosit într-o subcotare! În orice caz, aceasta nu este cea mai bună modalitate de a forma o cerere.

O altă modalitate poate fi

ȘI UTILIZAREA SUB-ÎNTREBĂRILOR CARE CREȘTE MAI MULTE LINII CU AJUTORUL OPERATORULUI

Aveți posibilitatea să utilizați subdotări care produc orice număr de linii dacă utilizați un operator special IN (operatorii BETWEEN, LIKE și IS NULL nu pot fi utilizați cu subqueries). După cum vă amintiți, IN definește un set de valori, dintre care unul trebuie să se potrivească cu celălalt termen al ecuației predicate în ordine, astfel încât predicatul să fie adevărat.
Când utilizați IN cu o subchetare, SQL generează pur și simplu acest set de la ieșirea din subchetă. Prin urmare, putem folosi IN pentru a efectua o subcotare care nu va funcționa cu operatorul relațional și pentru a găsi toate atributele tabelului Comenzi pentru vânzătorul din Londra (rezultatul este prezentat în Figura 10.4):

Într-o astfel de situație, subcheierea este mai ușor de înțeles de către utilizator și este mai ușor de condus de calculator decât dacă utilizați o conexiune:

Puteți elimina necesitatea pentru DISTINCT utilizând IN în loc de (=):

Ce se întâmplă dacă există o eroare și una dintre comenzi a fost acreditată vânzătorilor diferiți? O versiune folosind IN vă va oferi toate comenzile pentru ambii vânzători. Nu există nici o modalitate evidentă de a monitoriza eroarea și, prin urmare, rapoartele sau deciziile generate pe baza acestei interogări nu vor conține o eroare. O variantă care utilizează (=) va eșua pur și simplu. Aceasta, cel puțin, vă permite să știți că există o astfel de problemă. Apoi, trebuie să efectuați o depanare executând separat această subcontractare și respectând valorile pe care le produce. Practic, dacă știți că o subcotare ar trebui (logic) să genereze o singură valoare, ar trebui să folosiți =.
IN este adecvat dacă interogarea poate produce restrictiv una sau mai multe valori, indiferent dacă vă așteptați sau nu. Să presupunem că vrem să știm comisionul tuturor furnizorilor care deservesc clienții din Londra:

Rezultatul acestei interogări, prezentat în Figura 10.5, este valoarea comisionului vânzătorului Peel (snum = 1001), care are atât clienți în Londra. Dar numai pentru acest caz. Nu există nici un motiv ca anumiți clienți din Londra să nu poată fi alocați altcuiva. Prin urmare, IN este forma cea mai logică de utilizat într-o interogare.

SUB-ÎNTREBĂRI ALEGĂ UNELE COLOANE

Semnificația tuturor subrutinelor discutate în acest capitol este că toți aleg o singură coloană. Acest lucru este necesar, deoarece rezultatul rezultat este comparat cu o singură valoare. Acest lucru este confirmat de faptul că SELECT * nu poate fi folosit într-un subquery. Există o excepție la aceasta atunci când subcheile sunt folosite cu instrucțiunea EXISTS, despre care vom vorbi în Capitolul 12.

UTILIZAREA EXPRESIUNILOR ÎN SUB-FORȚE

Puteți utiliza o expresie bazată pe o coloană, nu doar pe coloana însăși, în clauza SELECT subchetare. Acest lucru se poate face fie cu ajutorul operatorilor relaționali, fie cu IN. De exemplu, următoarea interogare utilizează operatorul relațional = (ieșirea este prezentată în Figura 10.6):

El găsește toți clienții a căror valoare a câmpului cnum este de 1000, mai mare decât câmpul Snres Snour. Presupunem că coloana sname nu are valori duble (acest lucru poate fi prescris fie de INDICATUL UNIC discutat în Capitolul 17, fie de restricția UNIQUE discutată în Capitolul 18); altfel

o subchetare poate produce mai multe valori. Când câmpurile snum și cnum nu au o valoare funcțională simplă, ca de exemplu cheia primară, care nu este întotdeauna bună, o interogare ca cea de mai sus este incredibil de utilă.

P ODZAPROSY IN OFERTA DE CUPRINS

De asemenea, puteți utiliza subdotări în clauza HAVING. Aceste subdotări pot utiliza propriile funcții agregate dacă nu produc valori multiple sau utilizează GROUP BY sau HAVING. Următoarea interogare este un exemplu de acest lucru (ieșirea este prezentată în Figura 10.7):

Această echipă numără clienții din San Jose cu rating mai mare decât media. Deoarece există alte estimări, altele decât 300, acestea ar trebui deduse cu numărul de clienți care au avut această evaluare.

Acum folosiți interogările în mod ierarhic. Ați văzut că folosirea rezultatului unei interogări pentru a controla un alt extinde posibilitățile, permițându-vă să efectuați mai multe funcții.

Acum ați înțeles cum să utilizați subinterogările la operațiunile relaționale și un operator special IN, sau în clauza WHERE sau HAVING de interogare exterior.

În capitolele următoare vom examina subcotări. În primul rând, în capitolul 11, vom discuta despre un alt fel de subinterogare, care este executat pentru fiecare rând din tabel, numit interogarea exterioară. Apoi, în capitolele 12 și 13, vă vom prezenta câțiva operatori speciali care operează pe toate sub-interogări, așa cum o face IN, cu excepția cazurilor în care pot fi utilizate numai operatorii din sub-interogări.