Вступ

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

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

Розділ 1: Скалярні підзапити

Скалярні підзапити повертають одне значення і часто використовуються в SELECT, WHERE і HAVING для створення більш динамічних запитів.

Приклад

Розрахунок пропорції продажів кожної країни:

SELECT billing_country,   
 ROUND(COUNT(*) * 100.0 /   
 (SELECT COUNT(*) FROM invoice), 2) AS sales_prop  
FROM invoice  
GROUP BY billing_country  
ORDER BY sales_prop DESC  
LIMIT 5;

Ключові моменти:

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

Розділ 2: Багаторядкові та багатоколонкові підзапити

Багаторядкові підзапити повертають кілька значень в одній колонці, тоді як багатоколонкові підзапити повертають цілі таблиці.

Приклад 1: Багаторядковий підзапит з IN

Отримання клієнтів з країн, де загальна кількість замовлень перевищила 500:

SELECT customer_id  
FROM customers  
WHERE country IN (  
 SELECT DISTINCT country  
 FROM orders  
 WHERE total > 500  
);

Приклад 2: Багатоколонковий підзапит в JOIN

SELECT t1.customer_id, t2.total  
FROM customers AS t1  
JOIN (  
 SELECT customer_id, SUM(total) AS total  
 FROM orders  
 GROUP BY customer_id  
) AS t2  
ON t1.customer_id = t2.customer_id;

Ключові моменти:

• Використовуйте багаторядкові підзапити з IN або NOT IN.
• Багатоколонкові підзапити використовуються як похідні таблиці або в з'єднаннях.

Розділ 3: Вкладені та корельовані підзапити

Вкладені підзапити включають запити всередині запитів, в той час як корельовані підзапити залежать від значень зовнішнього запиту.

Приклад 1: Вкладений підзапит

Отримання максимального значення продажу:

SELECT customer_id  
FROM orders  
WHERE total = (SELECT MAX(total) FROM orders);

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

Знайти клієнтів, у яких замовлення перевищують середнє значення їхніх замовлень:

SELECT customer_id  
FROM orders AS t1  
WHERE total > (  
 SELECT AVG(total)  
 FROM orders AS t2  
 WHERE t1.customer_id = t2.customer_id  
);

Ключові моменти:

• Вкладені підзапити оцінюються незалежно.
• Корельовані підзапити переоцінюються для кожного рядка зовнішнього запиту.

Розділ 4: Загальні вирази таблиць (CTE)

CTE дозволяють визначити повторно використовувані блоки запитів з допомогою ключового слова WITH, що робить складні запити зрозумілішими та легшими для підтримки.

Приклад 1: Простий CTE

Розрахунок загальних продажів за країною:

WITH SalesByCountry AS (  
 SELECT billing_country, SUM(total) AS total_sales  
 FROM invoice  
 GROUP BY billing_country  
)  
SELECT billing_country  
FROM SalesByCountry  
WHERE total_sales > 1000;

Приклад 2: Рекурсивний CTE

Генерація послідовності чисел:

WITH RECURSIVE Numbers AS (  
 SELECT 1 AS num  
 UNION ALL  
 SELECT num + 1  
 FROM Numbers  
 WHERE num < 10  
)  
SELECT num FROM Numbers;

Ключові моменти:

• CTE покращують читаність запитів.
• Рекурсивні CTE дуже корисні для ієрархічних або ітеративних структур даних.

Розділ 5: Подання

Подання є віртуальними таблицями, створеними на основі SQL запитів, що дозволяє інкапсулювати та повторно використовувати складну логіку.

Приклад 1: Створення подання

CREATE VIEW HighValueCustomers AS  
SELECT customer_id, SUM(total) AS total_sales  
FROM invoice  
GROUP BY customer_id  
HAVING total_sales > 1000;

Приклад 2: Запит до подання

SELECT * FROM HighValueCustomers;

Приклад 3: Видалення подання

DROP VIEW HighValueCustomers;

Ключові моменти:

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

Кращі практики

1.
Розбивайте складні запити: Використовуйте CTE або підзапити для того, щоб зробити ваші SQL-вирази більш читабельними та легкими для підтримки.
2. Оптимізуйте для продуктивності: Уникайте надмірного вкладення або корельованих підзапитів для великих наборів даних.
3. Використовуйте подання стратегічно: Створюйте подання для повторюваних складних запитів, щоб спростити аналіз.

Висновок

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

Почніть експериментувати з цими техніками у ваших проектах і підніміть свої навички SQL на новий рівень!

Перекладено з: Master SQL Subqueries, Common Table Expressions, and Views