Условие

Дана таблица employees(employee_id, name, salary, manager_id). Напишите запрос, выводящий среднюю зарплату сотрудников, которые тоже являются менеджерами (то есть на них есть ссылки manager_id от других сотрудников).

Решение

Через подзапрос с IN

SELECT AVG(salary)
FROM   employees
WHERE  employee_id IN (
  SELECT DISTINCT manager_id
  FROM   employees
  WHERE  manager_id IS NOT NULL
);

Логика:

Подзапрос: ID всех сотрудников, на которых ссылаются как на менеджеров.
Внешний запрос: средняя зарплата этих сотрудников.

Через EXISTS

SELECT AVG(e1.salary)
FROM   employees e1
WHERE  EXISTS (
  SELECT 1 FROM employees e2
  WHERE  e2.manager_id = e1.employee_id
);

Часто читается яснее.

Через JOIN

SELECT AVG(DISTINCT e1.salary)         -- DISTINCT, иначе зарплата с повторами
FROM   employees e1
JOIN   employees e2 ON e2.manager_id = e1.employee_id;

Здесь нужен DISTINCT: иначе зарплата менеджера, у которого 5 подчинённых, попадёт в среднее 5 раз. Это ловушка JOIN-варианта.

IN vs EXISTS — производительность

	IN	EXISTS
Семантика	Проверка по списку значений	Проверка существования строки
NULL	`x IN (NULL, ...)` ведёт себя странно	NULL не мешает
Оптимизатор	Часто переписывается в EXISTS	Уже EXISTS
Большие списки	Может быть медленнее	Обычно быстрее

В большинстве СУБД оптимизатор разберётся, но в сложных запросах разница ощутима.

NOT IN с NULL — типичная ошибка

-- ОПАСНО: если manager_id содержит NULL, результат будет пустым
SELECT name FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);

employee_id <> NULL = UNKNOWN, и строка не пройдёт фильтр. Используйте NOT EXISTS:

SELECT name FROM employees e1
WHERE NOT EXISTS (
  SELECT 1 FROM employees e2 WHERE e2.manager_id = e1.employee_id
);

Подводные камни

JOIN без DISTINCT — задвоит зарплату менеджера с несколькими подчинёнными.
NOT IN с NULL — даёт пустой результат. Только NOT EXISTS.
IS NOT NULL в подзапросе — без него подзапрос вернёт NULL'ы, и IN молча пропустит их (что часто нужно, но иногда нет).

Эталонный ответ

SELECT AVG(salary) FROM employees
WHERE employee_id IN (
  SELECT DISTINCT manager_id FROM employees WHERE manager_id IS NOT NULL
);

Средняя зарплата сотрудников, которые сами менеджеры