Subinterogări

Subinterogările reprezintă expresii SELECT încorporate în alte interogări SQL. Să examinăm un exemplu simplu de utilizare a subinterogărilor.

De exemplu, să creăm tabele pentru produse și comenzi:

CREATE TABLE Products
(
   Id INT AUTO_INCREMENT PRIMARY KEY,
   ProductName VARCHAR(30) NOT NULL,
   Manufacturer VARCHAR(20) NOT NULL,
   ProductCount INT DEFAULT 0,
   Price DECIMAL NOT NULL
);
CREATE TABLE Orders
(
   Id INT AUTO_INCREMENT PRIMARY KEY,
   ProductId INT NOT NULL,
   ProductCount INT DEFAULT 1,
   CreatedAt DATE NOT NULL,
   Price DECIMAL NOT NULL,
   FOREIGN KEY (ProductId) REFERENCES Products(Id) ON DELETE CASCADE
);

Tabela Orders conține date despre produsele achiziționate din tabela Products.

Să adăugăm unele date în tabele:

INSERT INTO Products (ProductName, Manufacturer, ProductCount, Price)
VALUES ('iPhone X', 'Apple', 2, 76000),
('iPhone 8', 'Apple', 2, 51000),
('iPhone 7', 'Apple', 5, 42000),
('Galaxy S9', 'Samsung', 2, 56000),
('Galaxy S8', 'Samsung', 1, 46000),
('Honor 10', 'Huawei', 2, 26000),
('Nokia 8', 'HMD Global', 6, 38000);

INSERT INTO Orders (ProductId, CreatedAt, ProductCount, Price)
VALUES
(
   (SELECT Id FROM Products WHERE ProductName='Galaxy S8'),
   '2018-05-21',
   2,
   (SELECT Price FROM Products WHERE ProductName='Galaxy S8')
),
(
   (SELECT Id FROM Products WHERE ProductName='iPhone X'),
   '2018-05-23',  
   1,
   (SELECT Price FROM Products WHERE ProductName='iPhone X')
),
(
   (SELECT Id FROM Products WHERE ProductName='iPhone 8'),
   '2018-05-21',  
   1,
   (SELECT Price FROM Products WHERE ProductName='iPhone 8')
);

La adăugarea datelor în tabela Orders, sunt folosite subinterogările. De exemplu, prima comandă a fost făcută pentru produsul Galaxy S8. Prin urmare, în tabela Orders trebuie să salvăm informații despre comanda în care câmpul ProductId indică Id-ul produsului Galaxy S8, iar câmpul Price - prețul acestuia.

Dar în momentul redactării interogării, pot fi necunoscute atât Id-ul cumpărătorului, cât și Id-ul produsului și prețul produsului. În acest caz, se poate efectua o subinterogare sub forma:

(SELECT Price FROM Products WHERE ProductName='iPhone 8')

Subinterogarea realizează o comandă SELECT și este înconjurată de paranteze. În acest caz, la adăugarea unui singur produs, sunt efectuate două subinterogări.

Fiecare subinterogare returnează o valoare scalară, cum ar fi un identificator numeric.

În exemplul de mai sus, subinterogările au fost executate asupra unei alte tabele, dar pot fi executate și asupra aceleiași tabele pentru care este apelată interogarea principală. De exemplu, să găsim produsele din tabela Products care au prețul minim:

SELECT * FROM Products
WHERE Price = (SELECT MIN(Price) FROM Products);

Sau să găsim produsele ale căror prețuri sunt mai mari decât media:

SELECT * FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);

Subinterogările corelate și necorelate

Subinterogările pot fi de două tipuri: corelate și necorelate. În exemplele anterioare, comenzile SELECT efectiv au efectuat o singură subinterogare pentru toate rândurile extrase de comandă.

De exemplu, subinterogarea returnează prețul minim sau mediu, care nu se schimbă, indiferent câte rânduri alege comanda principală. Cu alte cuvinte, rezultatul subinterogării nu depinde de rândurile extrase de comanda principală. Și astfel de subinterogări sunt executate o singură dată pentru întreaga comandă externă.

Cu toate acestea, se pot folosi și subinterogări corelate (correlated subquery), ale căror rezultate depind de rândurile extrase de comanda principală.

De exemplu, să selectăm toate comenzile din tabela Orders, adăugând informații despre produs:

SELECT  CreatedAt, Price,
       (SELECT ProductName FROM Products
       WHERE Products.Id = Orders.ProductId) AS Product
FROM Orders;

În acest caz, pentru fiecare rând din tabela Orders se va executa o subinterogare, rezultatul căreia depinde de coloana ProductId. Și fiecare subinterogare poate returna date diferite.

Correlația unei subinterogări poate să se realizeze și în cadrul aceleiași tabele la care se efectuează interogarea principală. De exemplu, să selectăm din tabela Products produsele ale căror prețuri sunt mai mari decât media prețurilor pentru același producător:

SELECT ProductName,
      Manufacturer,
      Price,
       (SELECT AVG(Price) FROM Products AS SubProds
        WHERE SubProds.Manufacturer=Prods.Manufacturer)  AS AvgPrice
FROM Products AS Prods
WHERE Price >
   (SELECT AVG(Price) FROM Products AS SubProds
    WHERE SubProds.Manufacturer=Prods.Manufacturer);

Aici sunt definite două subinterogări corelate. Prima subinterogare definește specificația coloanei AvgPrice. Aceasta va fi executată pentru fiecare rând extras din tabela Products. Subinterogarea primește producătorul produsului și, pe baza acestuia, selectează prețul mediu pentru produsele aceluiași producător.

Și, deoarece producătorul la produse poate să difere, rezultatul subinterogării poate varia în fiecare caz.

A doua subinterogare este similară, dar este folosită pentru filtrarea rândurilor extrase din tabela Products. De asemenea, va fi executată pentru fiecare rând.

Pentru a evita ambiguitatea în timpul filtrării în subinterogare în timpul comparării producătorilor (SubProds.Manufacturer=Prods.Manufacturer), pentru selecția externă, este setat un alias Prods, iar pentru selecția din subinterogări este definit un alias SubProds.

Trebuie să luați în considerare că subinterogările corelate sunt executate pentru fiecare înregistrare individuală extrasa, astfel încât execuția acestor subinterogări poate încetini în ansamblu executarea întregii interogări.