Оволодіння SQL підзапитами: Загальний посібник

Чи ви коли-небудь відчували себе переповненими складністю управління та маніпулювання базами даних? Можливо, ви працювали з SQL (Structured Query Language), але не дійшли до того етапу, коли вважали, що зможете вирішувати реальні бізнес-проблеми за допомогою своїх SQL-запитів. Стрибок від рівня початківця в розумінні SQL до “ніндзя” SQL не такий великий, як може здаватися. Ця серія статей про SQL буде присвячена тому, щоб допомогти людям зробити цей стрибок і вивчити SQL простим та комплексним способом.

Сьогодні наша тема – одна з тих речей, які відрізняють початківців в області SQL від спеціалістів SQL: підзапити(subqueries). Однією з найпотужніших можливостей SQL є можливість використання підзапитів, які подібні до запитів всередині запитів, значно розширюючи ваші можливості до обробки даних. В цій статті розглянемо різні аспекти SQL-підзапитів, включаючи основні підзапити (basic subqueries), вкладені підзапити (nested subqueries), взаємодіючі підзапити (correlated subqueries) та підзапити в розділі FROM (subqueries in the FROM clause).

Основні підзапити (Basic Subqueries)

В SQL, підзапит – це запит, який вкладений у головний запит. Підзапити можуть повертати окремі значення або список записів; ці значення потім можуть використовуватися головним запитом для порівняння. Основний підзапит – це автономний запит, який може працювати незалежно і вбудований у інший запит. Це автономна одиниця, яка генерує набір результатів, який потім використовується зовнішнім запитом. В основному, це схоже на візит на ринок для отримання конкретних товарів (підзапит), а потім використання цих товарів для приготування страви (зовнішній запит).

Вони використовуються для відповіді на складні питання, які потребують даних з кількох таблиць, і вони можуть бути потужним інструментом у поєднанні з операторами, такими як IN, EXISTS, ANY, ALL та інші.

Основна структура підзапиту виглядає наступним чином:

SELECT column_name(s)
FROM table_name
WHERE column_name operator (
      SELECT column_name
      FROM table_name
      WHERE condition);

Візьмемо приклад із простого набору даних одного з найбільш базових (і поширених) типів реляційних баз даних. Припустимо, що у нас є дві таблиці: Orders та Customers. Таблиця Orders містить всі замовлення, зроблені клієнтами, тоді як таблиця Customers містить всі дані про клієнтів.

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

OrderID	Product	Quantity	CustomerID
1	Яблука	10	3
2	Банани	20	1
3	Виноград	15	2
4	Яблука	5	1
5	Виноград	10	3

Наша таблиця Customers просто відповідає імена клієнтів їхнім відповідним ідентифікаторам клієнта і виглядає приблизно так:

CustomerID	Name
1	Аліса
2	Боб
3	Керол

Ось як ми можемо змінити основну структуру підзапиту, показану вище, для виконання цього завдання:

SELECT OrderID, Product, Quantity
FROM Orders
WHERE CustomerID = (
      SELECT CustomerID
      FROM Customers
      WHERE Name = ‘Alice’);

Спочатку виконується підзапит (частина у дужках).

(SELECT CustomerID FROM Customers WHERE Name = ‘Alice’) витягує CustomerID для Alice, і зовнішній запит використовує його для знаходження всіх продуктів, замовлених Alice. Отриманий тут CustomerID буде дорівнювати 1, оскільки це відповідний ідентифікатор для Alice.

Отже, значення 1 стане результатом підзапиту, і повний запит, фактично, “зменшиться” до наступного:

SELECT OrderID, Product, Quantity
FROM Orders
WHERE CustomerID = 1;

Наступним етапом виконується основний запит. Він вибирає OrderID, Product та Quantity з таблиці Orders, де CustomerID такий самий, як і той, що повернутий підзапитом (CustomerID для Alice = 1).

“Оператор” в оригінальному запиті може бути будь-яким оператором порівняння, таким як =, >, <, ≥, <= і т.д., залежно від конкретної умови, яку ви хочете застосувати.

Загалом цей SQL-запит використовує підзапит для отримання записів з таблиці на підставі умови, яка залежить від інформації з іншої таблиці.

Тільки застереження: завжди переконайтеся, що ваш підзапит повертає одне значення, коли використовуєте оператори порівняння, такі як =, >, < і т.д. Якщо підзапит може повертати більше одного результату, розгляньте можливість використання операторів IN, ANY, або ALL, знову ж таки, залежно від конкретних вимог вашого запиту.

Чим корисні підзапити

У попередньому прикладі, здається, що ми могли б просто використати зменшений запит і “жорстко закодувати” CustomerID = 1, але насправді ми можемо не знати цю інформацію на початку. У програмуванні завжди хочемо включати найбільш “загальні” або “надійні” засоби написання коду. Якщо ми не знали ID клієнта заздалегідь (або, точніше, хотіли б прямо шукати відповідний ID на основі імені, такого як “Alice”), наш підзапит дійсно допомагає! У реальному світі ми, ймовірно, додали б ще один рівень абстракції, так щоб отримані таблиці автоматично генерувались програмно для будь-якого імені, а не лише для “Alice”.

Вкладені підзапити

Вкладені підзапити – це підзапити, які містять інший підзапит. Їх часто використовують, коли результати зовнішнього запиту залежать від результату підзапиту, який сам залежить від іншого підзапиту. Внутрішній підзапит запускається першим, після чого його результат використовується в наступному зовнішньому підзапиті, і так далі, поки можна обробити кінцевий зовнішній запит.

Давайте порівняємо роботу з SQL-підзапитами з українським народним обрядом вишивання. У вишиванні кожен вишитий елемент може представляти окремий зразок або мотив, і вони можуть вкладатися один у одного, створюючи унікальний та вишуканий дизайн.

Аналогічно, у SQL-підзапитах ви можете мати основний запит, який представляє собою загальну структуру запитання, а підзапити вкладаються в цей основний запит, надаючи додаткові деталі або уточнення. Такий підхід дозволяє створювати складні запити та отримувати потрібну інформацію, подібно тому, як вишиванням можна створити унікальний та красивий візерунок.

Case Study: Вкладений підзапит для топ-клієнтів

Іноді в бізнес-контексті ми хочемо зрозуміти, які клієнти є нашими найбільш “вірними” або найбільш “частими”. Ми можемо бажати порівняти деяких клієнтів в порівнянні з іншими клієнтами або з нашим клієнтським пулом, використовуючи статистичні показники.

Припустимо, ми хочемо зрозуміти, які з наших клієнтів є “топ-клієнтами”, визначеними як ті, які роблять “вище середнього” кількість замовлень. Як ми можемо використати SQL для виокремлення інформації про клієнтів, які купують більше товарів, ніж “середній” клієнт?

Спочатку нам потрібно визначити, “Хто такий середній клієнт?” У цьому випадку у нас є обмежена кількість інформації, тому найкращим “метрикою” для оцінки вірності клієнта буде кількість придбаних товарів. Таким чином, ми хочемо взяти AVG(Quantity) по всіх наших замовленнях, водночас відстежуючи, які клієнти здійснили ці покупки.

Давайте на швидку руку подивимося на кінцевий запит і потім розглянемо його, щоб розібратися:

SELECT Name
FROM Customers
WHERE CustomerID IN (
    SELECT CustomerID
    FROM Orders
    WHERE Quantity > (
        SELECT AVG(Quantity)
        FROM Orders
    )
);

В даному випадку у нас фактично два набори дужок вкладених одне в одне. Таким чином, це “підзапит всередині підзапиту” або вкладений підзапит. Щоб зрозуміти, як обробляється цей тип команди SQL-інтерпретатором, варто пригадати наступний принцип:

Внутрішні підзапити оцінюються перед зовнішніми запитами.

Внутрішні Запити Перед Зовнішніми Запитами

Отже, найкращий спосіб розглядати цей запит – це розуміти логіку запиту:

-----------  1)Inner Query 
----------     Get the Average Quantities
-------  2)Outer Query
-------    Get the Customer IDs of customers WHERE they buy more than average
----   3)Outermost Query
----     Get the Customer names of the respective CustomerIDs

Тепер, коли ми визначили основні частини компонентів нашого запиту, ми захочемо впорядкувати запит так, щоб отримати ці компоненти від Зовнішнього до Внутрішнього. Пам’ятайте, що інтерпретатор все ще буде розуміти логіку, яку ми вказали вище, але сам запит повинен мати наступний вигляд:

--Outer Query 
--Get the name of Customers that meet the right CustomerID
SELECT Name
FROM Customers
WHERE CustomerID IN (
    --SUBQUERY 1:
    --Get the CustomerIDs that meet “Above Average” criteria
    SELECT CustomerID
    FROM Orders
    WHERE Quantity > (
        --SUBQUERY 2:
        --Find the Average Quantity to “measure against”
        SELECT AVG(Quantity)
        FROM Orders
    )
);

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

Це був складний запит, але не переймайтеся, якщо ви не зрозуміли його одразу. Візьміть свій час, щоб розібрати кожну частину, і пам’ятайте, що найвнутрішніший запит виконується першим, і його результат використовується зовнішніми запитами.

Корельовані підзапити (Correlated Subqueries)

Корельований підзапит – це тип SQL-підзапиту, де внутрішній запит залежить від одночасного виконання зовнішнього запиту. Простіше кажучи, внутрішній запит може бути виконаний один раз для кожного рядка, обробленого зовнішнім запитом. Ця взаємозалежність відрізняє корельовані підзапити від їх некорельованих аналогів, в яких внутрішній запит може бути виконаний незалежно один раз, і його результат використовується зовнішнім запитом. У корельованому підзапиті внутрішній запит покладається на зовнішній запит для отримання своїх значень. Це означає, що підзапит виконується один раз для кожного рядка, обробленого зовнішнім запитом.

Навчальний випадок: Зарплати працівників

Уявіть, що ви допомагаєте другові в відділі кадрів отримати деякі дані, і він питає: “Чи можете ви перерахувати імена працівників, які заробляють більше, ніж середня зарплата в їхніх відповідних відділах?” Як б ви розглянули підхід до цієї проблеми з корельованим підзапитом?

SELECT e1.EmployeeName
FROM Employee e1
WHERE Salary > (SELECT AVG(Salary)
                FROM Employee e2
                WHERE e1.DepartmentID = e2.DepartmentID)

Цей запит виконує наступне:

1. Проходить кожного працівника в таблиці “Employee”, надаючи йому псевдонім “e1”.
   
2. Перевіряє, чи зарплата кожного працівника більша за середню зарплату всіх працівників, які працюють у тому ж відділі (визначено зіставленням “DepartmentID” працівника “e1” з “DepartmentID” працівників внутрішнього запиту “e2”).
   
3. Якщо зарплата працівника перевищує середню зарплату в їхньому відділі, їх ім’я буде включено в кінцевий набір результатів.

Чи це єдиний спосіб відповісти на вищезазначене питання? Звісно, ні! Ось інший спосіб вирішити це, повертаючись до наших вкладених підзапитів, але додавши умову JOIN.

SELECT e1.EmployeeName
FROM Employee e1
JOIN (
    SELECT DepartmentID, AVG(Salary) as AvgSalary
    FROM Employee
    GROUP BY DepartmentID
) e2
ON e1.DepartmentID = e2.DepartmentID
WHERE e1.Salary > e2.AvgSalary;

1. У підзапиті ми розраховуємо середню зарплату (AvgSalary) для кожного відділу (DepartmentID), групуючи дані за відділом.
   
2. Потім ми об’єднуємо цей підзапит (e2) з вихідною таблицею Employee (e1) за полем DepartmentID, що з’єднує кожного працівника з середньою зарплатою для його відділу.
   
3. У класі WHERE ми відфільтровуємо працівників, чиє заробітня плата більше, ніж середня зарплата їхнього відділу (e1.Salary > e2.AvgSalary).
   
4. На завершення ми вибираємо EmployeeName із результату.

Аналогія для підзапитів: Стопка Екзаменів

Уявіть, що ви вчитель, який перевіряє стопку екзаменів. Звичайний підзапит схожий на наявність списку середніх балів для кожного предмету. Ви можете використовувати цей список (результат підзапиту), щоб легко визначити, які учні набрали більше середнього балу з предметів.

Корельований підзапит, з іншого боку, схожий на оцінювання кожного екзаменаційного робочого зошита і, для кожного питання, порівнювання відповіді учня із відповідями всіх інших учнів, які склали той самий екзамен. Для кожного учня (кожен рядок у головному запиті) вам доведеться пройти через всю стопку екзаменів (виконати підзапит), щоб отримати інформацію, яку ви потребуєте.

Кореляція підзапиту та ефективність

На відміну від підзапитів, які ми досі розглядали, корельований підзапит не може виконуватися незалежно від зовнішнього запиту, оскільки він посилається на стовпці зовнішнього запиту. Для кожного запису, обробленого зовнішнім запитом, підзапит виконується знову з поточними значеннями стовпців зовнішнього запиту.

“Кореляція” відбувається тому, що підзапит використовує інформацію з зовнішнього запиту, і підзапит виконується один раз для кожного рядка, який вибирає зовнішній запит. Ця взаємозалежність між внутрішнім та зовнішнім запитами дозволяє внутрішньому запиту повертати різні результати для кожного рядка, обробленого зовнішнім запитом.

Важливо відзначити, що, хоча корельовані підзапити можуть бути дуже потужними, вони також можуть бути повільнішими, ніж стандартні підзапити чи з’єднання, оскільки їм може бути потрібно виконатися один раз для кожного запису, який повертає основний запит. Тому їх слід використовувати обережно, і важливо враховувати продуктивність при їх використанні. Таким чином, завжди є найкращою практикою спочатку обмежувати кількість рядків і тестувати продуктивність перед тим, як переходити до виконання корельованих підзапитів на великому наборі даних.

З цієї причини альтернативна версія прикладу зарплати працівника, яка включає умову JOIN, фактично може очікувати швидшої продуктивності за рівних умов. Однак корельований підзапит трошки легше читається, тому все залежить від розміру набору даних, з яким ви маєте справу, і від того, що важливіше – ефективність виробництва чи читабельність коду.

Підзапити в FROM Clause

Зрештою, підзапити можна використовувати в FROM SQL-виразу.

Іноді корисно обробляти результат підзапиту так, ніби це була б самостійна таблиця. SQL дозволяє це, включаючи підзапит в клаузулу FROM оператора SELECT.

Давайте припустимо, що у нас є таблиця Sales зі стовпцями Region, Year і Revenue, і ми хочемо знайти регіон з найвищим загальним доходом для кожного року. Ми можемо вирішити це за допомогою підзапиту в FROM:

SELECT Year, MAX(TotalRevenue) as MaxRevenue
FROM (
    SELECT Year, Region, SUM(Revenue) as TotalRevenue
    FROM Sales
    GROUP BY Year, Region
) as YearlySales
GROUP BY Year;

У цьому прикладі підзапит групує продажі за роками і регіонами та розраховує загальний дохід для кожної групи. Зовнішній запит потім бере цей результат і знаходить максимальний загальний дохід для кожного року.

Підзапит: Щорічні продажі за регіонами

Спочатку нам потрібно отримати щорічні продажі, додавши загальний дохід за весь рік і стратифікуючи результати (GROUP BY) за роком і регіоном.

Основний запит: Максимальний загальний дохід за кожен рік

Тепер, коли у нас є щорічні продажі для кожного року і регіону, це дійсно питання пошуку максимальних значень у цій новій отриманій таблиці, як показано в запиті вище. Отже, в цілому цей запит повертає таблицю з кожним роком з даних про продажі, і для кожного року – найвищий загальний дохід, який був досягнутий в будь-якому регіоні.

Думайте про отримання підзапитів в FROM просто як засіб зміни таблиці, яку ми вибираємо!

Всі варіації підзапитів

Підзапити – це запити, вкладені в інші запити. Їх можна використовувати в різних клаузулах, таких як SELECT, FROM, WHERE та HAVING. Вони дозволяють виконувати складні запити до бази даних та можуть бути використані для виконання операцій, які не можуть бути виконані в одному запиті.

Типи підзапитів:

• Підзапити з одним рядком (single row): Це підзапити, які повертають лише один рядок з внутрішнього оператора SELECT. Їх часто використовують із операторами, такими як =, >, <, >=, <=, або <>. Наприклад:

SELECT column_name(s)
FROM table_name
WHERE column_name = (
    SELECT column_name
    FROM table_name
    WHERE condition);

• Підзапити з декількома рядками (multiple row): Це підзапити, які повертають більше одного рядка з внутрішнього оператора SELECT. Їх часто використовують із операторами, такими як IN, ANY або ALL. Наприклад:

SELECT column_name(s)
FROM table_name
WHERE column_name IN (
    SELECT column_name
    FROM table_name
    WHERE condition);

• Вкладені підзапити (nested): Це підзапити, які існують в межах іншого підзапиту. Вони дозволяють використовувати кілька рівнів фільтрації та вибірки. Наприклад:

SSELECT column_name(s)
FROM table_name
WHERE column_name IN (
    SELECT column_name
    FROM table_name
    WHERE column_name IN (
        SELECT column_name
        FROM table_name
        WHERE condition));

• Підзапити з декількома стовпцями (multiple column): Це підзапити, які дозволяють повертати кілька стовпців в SELECT внутрішнього підзапиту. Це може бути використано, коли потрібно порівнювати більше одного результату стовпця з зовнішнім запитом. Наприклад:

SELECT column_name1, column_name2
FROM table_name
WHERE (column_name1, column_name2) IN (
    SELECT column_name1, column_name2
    FROM table_name
    WHERE condition);

• Корельовані підзапити (correlated): Це підзапити, які посилаються на стовпець таблиці у зовнішньому запиті. Вони виконуються один раз для кожного кандидатського рядка у зовнішньому запиті. Наприклад:

SELECT column_name(s)
FROM table_name t1
WHERE column_name operator (
    SELECT column_name
    FROM table_name t2
    WHERE t1.column_name = t2.column_name);

Корельовані та Некорельовані підзапити:

• Некорельовані підзапити: Їх можна виконувати незалежно від зовнішнього запиту. Вони виконуються один раз для всього запиту. Пам’ятайте, що вони (внутрішні підзапити) можуть виконуватися незалежно від зовнішнього (основного) запиту.
  
• Корельовані підзапити: Залежать від даних зовнішнього запиту. Вони виконуються один раз для кожного рядка, обробленого зовнішнім запитом. Зазвичай вони мають посилання, таке як WHERE t1.column_name = t2.column_name, на зовнішній запит, що робить їх незалежними (корельованими).

Практичні застосування підзапитів:

Підзапити можна використовувати для знаходження агрегованих значень по певних частинах даних.

Приклади в реальному світі:

• Знаходження максимального доходу за рік в різних регіонах.
  
• Визначення співробітників, які заробляють вище середньої зарплати у своїх відділах.
  
• Визначення клієнтів, які, здається, здійснюють покупки частіше, ніж решта клієнтської бази.

Зведення всього воєдино: Застосовувати підзапит чи ні?

Пам’ятайте, що важливо усвідомлювати, що не для кожного висловлення проблеми в SQL потрібний “молоток” у вигляді “підзапиту”. Іноді можуть існувати альтернативні рішення, використовуючи JOIN, віконні функції або групування, які пропонують кращу продуктивність чи читабельність. Розширюючи свій набір навичок у SQL та продовжуючи практику, ви почнете не лише бачити альтернативні рішення, але й вмітимете тестувати ці різні рішення самостійно. Є більше одного шляху розрізати SQL-кішку!

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

Пам’ятайте, що SQL – це інструмент, і, як будь-який інструмент, справа в тому, щоб використовувати потрібну функцію в потрібний момент для потрібної роботи. Підзапити – це потужна частина інструментарію SQL, яка дозволяє виконувати складний аналіз даних безпосередньо в базі даних. Так що наступного разу, коли ви працюватимете з SQL, пам’ятайте ці враження про підзапити та використовуйте їх повний потенціал.

Сподобався цей матеріал? Натискайте кнопку Follow, щоб отримувати мої останні поради та покрокові інструкції з програмування та науки про дані прямо у свій стрічку на Medium!

Дякую за читання!

ОРИГІНАЛ СТАТТІ:Mastering SQL Subqueries: A Comprehensive Guide

АВТОР СТАТІ:Manu Mulaveesala

Долучайтесь до нашої спільноти Telegram
* Data Life UA
* Data Analysis UA
* DATA ENGINEERING UA
Долучайтесь до нашої спільноти FaceBook
* Data-Life-UA