Câte ceva despre subquery

O subinterogare poate apărea într-o clauză:

SELECT
FROM
WHERE

Sintaxa ar fi următoarea:

SELECT lista_selectare
FROM tabel
WHERE expr operator (
    SELECT lista_selectare
    FROM tabel
);

SELECT lista_selectare

FROM tabel

WHERE expr operator (

SELECT lista_selectare

FROM tabel

);

Pentru a putea exemplifica câteva aspecte despre subquery vom crea tabelă, o vom popula cu câteva date, astfel încât să fie cât mai sugestive exemplele.

Creare tabelă

DROP TABLE IF EXISTS angajati; 
CREATE TABLE `angajati` (
  `id` int(3) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `nume` varchar(50) DEFAULT NULL,
  `luna` int(2) NOT NULL,
  `salariu` decimal(6,2) DEFAULT NULL,
  `departament` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_romanian_ci;

DROP TABLE IF EXISTS angajati;

CREATE TABLE `angajati` (

`id` int(3) NOT NULL PRIMARY KEY AUTO_INCREMENT,

`nume` varchar(50) DEFAULT NULL,

`luna` int(2) NOT NULL,

`salariu` decimal(6,2) DEFAULT NULL,

`departament` varchar(50) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_romanian_ci;

Populare cu date

INSERT INTO `angajati` (`nume`, `luna`, `salariu`, `departament`) VALUES
('Angajat1', 1, 5000.00, 'IT'),
('Angajat2', 1, 6000.00, 'IT'),
('Angajat3', 1, 7000.00, 'Vanzari'),
('Angajat4', 1, 7500.00, 'Vanzari'),
('Angajat5', 1, 8000.00, 'HR'),
('Angajat3', 2, 5000.00, 'Vanzari'),
('Angajat4', 2, 3200.00, 'Vanzari'),
('Angajat1', 3, 5400.00, 'IT'),
('Angajat2', 3, 6100.00, 'IT'),
('Angajat3', 3, 7000.00, 'Vanzari');

INSERT INTO `angajati` (`nume`, `luna`, `salariu`, `departament`) VALUES

('Angajat1', 1, 5000.00, 'IT'),

('Angajat2', 1, 6000.00, 'IT'),

('Angajat3', 1, 7000.00, 'Vanzari'),

('Angajat4', 1, 7500.00, 'Vanzari'),

('Angajat5', 1, 8000.00, 'HR'),

('Angajat3', 2, 5000.00, 'Vanzari'),

('Angajat4', 2, 3200.00, 'Vanzari'),

('Angajat1', 3, 5400.00, 'IT'),

('Angajat2', 3, 6100.00, 'IT'),

('Angajat3', 3, 7000.00, 'Vanzari');

1. Subquery scalar (Subquery cu un singur rând)

Un subquery scalar returnează zero sau un singur rezultat.

SELECT nume, (
    SELECT AVG(salariu) 
    FROM angajati 
    WHERE departament = 'IT'
) AS media_salariu
FROM angajati;

SELECT nume, (

SELECT AVG(salariu)

FROM angajati

WHERE departament = 'IT'

) AS media_salariu

FROM angajati;

În acest exemplu, subquery-ul scalar calculează media salariilor pentru departamentul „IT”, iar rezultatul este folosit în cadrul interogării principale pentru a obține media salarială pentru fiecare angajat.

Dacă se doreşte redarea salariului mediu pentru fiecare angajat, se poate folosi următorul script:

SELECT nume, (
    SELECT AVG(salariu) 
    FROM angajati AS a2 
    WHERE a2.nume = a1.nume
) AS media_salariu
FROM angajati AS a1
GROUP BY nume;

SELECT nume, (

SELECT AVG(salariu)

FROM angajati AS a2

WHERE a2.nume = a1.nume

) AS media_salariu

FROM angajati AS a1

GROUP BY nume;

Dacă dorim să exemplificăm interogarea în care să fie redat angajatul cu salariul maxim pe departament, se poate folosi interogarea următoare.

SELECT departament, 
    (SELECT nume 
     FROM angajati AS sub 
     WHERE sub.departament = ang.departament 
     ORDER BY sub.salariu DESC LIMIT 1) AS angajat_salar_max
FROM angajati AS ang
GROUP BY departament;

SELECT departament,

(SELECT nume

FROM angajati AS sub

WHERE sub.departament = ang.departament

ORDER BY sub.salariu DESC LIMIT 1) AS angajat_salar_max

FROM angajati AS ang

GROUP BY departament;

În acest exemplu, subquery-ul returnează un singur rând cu numele angajatului care are cel mai mare salariu pentru fiecare departament. Acest rezultat este utilizat în clauza SELECT principală pentru a obține o listă a departamentelor și angajatului cu salariul maxim pentru fiecare departament.

Este important de menţionat că astfel de subquery-uri implică adesea o performanță mai scăzută și pot fi necesare ajustări pentru a gestiona eficient seturi de date mari.

2. Subquery pe rând

SELECT nume, salariu
FROM angajati
WHERE (departament, salariu) = (
    SELECT departament, MAX(salariu) 
    FROM angajati 
    WHERE departament = 'Vanzari' 
    GROUP BY departament
);

SELECT nume, salariu

FROM angajati

WHERE (departament, salariu) = (

SELECT departament, MAX(salariu)

FROM angajati

WHERE departament = 'Vanzari'

GROUP BY departament

);

În acest exemplu, subquery-ul pe rând returnează perechile (departament, salariu) cu salariile maxime pentru un departament anume. Această subinterogare este utilizată în clauza WHERE pentru a selecta angajații cu cele mai mari salarii din departament, putând reda un singur rând.

Pentru a reda mai mult de un rând, este necesar operatorul IN, în loc de folosirea operatorului de egalitate (=), astfel încăt să se verifice dacă o pereche (departament, salariu) se află în rezultatul subquery-ului. Acelaşi tip de interogare este cea pe tabel.

SELECT nume, salariu
FROM angajati
WHERE (departament, salariu) IN (
    SELECT departament, MAX(salariu) 
    FROM angajati 
    GROUP BY departament
);

SELECT nume, salariu

FROM angajati

WHERE (departament, salariu) IN (

SELECT departament, MAX(salariu)

FROM angajati

GROUP BY departament

);

Un exemplu interesant de multiple-row subquery pentru tabela dată ar putea implica obținerea tuturor angajaților care au un salariu mai mare decât salariul mediu din departamentul lor ar putea fi următorul:

SELECT nume, salariu, departament
FROM angajati a 
WHERE salariu > (
    SELECT AVG(salariu) 
    FROM angajati AS sub 
    WHERE sub.departament = a.departament
);

SELECT nume, salariu, departament

FROM angajati a

WHERE salariu > (

SELECT AVG(salariu)

FROM angajati AS sub

WHERE sub.departament = a.departament

);

În acest exemplu, subquery-ul multiple-row calculează media salariilor pentru fiecare departament în parte. Apoi, în clauza WHERE a interogării principale, verificăm dacă salariul angajatului este mai mare decât media salariilor din departamentul său.

Această interogare va returna toți angajații care au un salariu mai mare decât media salariilor din propriul departament.

3. Subinterogare pe coloană

Returnează un set de coloane.

SELECT nume, salariu
FROM angajati
WHERE salariu IN (
    SELECT salariu 
    FROM angajati 
    WHERE departament = 'Vanzari'
);

SELECT nume, salariu

FROM angajati

WHERE salariu IN (

SELECT salariu

FROM angajati

WHERE departament = 'Vanzari'

);

În acest exemplu, subquery-ul pe coloană returnează toate salariile din departamentul „Vanzari”, iar rezultatul este utilizat pentru a selecta angajații din tabelul principal cu salariile corespunzătoare.

Pentru redarea multiple-column subquery pentru fiecare angajat, a numelui și salariului angajatului cu salariul maxim din același departament.

SELECT nume, salariu, departament, (
    SELECT nume 
    FROM angajati AS sub 
    WHERE sub.departament = a.departament 
    ORDER BY sub.salariu DESC LIMIT 1
) AS angajat_cu_salariu_max, (
    SELECT salariu 
    FROM angajati AS sub 
    WHERE sub.departament = a.departament 
    ORDER BY sub.salariu DESC LIMIT 1
) AS salariu_max
FROM angajati AS a;

SELECT nume, salariu, departament, (

SELECT nume

FROM angajati AS sub

WHERE sub.departament = a.departament

ORDER BY sub.salariu DESC LIMIT 1

) AS angajat_cu_salariu_max, (

SELECT salariu

FROM angajati AS sub

WHERE sub.departament = a.departament

ORDER BY sub.salariu DESC LIMIT 1

) AS salariu_max

FROM angajati AS a;

Un subquery returnează numele angajatului cu cel mai mare salariu din același departament, iar celălalt subquery returnează salariul respectiv.

4. Subquery pe tabel

SELECT nume, departament
FROM angajati
WHERE (departament, salariu) IN (
    SELECT departament, MAX(salariu) 
    FROM angajati 
    GROUP BY departament
);

SELECT nume, departament

FROM angajati

WHERE (departament, salariu) IN (

SELECT departament, MAX(salariu)

FROM angajati

GROUP BY departament

);

Un subquery pe tabel returnează un set întreg de rezultate sub forma unui tabel temporar. În acest exemplu, subquery-ul table returnează departamentul și salariul maxim pentru fiecare departament. Această subinterogare este folosită în clauza WHERE pentru a selecta angajații cu cele mai mari salarii din fiecare departament.

Nested subquery

Un exemplu de nested subquery pentru tabela dată ar putea implica obținerea numelui angajatului cu cel mai mare salariu din departamentul cu salariul mediu maxim.

SELECT nume, salariu, departament
FROM angajati
WHERE (departament, salariu) = (
    SELECT departament, MAX(salariu) 
    FROM angajati 
    WHERE salariu = (
        SELECT MAX(salariu) 
        FROM angajati
    ) GROUP BY departament
);

SELECT nume, salariu, departament

FROM angajati

WHERE (departament, salariu) = (

SELECT departament, MAX(salariu)

FROM angajati

WHERE salariu = (

SELECT MAX(salariu)

FROM angajati

) GROUP BY departament

);

În acest exemplu, avem un subquery nested (imbricat) care calculează salariul maxim global, apoi o altă subquery nested care calculează salariul maxim din fiecare departament pentru acea valoare globală. Interogarea principală compară salariul și departamentul pentru a găsi angajatul cu cel mai mare salariu în departamentul cu salariul mediu maxim.

L	Ma	Mi	J	V	S	D
				1	2	3
4	5	6	7	8	9	10
11	12	13	14	15	16	17
18	19	20	21	22	23	24
25	26	27	28	29	30	31

Câte ceva despre subquery

Creare tabelă

Populare cu date

1. Subquery scalar (Subquery cu un singur rând)

2. Subquery pe rând

3. Subinterogare pe coloană

4. Subquery pe tabel

Nested subquery

Author: Ovidiu.S

Lasă un răspuns Anulează răspunsul

Utilităţi

Categorii

Pagini

Articole recente

Arhive

Proiecte

Creare tabelă

Populare cu date

1. Subquery scalar (Subquery cu un singur rând)

2. Subquery pe rând

3. Subinterogare pe coloană

4. Subquery pe tabel

Nested subquery

Author: Ovidiu.S

Lasă un răspuns Anulează răspunsul

Utilităţi

Categorii

Pagini

Articole recente

Arhive

Proiecte

Etichete