Подзапросы SQL на собеседовании: примеры задач

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

Типы подзапросов на собеседовании

Существует несколько типов подзапросов, каждый из которых проверяет разные навыки:

1. Скалярный подзапрос — возвращает одно значение
2. Подзапрос в IN/NOT IN — возвращает список значений
3. Коррелированный подзапрос — ссылается на внешний запрос
4. Подзапрос в FROM — используется как временная таблица
5. EXISTS / NOT EXISTS — проверяет существование строк

Задача 1: Скалярный подзапрос (Junior+)

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

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;

Вопрос для обсуждения: «Что произойдёт, если в таблице нет ни одной записи? Что вернёт подзапрос?» — AVG от пустой таблицы возвращает NULL, и условие не выполнится для никакой строки.

Задача 2: Подзапрос в FROM (Middle)

-- Задача: найдите категории, где средняя цена товара
-- выше средней цены по всему каталогу

SELECT category, avg_price
FROM (
    SELECT
        category,
        AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_stats
WHERE avg_price > (SELECT AVG(price) FROM products);

Альтернатива через CTE (попросите кандидата переписать):

WITH category_stats AS (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
),
overall_avg AS (
    SELECT AVG(price) AS avg_price FROM products
)
SELECT cs.category, cs.avg_price
FROM category_stats cs
CROSS JOIN overall_avg oa
WHERE cs.avg_price > oa.avg_price;

Задача 3: Коррелированный подзапрос (Middle)

-- Задача: для каждого сотрудника найдите,
-- является ли его зарплата максимальной в его отделе

SELECT
    name,
    department_id,
    salary,
    CASE
        WHEN salary = (
            SELECT MAX(e2.salary)
            FROM employees e2
            WHERE e2.department_id = e1.department_id
        ) THEN 'Максимальная'
        ELSE 'Не максимальная'
    END AS salary_status
FROM employees e1;

Важный вопрос: «Насколько эффективен этот запрос? Как его улучшить?»

Коррелированный подзапрос выполняется для каждой строки — O(n²). Эффективнее через оконную функцию:

SELECT
    name,
    department_id,
    salary,
    CASE WHEN salary = MAX(salary) OVER (PARTITION BY department_id)
         THEN 'Максимальная' ELSE 'Не максимальная' END AS salary_status
FROM employees;

Задача 4: EXISTS vs IN (Middle+)

-- Задача: найдите клиентов, сделавших хотя бы один заказ

-- Способ с IN:
SELECT id, name FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- Способ с EXISTS (обычно быстрее на больших таблицах):
SELECT id, name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

Ключевой вопрос: «Когда EXISTS быстрее, чем IN?» — При большом подзапросе EXISTS останавливается на первом совпадении, а IN материализует весь список. Также EXISTS корректно работает с NULL.

Задача 5: NOT EXISTS vs NOT IN (важная ловушка!)

-- Задача: найдите клиентов, у которых НЕТ заказов

-- ОПАСНО: NOT IN с NULL в подзапросе
SELECT id, name FROM customers
WHERE id NOT IN (
    SELECT customer_id FROM orders  -- если есть NULL → результат пуст!
);

-- БЕЗОПАСНО: NOT EXISTS
SELECT id, name FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Также безопасно: LEFT JOIN + IS NULL
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Это классическая ловушка, на которой спотыкаются даже опытные разработчики. Кандидат, знающий о ней, реально работал с реальными данными.

Задача 6: N-й наибольший элемент (классика интервью)

-- Найдите вторую по величине зарплату (без оконных функций)

SELECT MAX(salary) AS second_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Универсальное решение для N-го значения (через OFFSET):
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;  -- OFFSET 0 = первый, OFFSET 1 = второй

-- Через оконные функции (предпочтительно):
SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 2;

Когда использовать подзапрос, а когда JOIN

Общее правило: если подзапрос коррелированный — почти всегда есть лучшее решение через JOIN или оконные функции.

Задачи на подзапросы с разбором каждого решения доступны на SQLlab.ru — идеально для подготовки к техническому интервью.