← К оглавлению занятия

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

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

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

  • INNER JOIN ... ON ...
  • LEFT JOIN ... ON ... WHERE ... IS NULL
  • SELECT ... UNION [ALL] SELECT ...

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

Связи и ключи

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
);

INNER JOIN

SELECT o.id, c.company_name
FROM orders AS o
INNER JOIN customers AS c
  ON o.customer_id = c.id;

LEFT JOIN для поиска отсутствующих связей

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;

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;

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

SELECT o.*, 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';

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

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