Приходилось ли использовать подзапросы в SQL или LINQ?

Да, часто. Подзапросы (subqueries) — это мощный инструмент для построения сложных запросов, когда результат одного запроса используется в другом (во WHERE, FROM, SELECT или JOIN). Важно понимать их альтернативы и влияние на производительность.

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

Пример: Найти всех сотрудников, чья зарплата выше средней по отделу.

-- Коррелированный подзапрос (выполняется для каждой строки внешнего запроса)
SELECT e.Name, e.Salary, e.DepartmentId
FROM Employees e
WHERE e.Salary > (
    SELECT AVG(e2.Salary)
    FROM Employees e2
    WHERE e2.DepartmentId = e.DepartmentId -- Связь с внешним запросом
);

Пример с подзапросом в качестве производной таблицы (в FROM):

-- Найти отделы с количеством сотрудников > 5
SELECT d.Name, emp_counts.EmployeeCount
FROM Departments d
JOIN (
    SELECT DepartmentId, COUNT(*) as EmployeeCount
    FROM Employees
    GROUP BY DepartmentId
) AS emp_counts ON d.Id = emp_counts.DepartmentId
WHERE emp_counts.EmployeeCount > 5;

2. Подзапросы в LINQ (Entity Framework Core)

LINQ естественным образом выражает подзапросы.

// Аналог первого SQL-примера: сотрудники с зарплатой выше средней по отделу
var query = context.Employees
    .Where(e => e.Salary > context.Employees
        .Where(e2 => e2.DepartmentId == e.DepartmentId)
        .Average(e2 => e2.Salary)
    )
    .Select(e => new { e.Name, e.Salary, e.Department.Name });
// EF Core транслирует это в коррелированный подзапрос SQL.

3. Критические соображения и альтернативы

• Производительность: Коррелированные подзапросы в WHERE могут быть медленными, так как выполняются для каждой строки основного запроса. Всегда анализирую план выполнения (EXPLAIN).
• Альтернатива JOIN: Часто запрос с JOIN и GROUP BY будет эффективнее.

  -- Оптимизированная версия через JOIN
  SELECT e.Name, e.Salary, e.DepartmentId
  FROM Employees e
  JOIN (
      SELECT DepartmentId, AVG(Salary) as AvgSalary
      FROM Employees
      GROUP BY DepartmentId
  ) dept_avg ON e.DepartmentId = dept_avg.DepartmentId
  WHERE e.Salary > dept_avg.AvgSalary;

• Альтернатива CTE (Common Table Expression): В сложных случаях использую CTE для улучшения читаемости.

  WITH DepartmentAverages AS (
      SELECT DepartmentId, AVG(Salary) as AvgSalary
      FROM Employees
      GROUP BY DepartmentId
  )
  SELECT e.*, da.AvgSalary
  FROM Employees e
  JOIN DepartmentAverages da ON e.DepartmentId = da.DepartmentId
  WHERE e.Salary > da.AvgSalary;

• В LINQ: Для сложных сценариев иногда разбиваю запрос на части, используя промежуточные переменные, или применяю Join и GroupJoin для лучшей производительности и контроля над генерируемым SQL.

Вывод: Подзапросы — отличный инструмент для читаемости в простых случаях, но для больших данных или сложной логики необходимо анализировать план запроса и рассматривать варианты с JOIN или CTE.