Multi Table Databases

Підзапити (Subqueries)

Детальний розбір підзапитів (subqueries) - scalar, table, correlated, EXISTS, IN, позиції в SELECT/FROM/WHERE

Підзапити (Subqueries)

Проблема: Складні багаторівневі запити

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

Простий підхід НЕ працює:

-- ❌ Не працює - WHERE виконується ДО агрегації!
SELECT FirstName, Grade
FROM Students
WHERE Grade > AVG(Grade);  -- Помилка!

Рішення: Підзапит (Subquery)!

-- ✅ Працює - спочатку обчислюємо середнє
SELECT FirstName, Grade
FROM Students
WHERE Grade > (
    SELECT AVG(Grade) FROM Students  -- Підзапит
);

1. Що таке підзапит?

Підзапит (Subquery) — SQL-запит всередині іншого запиту.

Loading diagram...

Терміни:

Outer query (зовнішній запит) - головний запит
Subquery / Inner query (підзапит) - вкладений запит

2. Типи підзапитів

2.1. За результатом

Повертає: ONE VALUE (один рядок, один стовпець)

SELECT FirstName,
    (SELECT AVG(Grade) FROM Assessments) AS AvgGrade
FROM Students;

Повертає: МНОЖИНУ РЯДКІВ (таблицю)

SELECT *
FROM (
    SELECT FirstName, Grade
    FROM Students
    WHERE Grade > 80
) AS TopStudents;

Повертає: ОДИН РЯДОК (кілька стовпців)

SELECT *
FROM Students
WHERE (GroupId, Grade) = (
    SELECT GroupId, MAX(Grade)
    FROM Students
    GROUP BY GroupId
    LIMIT 1
);

2.2. За залежністю

Незалежний від зовнішнього запиту - виконується один раз.

WHERE Grade > (
    SELECT AVG(Grade) FROM Students
    -- Виконується ОДИН РАЗ
)

Залежний від зовнішнього запиту - виконується for each row.

WHERE Grade > (
    SELECT AVG(Grade)
    FROM Assessments AS A
    WHERE A.StudentId = S.Id  -- Залежить від S!
    -- Виконується ДЛЯ КОЖНОГО студента
)

3. Підзапити в WHERE

3.1. Scalar subquery (одне значення)

-- Студенти з балом вище середнього
SELECT
    FirstName,
    LastName,
    Grade
FROM Students
WHERE Grade > (SELECT AVG(Grade) FROM Students);

Виконання:

Спочатку підзапит: SELECT AVG(Grade) → 75.5
Потім: WHERE Grade > 75.5

3.2. IN з підзапитом (множина значень)

-- Студенти, які є в групах факультету "Computer Science"
SELECT FirstName, LastName
FROM Students
WHERE GroupId IN (
    SELECT G.Id
    FROM Groups AS G
    INNER JOIN Faculties AS F
        ON G.FacultyId = F.Id
    WHERE F.Name = 'Computer Science'
);

Аналогічно з JOIN:

SELECT S.FirstName
FROM Students AS S
WHERE S.GroupId IN (
    SELECT G.Id FROM Groups AS G
    WHERE G.FacultyId = 1
);

SELECT S.FirstName
FROM Students AS S
INNER JOIN Groups AS G
    ON S.GroupId = G.Id
WHERE G.FacultyId = 1;

Performance: JOIN зазвичай швидший за IN з підзапитом. Але IN зрозуміліший для читання.

3.3. NOT IN - знайти відсутні

-- Кафедри БЕЗ викладачів
SELECT Name
FROM Departments
WHERE Id NOT IN (
    SELECT DISTINCT DepartmentId
    FROM Teachers
    WHERE DepartmentId IS NOT NULL  -- ВАЖЛИВО!
);

NULL проблема: NOT IN з NULL повертає ПУСТЕ результат!

-- ❌ Поверне 0 рядків, якщо є NULL
WHERE Id NOT IN (1, 2, NULL)

-- ✅ Фільтруйте NULL
WHERE Id NOT IN (
    SELECT DepartmentId
    FROM Teachers
    WHERE DepartmentId IS NOT NULL
)

4. Підзапити в SELECT

4.1. Scalar subquery для обчислень

-- Показати студента + середній бал по БД
SELECT
    FirstName,
    LastName,
    Grade,
    (SELECT AVG(Grade) FROM Students) AS OverallAvg,
    Grade - (SELECT AVG(Grade) FROM Students) AS Difference
FROM Students;

Результат:

FirstName	LastName	Grade	OverallAvg	Difference
John	Doe	85	75.5	+9.5
Jane	Smith	70	75.5	-5.5

4.2. Correlated subquery в SELECT

-- Для кожного студента - його бал + середній по його групі
SELECT
    S.FirstName,
    S.Grade,
    (
        SELECT AVG(S2.Grade)
        FROM Students AS S2
        WHERE S2.GroupId = S.GroupId  -- Correlated!
    ) AS GroupAvg
FROM Students AS S;

Виконання: Для кожного студента S підзапит виконується заново з його GroupId.

Performance: Correlated subqueries повільні - виконуються N разів (по разу на рядок)!

5. Підзапити в FROM (Derived Tables)

5.1. Table subquery

-- Спочатку відфільтрувати, потім JOIN
SELECT
    TS.FirstName,
    TS.Grade,
    G.Name AS GroupName
FROM (
    SELECT *
    FROM Students
    WHERE Grade > 80  -- Попередній фільтр
) AS TS  -- "Derived table" - ОБОВ'ЯЗКОВИЙ alias!
INNER JOIN Groups AS G
    ON TS.GroupId = G.Id;

Обов'язково: Derived table має мати alias (AS TS)!

5.2. Агрегація в підзапиті

-- Середній бал по групі, потім відсортувати
SELECT
    GroupAvg.GroupId,
    GroupAvg.AvgGrade
FROM (
    SELECT
        GroupId,
        AVG(Grade) AS AvgGrade
    FROM Students
    GROUP BY GroupId
) AS GroupAvg  -- Derived table
WHERE GroupAvg.AvgGrade > 75
ORDER BY GroupAvg.AvgGrade DESC;

6. EXISTS та NOT EXISTS

6.1. EXISTS - перевірка існування

EXISTS повертає TRUE, якщо підзапит повертає хоч один рядок.

-- Кафедри, на яких Є викладачі
SELECT D.Name
FROM Departments AS D
WHERE EXISTS (
    SELECT 1  -- Не важливо що SELECT, важливо ЧИ Є рядки
    FROM Teachers AS T
    WHERE T.DepartmentId = D.Id
);

Переваги EXISTS:

✅ Зупиняється після першого збігу (швидко!)
✅ Не має NULL проблем
✅ Підтримує correlated підзапити

Best Practice: Використовуйте SELECT 1 або SELECT * в EXISTS - все одно.

6.2. NOT EXISTS - відсутність записів

-- Кафедри БЕЗ викладачів
SELECT D.Name
FROM Departments AS D
WHERE NOT EXISTS (
    SELECT 1
    FROM Teachers AS T
    WHERE T.DepartmentId = D.Id
);

6.3. EXISTS vs IN: Порівняння

-- ✅ Швидше, зупиняється після першого збігу
SELECT D.Name
FROM Departments AS D
WHERE EXISTS (
    SELECT 1
    FROM Teachers AS T
    WHERE T.DepartmentId = D.Id
);

-- ⚠️ Обробляє всі рядки
SELECT D.Name
FROM Departments AS D
WHERE D.Id IN (
    SELECT DepartmentId
    FROM Teachers
);

Коли що використовувати:

Сценарій	Рекомендація
Перевірка існування	EXISTS
Порівняння з конкретним списком	IN ('A', 'B', 'C')
Велика кількість збігів	EXISTS
NULL можливі	EXISTS (безпечніше)

7. ANY, ALL, SOME

7.1. ANY - хоч один

-- Студенти з балом вище ЗА БУДЬ-ЯКИЙ бал групи 1
SELECT FirstName, Grade
FROM Students
WHERE Grade > ANY (
    SELECT Grade
    FROM Students
    WHERE GroupId = 1
);

-- Еквівалентно: Grade > MIN(subquery)

7.2. ALL - всі

-- Студенти з балом вище ЗА ВСІ бали групи 1
SELECT FirstName, Grade
FROM Students
WHERE Grade > ALL (
    SELECT Grade
    FROM Students
    WHERE GroupId = 1
);

-- Еквівалентно: Grade > MAX(subquery)

7.3. SOME - синонім ANY

WHERE Grade > SOME (subquery)
-- Те саме що ANY

На практиці: ANY/ALLрідко використовуються - MIN/MAX зрозуміліші.

8. Correlated Subqueries

8.1. Концепція

Correlated subquery посилається на стовпці з зовнішнього запиту.

SELECT
    S1.FirstName,
    S1.Grade,
    (
        SELECT AVG(S2.Grade)
        FROM Students AS S2
        WHERE S2.GroupId = S1.GroupId  -- ← Correlated!
    ) AS GroupAvg
FROM Students AS S1;

Виконання:

Для S1 (рядок 1): виконати підзапит з S1.GroupId = 1
Для S1 (рядок 2): виконати підзапит з S1.GroupId = 1
Для S1 (рядок 3): виконати підзапит з S1.GroupId = 2
...

Loading diagram...

8.2. Приклад: Вище середнього по групі

-- Студенти з балом вище середнього ПО ЇХ ГРУПІ
SELECT
    S1.FirstName,
    S1.Grade,
    S1.GroupId
FROM Students AS S1
WHERE S1.Grade > (
    SELECT AVG(S2.Grade)
    FROM Students AS S2
    WHERE S2.GroupId = S1.GroupId  -- Correlated
);

8.3. Performance проблеми

ПОВІЛЬНО: Correlated subquery виконується N разів (для кожного рядка)!Альтернатива - CTE або JOIN:

-- ✅ Швидше - виконується ОДИН раз
WITH GroupAvgs AS (
    SELECT GroupId, AVG(Grade) AS AvgGrade
    FROM Students
    GROUP BY GroupId
)
SELECT S.FirstName, S.Grade
FROM Students AS S
INNER JOIN GroupAvgs AS GA
    ON S.GroupId = GA.GroupId
WHERE S.Grade > GA.AvgGrade;

9. Common Table Expressions (CTE) - Preview

CTE - альтернатива підзапитам, зрозуміліша та швидша.

-- Замість підзапиту в FROM
WITH TopStudents AS (
    SELECT FirstName, Grade
    FROM Students
    WHERE Grade > 80
)
SELECT TS.FirstName, G.Name
FROM TopStudents AS TS
INNER JOIN Groups AS G
    ON TS.GroupId = G.Id;

CTE буде детально розглянуто в наступних уроках. Поки що пам'ятайте: CTE = "іменований підзапит".

10. Практичні приклади

10.1. Топ-N запит

-- Топ-3 студенти по балу
SELECT FirstName, Grade
FROM Students
WHERE Grade IN (
    SELECT DISTINCT TOP 3 Grade
    FROM Students
    ORDER BY Grade DESC
)
ORDER BY Grade DESC;

10.2. Знайти другий максимум

-- Другий найвищий бал
SELECT MAX(Grade) AS SecondMax
FROM Students
WHERE Grade < (SELECT MAX(Grade) FROM Students);

10.3. Duplicate detection

-- Знайти студентів з однаковими іменами
SELECT FirstName, LastName
FROM Students AS S1
WHERE EXISTS (
    SELECT 1
    FROM Students AS S2
    WHERE S1.FirstName = S2.FirstName
    AND S1.LastName = S2.LastName
    AND S1.Id <> S2.Id  -- Не той самий запис
);

10.4. Знайти "сироти"

-- Студенти без групи (група видалена)
SELECT FirstName
FROM Students AS S
WHERE NOT EXISTS (
    SELECT 1
    FROM Groups AS G
    WHERE G.Id = S.GroupId
);

11. Best Practices

1. JOIN замість IN

-- ✅ Швидше
FROM Students S
INNER JOIN Groups G
    ON S.GroupId = G.Id

-- ⚠️ Повільніше
WHERE GroupId IN (
    SELECT Id FROM Groups
)

2. EXISTS замість IN

-- ✅ Для перевірки існування
WHERE EXISTS (SELECT 1 ...)

-- ⚠️ Для списку значень
WHERE Id IN (1, 2, 3)

3. Уникайте Correlated

-- ❌ Повільно (N виконань)
WHERE Grade > (
    SELECT AVG(Grade)
    WHERE GroupId = S.GroupId
)

-- ✅ Використовуйте CTE/JOIN

4. Filter NULL в NOT IN

WHERE Id NOT IN (
    SELECT DepartmentId
    FROM Teachers
    WHERE DepartmentId IS NOT NULL  -- !
)

12. Практичні завдання

Знайти студентів з балом вище середнього по всій БД.

💡 Розв'язок

SELECT FirstName, LastName, Grade
FROM Students
WHERE Grade > (
    SELECT AVG(Grade) FROM Students
)
ORDER BY Grade DESC;

Знайти кафедри без викладачів використовуючи NOT EXISTS.

💡 Розв'язок

SELECT Name
FROM Departments AS D
WHERE NOT EXISTS (
    SELECT 1
    FROM Teachers AS T
    WHERE T.DepartmentId = D.Id
);

Групи з найвищим середнім балом (топ-5).

💡 Розв'язок

SELECT
    G.Name,
    (
        SELECT AVG(S.Grade)
        FROM Students AS S
        WHERE S.GroupId = G.Id
    ) AS AvgGrade
FROM Groups AS G
ORDER BY AvgGrade DESC
OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY;

Студенти з балом вище середнього по їх групі (correlated).

💡 Розв'язок

SELECT S1.FirstName, S1.Grade, G.Name AS GroupName
FROM Students AS S1
INNER JOIN Groups AS G
    ON S1.GroupId = G.Id
WHERE S1.Grade > (
    SELECT AVG(S2.Grade)
    FROM Students AS S2
    WHERE S2.GroupId = S1.GroupId
);

Резюме

Ключові моменти п підзапитів:

Типи за результатом:
- Scalar (одне значення)
- Table (таблиця)
- Row (один рядок)
Позиції:
- WHERE: фільтрація (WHERE Grade > (SELECT ...)))
- SELECT: обчислення ( SELECT (SELECT AVG...) AS Avg)
- FROM: derived tables
Оператори:
- IN / NOT IN: список значень
- EXISTS / NOT EXISTS: перевірка існування (краще за IN!)
- ANY / ALL: порівняння з множиною
Correlated Subqueries:
- Залежать від зовнішнього запиту
- ⚠️ Повільні (виконуються N разів)
- Альтернатива: CTE або JOIN
Performance:
- JOIN > EXISTS > IN
- Уникайте correlated підзапитів
- Остерігайтеся NULL в NOT IN

Коли використовувати:

Багаторівневі умови
Comparison з агрегатами
Знайти відсутні зв'язки
Динамічні фільтри

Попередня тема: CROSS та SELF JOINs
Наступна тема: Агрегації з JOIN

Edit this pageorReport an issue

CROSS та SELF JOINs

Детальний розбір CROSS JOIN (Декартове представлення) та SELF JOIN (таблиця з собою) з практичними прикладами

Агрегації з JOIN

Використання COUNT, SUM, AVG, MIN, MAX з JOIN, GROUP BY з множинними таблицями, складні аналітичні запити