Расширенные применения оператора IN с подзапросами

Оператор IN в SQL позволяет проверять, входит ли значение в список значений или результат подзапроса. Расширенные применения IN с подзапросами позволяют выполнять сложные и гибкие запросы, проверяя наличие данных на основе связанных таблиц и условий. Рассмотрим несколько примеров и сценариев использования оператора IN с подзапросами.

1. Поиск записей по результатам подзапроса

Пример: Найти сотрудников, работающих в отделах, которые находятся в определенном местоположении.

SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 100
);

Этот запрос вернет сотрудников, работающих в отделах, расположенных в локации с идентификатором 100.

2. Использование IN с агрегатными функциями

Пример: Найти товары, которые были проданы в количестве выше среднего.

SELECT product_id, product_name
FROM products
WHERE product_id IN (
    SELECT product_id
    FROM sales
    GROUP BY product_id
    HAVING SUM(quantity) > (
        SELECT AVG(total_quantity)
        FROM (
            SELECT SUM(quantity) AS total_quantity
            FROM sales
            GROUP BY product_id
        ) AS avg_sales
    )
);

Этот запрос находит товары, которые были проданы в количестве больше среднего количества продаж всех товаров.

3. Поиск по нескольким подзапросам

Пример: Найти клиентов, которые сделали заказы в 2024 году и имеют статус "активный".

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
)
AND customer_id IN (
    SELECT customer_id
    FROM customer_status
    WHERE status = 'active'
);

Этот запрос найдет клиентов, которые сделали заказы в 2024 году и имеют статус "активный".

4. Поиск по результатам нескольких подзапросов с объединением данных

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

SELECT employee_id, employee_name
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 100
    AND department_name LIKE 'Sales%'
)
AND employee_id IN (
    SELECT employee_id
    FROM bonuses
    WHERE bonus_amount > 1000
);

Этот запрос найдет сотрудников, которые работают в отделах по продаже в локации с идентификатором 100 и получили бонусы больше 1000.

5. Использование IN с подзапросом, возвращающим уникальные значения

Пример: Найти клиентов, которые сделали заказы в каждом из месяцев года.

SELECT customer_id
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY customer_id
HAVING COUNT(DISTINCT EXTRACT(MONTH FROM order_date)) = 12;

Этот запрос вернет клиентов, которые сделали хотя бы один заказ в каждом месяце 2024 года.

6. Поиск с IN и соединением таблиц

Пример: Найти сотрудников, работающих в отделах с определенными характеристиками.

SELECT e.employee_id, e.employee_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IN (
    SELECT department_id
    FROM departments
    WHERE location_id = 100 AND department_name LIKE 'IT%'
);

Этот запрос найдет сотрудников, работающих в отделах IT, расположенных в локации с идентификатором 100.

Заключение

Оператор IN с подзапросами предоставляет мощные средства для выполнения сложных запросов, которые зависят от данных в связанных таблицах. Расширенные применения IN позволяют искать данные, основываясь на результатах подзапросов, использовать агрегатные функции, объединять данные из нескольких источников и выполнять более сложные условия фильтрации.