Что такое подзапрос в БД?

Подзапрос (Subquery) в базе данных

Подзапрос (Subquery) — это SQL-запрос, который находится внутри другого SQL-запроса. Подзапрос также называют внутренним запросом, а запрос, который его содержит, называют внешним запросом или основным запросом. Подзапросы позволяют разбить сложную задачу на несколько более простых операций и значительно повышают выразительность и гибкость SQL-команд.

Основные характеристики подзапросов

Подзапросы обладают рядом важных свойств:

• Вложенность — подзапрос может содержать другие подзапросы
• Область видимости — подзапрос может ссылаться на таблицы внешнего запроса (коррелирующие подзапросы)
• Тип результата — может возвращать одно значение, строку или таблицу
• Выполнение — может выполняться один раз или для каждой строки внешнего запроса

Простой пример подзапроса

-- Найти сотрудников, заработная плата которых выше средней
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Здесь SELECT AVG(salary) FROM employees — это подзапрос, который вычисляет среднюю зарплату.

Типы подзапросов

1. Подзапросы в WHERE

SELECT project_name
FROM projects
WHERE department_id IN (
    SELECT id
    FROM departments
    WHERE employee_count > 10
);

2. Подзапросы в SELECT

SELECT 
    name,
    salary,
    (SELECT AVG(salary) FROM employees e2 WHERE e2.department_id = e1.department_id) as avg_dept_salary
FROM employees e1;

3. Подзапросы в FROM

SELECT *
FROM (
    SELECT department_id, MAX(salary) as max_salary
    FROM employees
    GROUP BY department_id
) dept_stats
WHERE max_salary > 100000;

Коррелирующие подзапросы

Коррелирующий подзапрос — это подзапрос, который ссылается на столбцы внешнего запроса. Такой подзапрос выполняется для каждой строки внешнего запроса:

SELECT name, salary, department_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary) 
    FROM employees e2 
    WHERE e2.department_id = e1.department_id
);

Операторы для работы с подзапросами

IN / NOT IN

SELECT *
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE location = 'New York'
);

EXISTS / NOT EXISTS

SELECT d.*
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.id
);

ANY / ALL

SELECT *
FROM employees
WHERE salary > ANY (
    SELECT salary FROM employees WHERE position = 'Manager'
);

Практические примеры

Пример 1: Комплексный анализ продаж

SELECT product_id, SUM(quantity) as total_sold
FROM orders
GROUP BY product_id
HAVING SUM(quantity) > (
    SELECT AVG(total_quantity)
    FROM (
        SELECT SUM(quantity) as total_quantity
        FROM orders
        GROUP BY product_id
    ) product_stats
);

Использование подзапросов в Python с SQLAlchemy

from sqlalchemy import func, select
from sqlalchemy.orm import Session

subquery = (
    select(
        Employee.department_id,
        func.avg(Employee.salary).label('avg_salary')
    )
    .group_by(Employee.department_id)
    .subquery()
)

result = (
    session.query(Employee)
    .join(subquery, Employee.department_id == subquery.c.department_id)
    .filter(Employee.salary > subquery.c.avg_salary)
    .all()
)

Оптимизация подзапросов

Подзапросы могут быть неэффективны, поэтому важно предпочитать JOINы коррелирующим подзапросам:

SELECT e.name
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
) dept_avg ON e.department_id = dept_avg.department_id
WHERE e.salary > dept_avg.avg_salary;

Важные замечания

• Производительность — предпочитайте JOINы коррелирующим подзапросам
• Читаемость — простые подзапросы понятнее, но сложные лучше разбить на части
• Индексы — убедитесь, что на столбцах в WHERE подзапроса есть индексы
• Null-значения — помните, что NULL в подзапросе может вызвать неожиданные результаты

Подзапросы — это мощный инструмент SQL, который позволяет решать сложные задачи аналитики данных и обработки информации эффективным способом.