Table of Contents

Laborator 08 - Subcereri SQL (I)

Obiective

Materiale ajutătoare

Resurse BD

Subcereri

Subcererile sunt cereri SQL incluse în clauzele SELECT, FROM, WHERE, HAVING și ORDER BY ale altei cereri numită și cerere principală. Rezultatele întoarse de subcereri sunt folosite de o altă subcerere sau de cererea principală în situații cum ar fi:

Subcererile pot fi imbricate, adică, o subcerere poate avea propriile ei subcereri.

Dacă există o legătură de asociere sau nu între subcere și cererea exterioară, subcererile pot fi de 2 tipuri:

Din punct de vedere al rolului pe care îl au într-o comandă SQL și a modului de a face construcția comenzii, subcererile pot fi împărțite în:

Subcererile pot fi împărțite pe mai multe categorii, în funcție de numărul de coloane sau linii pe care le returnează:

Această împărțire pe categorii (în funcție de numărul de coloane și linii pe care le întoarce subcererea) a subcererilor se aplică atât pentru subcererile necorelate cât și pentru cele corelate.

Subcererile pot fi împărțite în două categorii, în funcție de modul în care sunt corelate cu cererea principală (cerea care cuprinde subcererea):

Când folosim subcereri, trebuie să respectăm câteva reguli:

Subcereri necorelate în clauza WHERE

Subcereri necorelate care întorc o valoare în clauza WHERE
SELECT 
		[table_1.]expr_1, 
		[table_1.]expr_2,
		..., 
		[table_1.]expr_n
FROM table_1
WHERE [NOT] [table_1.]expr_k OPERATOR
	(SELECT [table_2.]expr_i 
	FROM table_2
	[WHERE conditions_1]
	[HAVING conditions_2])

[table_1.]expr_k – sunt expresii pe coloanele(sau doar o coloană) care aparțin tabelului table_1, k=1..n;
[table_2.]expr_i – este o expresie pe coloanele(sau doar o coloană) care aparțin tabelului table_2;
OPERATOR – este mulțimea operatorilor de comparație {>, >=, =, <=, <};
conditions_1 – sunt condițiile din clauza WHERE (opțională) a subcererii;
conditions_2 – sunt condițiile din clauza HAVING (opțională) a subcererii;

Ex. 1. Să se selecteze angajatul cu cel mai mare salariu din firmă.

Subcereri necorelate care întorc o coloană în clauza WHERE

Sunt subcereri care întorc mai multe valori și folosesc operatorii IN și NOT IN. În cazul în care se folosesc operatori de comparație în locul operatorului IN (NOT IN), atunci se generează o eroare. Observatie:

Operatorul IN (NOT IN) se poate folosi în loc de operatori de comparație, dar nu și invers. 

SELECT 
		[table_1.]expr_1, 
		[table_1.]expr_2,
		..., 
		[table_1.]expr_n
FROM table_1
WHERE [table_1.]expr_k  [NOT] IN
	(SELECT [table_2.]expr_i 
	FROM table_2
	[WHERE conditions_1]
	[[GROUP BY expressions] [HAVING conditions_2]])

Ex. 2. Să se selecteze angajații care au funcții similare funcțiilor din departamentul 20 și nu lucrează în acest departament. Ex. 3. Să se selecteze angajații care nu s-au angajat în lunile decembrie, ianuarie și februarie. Ex. 4. Să se selecteze angajații care au salariile in lista de salarii maxime pe departament.

Subcereri necorelate care întorc o linie în clauza WHERE
SELECT  [table_1.]expr_1, [table_1.]expr_2,..., [table_1.]expr_n
FROM table_1
WHERE ([table_1.]expr_i1, ..., [table_1.]expr_im)  [NOT] IN
	(SELECT  [table_2.]expr_j1, ..., [table_1.]expr_jm
	FROM table_2
	[WHERE conditions_1]
	[HAVING conditions_2])

Ex. 5. Să se selecteze angajații care au venit în același an și au aceeași funcție cu angajatul care are numele JONES.

Subcereri necorelate care întorc mai multe linii în clauza WHERE
SELECT  [table_1.]expr_1, [table_1.]expr_2,..., [table_1.]expr_n
FROM table_1
WHERE ([table_1.]expr_i1, ..., [table_1.]expr_im)  [NOT] IN
	(SELECT  [table_2.]expr_j1, ..., [table_1.]expr_jm
	FROM table_2
	[WHERE conditions_1]
	[[GROUP BY expressions] [HAVING conditions_2]])

Ex. 6. Să se afișeze angajatii care au venitul lunar minim pe fiecare departament. Ex. 7. Să se afișeze angajații care au salariul mai mare decât salariul maxim din departamentul SALES. Acesta este un exemplu de subcereri imbricate (in total 3 nivele), subcererile insa NU intorc mai multe linii in final, ci o singura valoare.

Subcereri corelate în clauze WHERE

Subcererile corelate se execută o singură dată pentru fiecare linie candidat prelucrată de cererea principală. O subcerere corelată se join-ează cu cererea exterioară prin folosirea unei coloane a cererii exterioare în clauza predicatului cererii interioare. 

SELECT  [table_1.]expr_1, [table_1.]expr_2,..., [table_1.]expr_n
FROM table_1
WHERE ([table_1.]expr_i1, ..., [table_1.]expr_im)  [NOT] IN
	(SELECT  [table_2.]expr_j1, ..., [table_1.]expr_jm
	FROM table_2
	WHERE [table_2.]expr_x OPERATOR [table_1.]expr_y [{AND|OR} ...]
	[[GROUP BY expressions] 
	[HAVING [table_2.]expr_t OPERATOR [table_1.]expr_z [{AND|OR} ...]
]])

Pentru o subcerere corelată, subcererea se execută de mai multe ori, câte o dată pentru fiecare linie prelucrată de cererea principală, deci cererea interioară este condusă de cererea exterioară; Pașii de execuție a unei subcereri corelate sunt:

Deși o subcerere corelată se execută de mai multe ori, acest lucru nu implică că subcererile corelate sunt mai ineficiente decât subcererile necorelate.

Ex. 8. Să se afișeze angajații care au salariul peste valoarea medie a departamentului din care fac parte. Ex. 9. Să se mărească salariile angajaților cu 10% din salariul mediu și să se acorde tuturor angajaților un comision egal cu comisionul mediu pe departamentul din care face parte angajatul, numai pentru persoanele angajate înainte de 1-JUN-1981.

Subcereri pe tabelă temporară (în clauza FROM)

SELECT t1.expr_1, ..., t1.expr_n, t2.expr_1,..., t2.expr_m
FROM table_1 t1
	{,|[[INNER|CROSS|NATURAL]|[FULL|RIGHT|LEFT] [OUTER] ]] JOIN}
	(SELECT ti.expr_1, ... , ti.expr_s
	FROM table_2
	[WHERE nested_where_conditions]
	[[GROUP BY nested_expresions] 
		[HAVING nested_having_conditions]]
	) t2 
	[ON on_conditions]
	[WHERE where_conditions]
	[[GROUP BY expresions] 
		[HAVING having_conditions]]
[ORDER BY {COLUMNS|expressions|possition} [ASC|DESC]]

Ex. 10. Să se afle salariul maxim pentru fiecare departament.

Exerciții individuale

Să se selecteze numele angajatului, numele departamentului si salariul pentru angajatii care au salariul egal cu salariul minim din departamentul in care lucreaza. Ordonati dupa nume.

Rezolvati prin 4 metode: