Занятие 22. Операторы JOIN и UNION

📁 Блок: SQL / MySQL / Связи и JOIN ⏱️ Время изучения: ~90 мин 🎯 Сложность: Средняя

#select #from #join #union

⚡ Кратко: дополнительная практика

Объедините orders и purchase_orders через UNION.
Отфильтруйте пустые employee_id и добавьте столбец-источник.
Соедините order_details с purchase_orders по payment_method.
Посчитайте количество инвойсов для каждого клиента.

🏠 Дополнительная практика по материалам занятия

Проверить по документации: извлечённый HTML-источник LMS для этого занятия содержит страницу авторизации, а не текст задания. Ниже оставлена учебная практика по JOIN и UNION; перед выдачей студентам сверить с актуальным заданием в LMS.

Все задания выполняются в базе northwind.

Задание 1. UNION двух таблиц

Выведите одним запросом с использованием UNION столбцы id, employee_id из таблицы orders и соответствующие им столбцы из таблицы purchase_orders. В таблице purchase_orders столбец created_by соответствует employee_id.

Пошаговое решение

Нам нужно выбрать два столбца из каждой таблицы. В orders это id и employee_id. В purchase_orders — id и created_by, причём created_by по смыслу равен employee_id.

USE northwind;

SELECT id, employee_id
FROM orders
UNION
SELECT id, created_by
FROM purchase_orders;

Задание 2. Фильтрация и источник строки

Из предыдущего запроса удалите записи, где employee_id не имеет значения. Добавьте дополнительный столбец со сведениями, из какой таблицы была взята запись.

Пошаговое решение

Для удаления строк с незаполненным employee_id используем WHERE employee_id IS NOT NULL в каждом SELECT. Для обозначения источника добавляем литерал.

USE northwind;

SELECT id, employee_id, 'orders' AS source
FROM orders
WHERE employee_id IS NOT NULL
UNION
SELECT id, created_by, 'purchase_orders' AS source
FROM purchase_orders
WHERE created_by IS NOT NULL;

Задание 3. JOIN с фильтрацией по payment_method

Выведите все столбцы таблицы order_details, а также дополнительный столбец payment_method из таблицы purchase_orders. Оставьте только заказы, для которых известен payment_method.

Пошаговое решение

Связываем таблицы по purchase_order_id и отбрасываем строки, где payment_method неизвестен.

USE northwind;

SELECT od.*, po.payment_method
FROM order_details AS od
INNER JOIN purchase_orders AS po
  ON od.purchase_order_id = po.id
WHERE po.payment_method IS NOT NULL;

Примечание: INNER JOIN уже исключает строки без совпадения, но явная проверка IS NOT NULL делает намерение очевидным.

Задание 4. Заказы с инвойсами

Выведите заказы orders и фамилии клиентов customers для тех заказов, по которым были инвойсы (таблица invoices).

Пошаговое решение

Связываем orders с customers через customer_id, а с invoices — через order_id. Достаточно INNER JOIN, потому что нужны только заказы с инвойсами.

USE northwind;

SELECT o.id AS order_id, c.last_name
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.id
INNER JOIN invoices AS i
  ON o.id = i.order_id;

Задание 5. Подсчёт инвойсов по клиентам

Подсчитайте количество инвойсов для каждого клиента из предыдущего запроса.

Пошаговое решение

Группируем по идентификатору клиента и считаем инвойсы.

USE northwind;

SELECT c.id, c.last_name, COUNT(i.id) AS invoice_count
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.id
INNER JOIN invoices AS i
  ON o.id = i.order_id
GROUP BY c.id, c.last_name;