Скалярные, табличные подзапросы, EXISTS, ANY/ALL, WITH
Подзапрос — запрос вложенный в другой SQL-запрос.
| Тип | Возвращает | Использование |
|---|---|---|
| Скалярный | Одно значение | WHERE, SELECT, HAVING |
| Строковый | Одну строку | WHERE, FROM |
| Табличный | Таблицу строк | WHERE, FROM, JOIN |
Возвращают одно значение:
-- Пользователи старше среднего возраста
SELECT name, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);
-- Последний заказ каждого пользователя
SELECT name,
(SELECT MAX(order_date)
FROM orders o
WHERE o.user_id = u.id) AS last_order
FROM users u;-- Пользователи у которых есть заказы
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders);
-- Пользователи без заказов
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);Осторожно с NOT IN: Если подзапрос вернёт NULL, результат будет пустым!
-- Безопасная альтернатива
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);-- Пользователи с заказами
SELECT * FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- Пользователи без заказов
SELECT * FROM users u
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id
);Преимущество EXISTS:
-- Больше любого значения (больше минимального)
SELECT * FROM products
WHERE price > ANY (SELECT price FROM products WHERE category = 'Electronics');
-- Больше всех значений (больше максимального)
SELECT * FROM products
WHERE price > ALL (SELECT price FROM products WHERE category = 'Electronics');| Оператор | Описание |
|---|---|
> ANY | Больше минимального |
> ALL | Больше максимального |
< ANY | Меньше максимального |
< ALL | Меньше минимального |
= ANY | Эквивалентно IN |
Ссылаются на столбцы внешнего запроса:
-- Заказы выше среднего по пользователю
SELECT * FROM orders o1
WHERE total_amount > (
SELECT AVG(total_amount)
FROM orders o2
WHERE o2.user_id = o1.user_id
);
-- Сотрудники с зарплатой выше средней по отделу
SELECT * FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department_id = e1.department_id
);Производительность: Коррелированные подзапросы выполняются для каждой строки внешнего запроса. Часто можно переписать с JOIN для лучшей производительности.
Именованный подзапрос с WITH:
WITH active_users AS (
SELECT id, name, email
FROM users
WHERE is_active = TRUE
)
SELECT * FROM active_users
WHERE name LIKE 'A%';Преимущества CTE:
WITH
active_users AS (
SELECT id, name FROM users WHERE is_active = TRUE
),
user_orders AS (
SELECT user_id, COUNT(*) AS order_count
FROM orders
GROUP BY user_id
)
SELECT au.name, uo.order_count
FROM active_users au
JOIN user_orders uo ON au.id = uo.user_id
WHERE uo.order_count > 5;Для иерархических данных:
WITH RECURSIVE employee_hierarchy AS (
-- Базовый случай: сотрудники без руководителя
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Рекурсивный случай: подчинённые
SELECT e.id, e.name, e.manager_id, eh.level + 1
FROM employees e
JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM employee_hierarchy
ORDER BY level, name;Результат:
+----+-------+------------+-------+
| id | name | manager_id | level |
+----+-------+------------+-------+
| 1 | Alice | NULL | 1 |
| 2 | Bob | 1 | 2 |
| 3 | Carol | 1 | 2 |
| 4 | Dave | 2 | 3 |
+----+-------+------------+-------+
Комбинирование результатов запросов:
-- UNION: объединение с удалением дубликатов
SELECT id FROM users
UNION
SELECT id FROM customers;
-- UNION ALL: объединение с дубликатами
SELECT id FROM users
UNION ALL
SELECT id FROM customers;
-- INTERSECT: пересечение (общие значения)
SELECT id FROM users
INTERSECT
SELECT user_id FROM orders;
-- EXCEPT: разница (есть в первом, нет во втором)
SELECT id FROM users
EXCEPT
SELECT user_id FROM orders;| Оператор | Описание |
|---|---|
| UNION | Объединение с удалением дубликатов |
| UNION ALL | Объединение с дубликатами |
| INTERSECT | Пересечение (общие значения) |
| EXCEPT | Разница (только в первом запросе) |
-- Топ покупателей (CTE)
WITH user_totals AS (
SELECT
user_id,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
)
SELECT u.name, ut.total_spent
FROM users u
JOIN user_totals ut ON u.id = ut.user_id
ORDER BY ut.total_spent DESC
LIMIT 10;
-- Иерархия категорий (рекурсивный CTE)
WITH RECURSIVE category_tree AS (
SELECT id, name, parent_id, 0 AS level
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.name, c.parent_id, ct.level + 1
FROM categories c
JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree
ORDER BY level, name;
-- Поиск дубликатов (подзапрос)
SELECT email, name
FROM users
WHERE email IN (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1
);Готовы проверить знания? Переходите к вопросам!
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.