Які два типи підзапитів існують?

Підзапити являють собою вирази SELECT, які вбудовані в інші запити SQL. Розглянемо найпростіший приклад застосування підзапитів.

Наприклад, створимо таблиці для товарів та замовлень:

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(TE);

Таблиця Orders містить дані про куплені товари з таблиці Products.

Додамо до таблиці деякі дані:

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='Galax ) ), ( (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'));

При додаванні даних до таблиці Orders використовуються підзапити. Наприклад, перше замовлення було зроблено на товар Galaxy S8. Відповідно до таблиці Orders нам треба зберегти інформацію про замовлення, де поле ProductId вказує на Id товару Galaxy S8, поле Price – на його ціну. Але на момент написання запиту нам може бути невідомим ні Id покупця, ні Id товару, ні ціна товару.У цьому випадку можна виконати підзапит у вигляді

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

Підзапит виконує команду SELECT і полягає у дужках. У цьому випадку при додаванні одного товару виконується два подзапроса. Кожен підзапит повертає одного скалярного значення, наприклад, числовий ідентифікатор.

У прикладі вище підзапити виконувались до іншої таблиці, але можуть виконуватися і до тієї ж, на яку викликається основний запит. Наприклад, знайдемо товари з таблиці Products, які мають мінімальну ціну:

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

Або знайдемо товари, ціна яких вища за середню:

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

Корелюючі та некорелюючі підзапити

Підзапити бувають корелюючими та некорелюючими. У прикладах вище команди SELECT фактично виконували один підзапит всіх рядків, що витягуються командою. Наприклад, підзапит повертає мінімальну чи середню ціну, яка не зміниться, скільки б ми рядків не обирали в основному запиті. Тобто результат підзапиту не залежав від рядків, які вибираються переважно запитом. І такий підзапит виконується один раз для всього зовнішнього запиту.

Але також можна використовувати корелюючі підзапити (correlated subquery), результати яких залежать від рядків, які вибираються в основному запиті.

Наприклад, виберемо всі замовлення з таблиці Orders, додавши до них інформацію про товар:

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

У цьому випадку для кожного рядка з таблиці Orders буде виконуватися запит, результат якого залежить від стовпця ProductId. І кожен підзапит може повертати різні дані.

Корелюючий підзапит може виконуватися і тієї ж таблиці, до якої виконується основний запит.Наприклад, виберемо з таблиці Products ті товари, вартість яких вища за середню ціну товарів для даного виробника:

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

Тут визначено два корелюючі підзапити. Перше підзапит визначає специфікацію стовпця AvgPrice. Він буде виконуватися для кожного рядка, що витягується з таблиці Products. У підзапит передається виробник товару та на його основі вибирається середня ціна для товарів саме цього виробника. І оскільки виробник у товарів може відрізнятися, то результат підзапиту в кожному випадку також може відрізнятися.

Друге підзапит аналогічний, тільки він використовується для фільтрації вилучених з таблиці Products. І він буде виконуватися для кожного рядка.

Щоб уникнути двоїстості при фільтрації в підзапиті при порівнянні виробників (SubProds.Manufacturer=Prods.Manufacturer) для зовнішньої вибірки встановлено псевдонім Prods, а для вибірки із підзапитів визначено псевдонім SubProds.

Слід враховувати, що коррелирующие підзапити виконуються кожної окремої рядки вибірки, то виконання таких підзапитів може уповільнювати виконання всього запиту загалом.

Підзапити в SQL (вкладені запити SQL)

o В інструкції SELECT;
o В інструкції FROM;
o В умовах WHERE.

Підзапит може бути вкладений в інструкції SELECT, INSERT, UPDATE або DELETE, а також в інше підзапит;
Підзапит зазвичай додається за умови WHERE оператора SQL SELECT ;
Можна використовувати оператори порівняння, такі як >,
Підзапит також називається внутрішнім запитом. Оператор, що містить підзапит, також називається зовнішнім;
Внутрішній запит виконується перед батьківським запитом, щоб результати його роботи були передані зовнішньому.

Підзапит можна використовувати в інструкціях SELECT, INSERT, DELETE або UPDATE для виконання наступних завдань:

Порівняння виразу з результатом запиту;
Визначення того, чи включено вираз до результатів запиту;
Перевірка того, чи вибирає запит будь-які рядки.

Підзапит SQL (внутрішній запит) виконується перед виконанням основного запиту (зовнішнього запиту);
Основний запит використовує результат виконання підзапиту.

Підзапити SQL-приклади

У цьому розділі ми розглянемо, як використовувати підзапити. У нас є дві таблиці: ' student ' і ' marks ' із загальним полем ' StudentID ':

Тепер потрібно скласти запит, який визначає всіх студентів, які отримують кращі позначки, ніж студент зі StudentID – «V002». Але ми не знаємо позначок студента «V002».

Тому потрібно скласти два SQL підзапити в Select. Один запит повертає позначки ( зберігаються в полі Total_marks ) для V002, а другий запит вибирає учнів, які отримують кращі оцінки, ніж результат першого запиту.

SELECT * FROM `marks` WHERE studentid = 'V002';

Результатом запиту буде 80 .

Використовуючи результат цього запиту, ми написали ще один запит, щоб визначити учнів, які отримують оцінки краще, ніж 80 .

SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks >80;

Два наведені запити визначають студентів, які отримують краще оцінки, ніж студент StudentID «V002» (Abhay).

Можна об'єднати ці два запити, вклавши один запит до іншого. Підзапит – це запит усередині круглих дужок. Розглянемо підзапит у SQL приклад :

SELECT a.studentid, a.name, b.total_marks FROM student a, marks b WHERE a.studentid = b.studentid AND b.total_marks > (SELECT total_marks FROM marks WHERE studentid = 'V002');

Графічне представлення підзапиту SQL:

Підзапити SQL (вкладені запити SQL): загальні правила

Нижче наведено синтаксис підзапиту:

(SELECT [DISTINCT] аргументи_підзапиту_для_відбору FROM < ім'я_таблиці | ім'я_подання >. [WHERE умови_пошуку] [GROUP BY вираз_об'єднання [,вираз_об'єднання] . ] [HAVING умови_пошуку])

Підзапити SQL (вкладені запити SQL): рекомендації щодо використання

Нижче наведено низку рекомендацій, які слід виконувати за допомогою SQL підзапитів:

Підзапит має бути укладений у круглі дужки;
Підзапит має вказуватися в правій частині оператора порівняння;
Підзапити не можуть обробляти свої результати, тому до підзапиту не може бути додано умову ORDER BY ;
Використовуйте однорядкові оператори з однорядковими запитами;
Якщо підзапит повертає у зовнішній запит значення null, зовнішній запит не повертатиме жодних рядків при використанні операторів порівняння за умови WHERE.

Підзапити SQL (вкладені запити SQL) - основні типи

Однорядковий підзапит: повертає нуль або один рядок;
Багаторядковий підзапит: повертає один або кілька рядків;
Багатостовпцевий підзапит: повертає один або кілька стовпців;
Кореловані підзапити: вказують один або кілька стовпців у зовнішній інструкції SQL. Такий підзапит називається корельованим, оскільки він пов'язаний із зовнішньою інструкцією SQL;
Вкладені підзапити : підзапити розміщені в іншому підзапиті.

Також можна використовувати підзапит всередині інструкцій INSERT, UPDATE та DELETE.

Підзапити SQL з інструкцією INSERT

Інструкція INSERT може використовуватися з підзапитами SQL.

INSERT INTO имя_таблицы [ (стовпець1 [, стовпець2 ]) ] SELECT [ * | стовпець1 [, стовпець2 ] FROM таблиця1 [, таблиця2 ] [ WHERE VALUE OPERATOR ];

Якщо ми хочемо вставити замовлення з таблиці 'orders', для яких у таблиці neworder значення advance_amount становить 2000 або 1500, можна використовувати наступний код SQL:

Приклад таблиці: orders

ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION ---------- ---------- -------------- --------- --------------- --------------- ----------------- 200114 3500 2000 15-AUG-08 C00002 A008 200122 2500 400 16-SEP-08 C00003 A004 200118 500 100 20-JUL-08 C00023 A006 200119 4000 700 16-SEP-08 C00007 A010 23-SEP-08 C00008 A004 200130 2500 400 30-JUL-08 C00025 A011 200134 4200 1800 25-SEP-08 C00004 A005 200108 C00008 A004 200103 1500700 15-MAY-08 C00021 A005 200105 2500500 18-JUL-08 C00025 A011 200109 3500 C000 800 200101 3000 1000 15-JUL-08 C00001 A008 200111 1000 300 10-JUL-08 C00020 A008 200104 1500 500 13-MAR-08 000 700 20-APR-08 C00005 A002 200125 2000 600 10-OCT-08 C00018 A005 200117 800 200 20-OCT-08 C00014 A001 2006 C00022 A002 200120 500 100 20-JUL-08 C00009 A002 200116 500 100 13-JUL-08 C00010 A009 200124 500 100 20-00 200126 500 100 24-JUN-08 C00022 A002 200129 2500 500 20-JUL-08 C00024 A006 200127 2500 400 20-JUL-08 C00 1500 20-JUL-08 C00009 A002 200135 2000 800 16-SEP-08 C00007 A010 200131 900 150 26-AUG-08 C00012 A012 200 C00009 A002 200100 1000 600 08-JAN-08 C00015 A003 200110 3000 500 15-APR-08 C00019 A010 200107 4500 C000 200112 2000 400 30-MAY-08 C00016 A007 200113 4000 600 10-JUN-08 C00022 A002 200102 2000 300 25-MAY-08 C00

INSERT INTO neworder SELECT * FROM orders WHERE advance_amount in(2000,1500);

Підзапити SQL з інструкцією UPDATE

В інструкції UPDATE можна встановити нове значення стовпця, що дорівнює результату, що повертається однорядковим підзапитом.

UPDATE таблиця SET ім'я_стовпця = нове_значення [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]

Якщо ми хочемо змінити параметри ord_date в таблиці 'neworder' з '15-JAN-10', для яких різниця між ord_amount та advance_amount менша за мінімальну ord_amount в таблиці 'orders', то можна використовувати наступний код SQL:

Приклад таблиці: neworder ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION ---------- ---------- -------------- ----- ---- --------------- --------------- ---------------- - 200114 3500 2000 15-AUG-08 C00002 A008 200122 2500 400 16-SEP-08 C00003 A004 200118 500 100 20-JUL-08 C00023 A006 200119 4000 700 16-SEP-08 C00 600 23-SEP-08 C00008 A004 200130 2500 400 30-JUL-08 C00025 A011 200134 4200 1800 25-SEP-08 C00004 A005 200 15-FEB-08 C00008 A004 200103 1500 700 15-MAY-08 C00021 A005 200105 2500 500 18-JUL-08 C00025 A011 200108 0 C00011 A010 200101 3000 1000 15-JUL-08 C00001 A008 200111 1000300 10-JUL-08 C00020 A008 200104 1500 C000 200106 2500 700 20-APR-08 C00005 A002 200125 2000 600 10-OCT-08 C00018 A005 200117 800 200 20-OCT-08 C00 100 16-SEP-08 C00022 A002 200120 500 100 20-JUL-08 C00009 A002 200116 500 100 13-JUL-08 C00010 A009 20011 C00017 A007 200126 500 100 24-JUN-08 C00022 A002 200129 2500 500 20-JUL-08 C00024 A006 200127 2500 4005 200128 3500 1500 20-JUL-08 C00009 A002 200135 2000 800 16-SEP-08 C00007 A010 200131 900 150 26-AUG-01 C00 400 29-JUN-08 C00009 A002 200100 1000 600 08-JAN-08 C00015 A003 200110 3000 500 15-APR-08 C00019 A010 2000 C00007 A010 200112 2000 400 30-MAY-08 C00016 A007 200113 4000 600 10-JUN-08 C00022 A002 200102 2000 300 2001

UPDATE neworder SET ord_date='15-JAN-10' WHERE ord_amount-advance_amount< (SELECT MIN(ord_amount) FROM orders);

Підзапити SQL з інструкцією DELETE

Нижче наводиться синтаксис та приклад використання SQL підзапитів з інструкцією DELETE.

DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]

Якщо потрібно видалити замовлення з таблиці neworder, для яких advance_amount менше максимального значення advance_amount з таблиці orders, можна використовувати наступний код SQL:

Приклад таблиці: neworder

ORD_NUM ORD_AMOUNT ADVANCE_AMOUNT ORD_DATE CUST_CODE AGENT_CODE ORD_DESCRIPTION ---------- ---------- -------------- --------- --------------- --------------- ----------------- 200114 3500 2000 15-AUG-08 C00002 A008 200122 2500 400 16-SEP-08 C00003 A004 200118 500 100 20-JUL-08 C00023 A006 200119 4000 700 16-SEP-08 C00007 A010 23-SEP-08 C00008 A004 200130 2500 400 30-JUL-08 C00025 A011 200134 4200 1800 25-SEP-08 C00004 A005 200108 C00008 A004 200103 1500700 15-MAY-08 C00021 A005 200105 2500500 18-JUL-08 C00025 A011 200109 3500 C000 800 200101 3000 1000 15-JUL-08 C00001 A008 200111 1000 300 10-JUL-08 C00020 A008 200104 1500 500 13-MAR-08 000 700 20-APR-08 C00005 A002 200125 2000 600 10-OCT-08 C00018 A005 200117 800 200 20-OCT-08 C00014 A001 2006 C00022 A002 200120 500 100 20-JUL-08 C00009 A002 200116 500 100 13-JUL-08 C00010 A009 200124 500 100 20-00 200126 500 100 24-JUN-08 C00022 A002 200129 2500 500 20-JUL-08 C00024 A006 200127 2500 400 20-JUL-08 C00 1500 20-JUL-08 C00009 A002 200135 2000 800 16-SEP-08 C00007 A010 200131 900 150 26-AUG-08 C00012 A012 200 C00009 A002 200100 1000 600 08-JAN-08 C00015 A003 200110 3000 500 15-APR-08 C00019 A010 200107 4500 C000 200112 2000 400 30-MAY-08 C00016 A007 200113 4000 600 10-JUN-08 C00022 A002 200102 2000 300 25-MAY-08 C00

DELETE FROM neworder WHERE advance_amount< (SELECT MAX(advance_amount) FROM orders);

Які два типи підзапитів існують