Соединения нескольких таблиц и самосоединения
Собрать целостную картину из многих таблиц -- включая таблицу, соединённую саму с собой.
JOIN ... ON присоединяет к результату ещё одну таблицу. Самосоединение -- тот же приём, применённый к одной таблице дважды: так вы связываете строку с другой строкой в той же таблице (сотрудника с его руководителем).
- аккуратно сцеплять три и более соединений с псевдонимами таблиц,
- рассуждать о порядке соединений -- когда он важен, а когда нет,
- писать самосоединение, связывающее строку с другой строкой той же таблицы,
- строить запросы на пары и избегать дубликатов и зеркальных пар,
- распознавать
CROSS JOIN-- как намеренный, так и случайный «декартов взрыв».
Это продолжает начальный курс: вы уже знаете SELECT, WHERE, GROUP BY, ключи и одиночные INNER JOIN / LEFT JOIN с NULL.
1. Пример схемы, с которой мы работаем
Каждый запрос ниже выполняется над одной небольшой базой магазина. У магазина естественно есть клиенты, оформленные ими заказы, товары в каждом заказе, сотрудники, которые их обслуживают, и отделы, к которым эти сотрудники относятся. Два отношения стоит отметить сразу: orders.emp_id связывает заказ с оформившим его менеджером, а employees.manager_id указывает обратно в ту же таблицу -- именно этот столбец делает самосоединения возможными.
CREATE TABLE departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(40)
);
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(40),
manager_id INT, -- points at another employees.emp_id
dept_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(emp_id),
FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
name VARCHAR(40),
city VARCHAR(40)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
emp_id INT, -- the sales rep who took the order
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (emp_id) REFERENCES employees(emp_id)
);
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(40),
price DECIMAL(8,2)
);
CREATE TABLE order_items (
order_id INT,
product_id INT,
quantity INT,
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES orders(order_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);Несколько строк для запросов:
INSERT INTO departments VALUES
(1,'Sales'), (2,'Support'), (3,'Warehouse');
INSERT INTO employees VALUES -- (emp_id, name, manager_id, dept_id)
(1,'Alice', NULL, 1), -- top manager, no boss
(2,'Bob', 1, 1),
(3,'Carol', 1, 2),
(4,'Dan', 2, 1),
(5,'Eve', 3, 2);
INSERT INTO customers VALUES
(1,'Maria','Bishkek'), (2,'John','Almaty'), (3,'Sara','Osh');
INSERT INTO orders VALUES -- (order_id, customer_id, emp_id, order_date)
(101,1,2,'2026-03-01'),
(102,2,2,'2026-03-02'),
(103,1,4,'2026-03-05'),
(104,3,3,'2026-03-06');
INSERT INTO products VALUES
(1,'Keyboard',25.00), (2,'Mouse',15.00), (3,'Monitor',120.00);
INSERT INTO order_items VALUES -- (order_id, product_id, quantity)
(101,1,2), (101,2,1),
(102,3,1),
(103,2,3),
(104,1,1);Держите эту диаграмму в уме: соединение всегда следует одной из этих стрелок, сопоставляя столбец внешнего ключа одной таблицы с первичным ключом, на который он ссылается в другой.