Занятие 23. Практикум 6

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

#select #union #join #where #null #group #by #like

⚡ Кратко: команды практикума

SELECT ... UNION ALL SELECT ... UNION ALL SELECT ...
LEFT JOIN ... WHERE ... IS NULL
JOIN ... JOIN ... GROUP BY ...
WHERE ... NOT LIKE '%...%'

🔖 Справочник операторов

UNION ALL из нескольких таблиц

SELECT company FROM employees
UNION ALL
SELECT company FROM customers
UNION ALL
SELECT company FROM suppliers;

UNION ALL с пометкой источника

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;

Поиск записей без связи

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;

Многотабличный JOIN + GROUP BY

SELECT itt.type_name, COUNT(*) AS transaction_count
FROM inventory_transactions AS it
JOIN inventory_transaction_types AS itt
  ON it.transaction_type = itt.id
JOIN products AS p
  ON it.product_id = p.id
WHERE itt.type_name NOT LIKE '%Sold%'
GROUP BY itt.type_name;

Расшифровка внешних ключей заказа

SELECT o.*, e.first_name, c.company_name, s.company AS shipper_name
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';

🔗 Полный справочник

Более полный каталог SQL-операторов курса — в справочнике SQL.