Занятие 24. Summary session 6

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

#join #where #null #select #union

⚡ Кратко: примеры

Сравнение INNER и LEFT JOIN.
UNION ALL с источником строки.
Поиск сотрудников без привилегий.

💻 Характерные примеры блока

Пример 1. Сравнение INNER и LEFT JOIN

USE northwind;

-- Только заказы с известным клиентом
SELECT o.id, c.company_name
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.id;

-- Все заказы, даже если клиент неизвестен
SELECT o.id, c.company_name
FROM orders AS o
LEFT JOIN customers AS c
  ON o.customer_id = c.id;

Пример 2. UNION ALL с пометкой источника

USE northwind;

SELECT company, 'employees' AS source FROM employees
UNION ALL
SELECT company, 'customers' AS source FROM customers
UNION ALL
SELECT company, 'suppliers' AS source FROM suppliers;

Пример 3. Поиск сотрудников без привилегий

USE northwind;

SELECT e.first_name, e.last_name
FROM employees AS e
LEFT JOIN employee_privileges AS ep
  ON e.id = ep.employee_id
WHERE ep.employee_id IS NULL;

Пример 4. Расшифровка заказов Seattle

USE northwind;

SELECT o.id, e.first_name, c.company_name, s.company
FROM orders AS o
LEFT JOIN employees AS e ON o.employee_id = e.id
LEFT JOIN customers AS c ON o.customer_id = c.id
LEFT JOIN shippers AS s ON o.shipper_id = s.id
WHERE o.ship_city = 'Seattle';