Декартово произведение и соединение таблицы с самой собой для иерархий
CROSS JOIN создаёт декартово произведение таблиц, а SELF JOIN позволяет соединить таблицу с самой собой для работы с иерархическими данными.
CROSS JOIN возвращает декартово произведение двух таблиц — каждую строку из первой таблицы соединяет с каждой строкой из второй.
SELECT <колонки>
FROM таблица1
CROSS JOIN таблица2;Или без ключевого слова CROSS (неявное декартово произведение):
SELECT <колонки>
FROM таблица1, таблица2;Важно: У CROSS JOIN нет условия
ON— оно не требуется, так как соединяются все строки со всеми.
CREATE TABLE colors (
id SERIAL PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE sizes (
id SERIAL PRIMARY KEY,
name VARCHAR(10)
);
INSERT INTO colors (name) VALUES ('Red'), ('Green'), ('Blue');
INSERT INTO sizes (name) VALUES ('S'), ('M'), ('L');Данные:
colors: sizes:
+----+-------+ +----+------+
| id | name | | id | name |
+----+-------+ +----+------+
| 1 | Red | | 1 | S |
| 2 | Green | | 2 | M |
| 3 | Blue | | 3 | L |
+----+-------+ +----+------+
SELECT c.name AS color, s.name AS size
FROM colors c
CROSS JOIN sizes s
ORDER BY c.name, s.name;Результат (3 × 3 = 9 строк):
+-------+------+
| color | size |
+-------+------+
| Blue | L |
| Blue | M |
| Blue | S |
| Green | L |
| Green | M |
| Green | S |
| Red | L |
| Red | M |
| Red | S |
+-------+------+
Размер декартова произведения = rows1 × rows2:
| Таблица 1 | Таблица 2 | Результат |
|---|---|---|
| 3 строки | 3 строки | 9 строк |
| 100 строк | 50 строк | 5 000 строк |
| 1000 строк | 1000 строк | 1 000 000 строк! |
Предупреждение: CROSS JOIN на больших таблицах может создать огромный результат и исчерпать память.
Генерация комбинаций:
-- Все комбинации товаров и складов
SELECT p.name, w.name AS warehouse
FROM products p
CROSS JOIN warehouses w;Заполнение пропусков в данных:
-- Создать все комбинации дата×товар для отчёта
WITH dates AS (
SELECT generate_series('2024-01-01', '2024-01-31', '1 day'::interval) AS date
),
all_combinations AS (
SELECT d.date, p.id AS product_id
FROM dates d
CROSS JOIN products p
)
SELECT ac.date, ac.product_id, COALESCE(s.amount, 0) AS sales
FROM all_combinations ac
LEFT JOIN sales s ON ac.date = s.date AND ac.product_id = s.product_id;Тестирование и генерация данных:
-- Создать тестовые данные
INSERT INTO test_data (color, size)
SELECT c.name, s.name
FROM colors c
CROSS JOIN sizes s;SELF JOIN — это соединение таблицы с самой собой. Полезно для работы с иерархическими данными, где строки ссылаются на другие строки той же таблицы.
SELECT <колонки>
FROM таблица t1
JOIN таблица t2 ON t1.колонка = t2.колонка;Ключевое правило: Обязательно используйте псевдонимы для таблицы, иначе SQL не сможет различить экземпляры.
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager_id INTEGER REFERENCES employees(id)
);
INSERT INTO employees (name, manager_id) VALUES
('CEO', NULL), -- id = 1, нет руководителя
('CTO', 1), -- id = 2, руководитель = CEO
('CFO', 1), -- id = 3, руководитель = CEO
('Dev Lead', 2), -- id = 4, руководитель = CTO
('Developer', 4), -- id = 5, руководитель = Dev Lead
('Accountant', 3); -- id = 6, руководитель = CFOДанные:
employees:
+----+------------+------------+
| id | name | manager_id |
+----+------------+------------+
| 1 | CEO | NULL |
| 2 | CTO | 1 |
| 3 | CFO | 1 |
| 4 | Dev Lead | 2 |
| 5 | Developer | 4 |
| 6 | Accountant | 3 |
+----+------------+------------+
-- Сотрудники с их руководителями
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.name;Результат:
+------------+------------+
| employee | manager |
+------------+------------+
| CEO | NULL |
| CTO | CEO |
| CFO | CEO |
| Dev Lead | CTO |
| Developer | Dev Lead |
| Accountant | CFO |
+------------+------------+
Используем LEFT JOIN, чтобы CEO (у которого manager_id = NULL) тоже попал в результат.
Можно соединять таблицу с собой несколько раз:
-- Сотрудник, руководитель, руководитель руководителя
SELECT
e.name AS employee,
m.name AS manager,
mm.name AS grand_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
LEFT JOIN employees mm ON m.manager_id = mm.id;Результат:
+------------+------------+---------------+
| employee | manager | grand_manager |
+------------+------------+---------------+
| CEO | NULL | NULL |
| CTO | CEO | NULL |
| CFO | CEO | NULL |
| Dev Lead | CTO | CEO |
| Developer | Dev Lead | CTO |
| Accountant | CFO | CEO |
+------------+------------+---------------+
Для произвольно глубоких иерархий используйте рекурсивные CTE:
-- Все подчинённые CEO (прямые и косвенные)
WITH RECURSIVE subordinates AS (
-- Базовый случай: прямые подчинённые CEO
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id = 1 -- CEO
UNION ALL
-- Рекурсивный случай: подчинённые подчинённых
SELECT e.id, e.name, e.manager_id, s.level + 1
FROM employees e
INNER JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;Результат:
+----+------------+------------+-------+
| id | name | manager_id | level |
+----+------------+------------+-------+
| 2 | CTO | 1 | 1 |
| 3 | CFO | 1 | 1 |
| 4 | Dev Lead | 2 | 2 |
| 6 | Accountant | 3 | 2 |
| 5 | Developer | 4 | 3 |
+----+------------+------------+-------+
CREATE TABLE friendships (
user_id INTEGER,
friend_id INTEGER,
PRIMARY KEY (user_id, friend_id)
);
INSERT INTO friendships VALUES
(1, 2), (1, 3), (2, 1), (2, 4),
(3, 1), (3, 4), (4, 2), (4, 3);-- Друзья друзей (кто не является прямым другом)
SELECT
f1.user_id AS user,
f2.friend_id AS friend_of_friend
FROM friendships f1
JOIN friendships f2 ON f1.friend_id = f2.user_id
LEFT JOIN friendships f3 ON f1.user_id = f3.user_id AND f2.friend_id = f3.friend_id
WHERE f1.user_id != f2.friend_id -- не сам пользователь
AND f3.user_id IS NULL; -- не прямой друг-- Сотрудники с одинаковой зарплатой
SELECT
e1.name AS employee1,
e2.name AS employee2,
e1.salary
FROM employees e1
JOIN employees e2 ON e1.salary = e2.salary AND e1.id < e2.id;Условие e1.id < e2.id предотвращает дублирование пар и соединение строки с самой собой.
| Характеристика | CROSS JOIN | SELF JOIN |
|---|---|---|
| Цель | Декартово произведение | Иерархии, сравнения внутри таблицы |
| Условие ON | Нет | Обязательно |
| Псевдонимы | Опциональны | Обязательны |
| Размер результата | rows1 × rows2 | Зависит от условия |
| Производительность | Может быть дорогим | Зависит от индексов |
LIMIT для тестирования:
SELECT * FROM table1 CROSS JOIN table2 LIMIT 100;WHERE для фильтрации:
SELECT * FROM colors c
CROSS JOIN sizes s
WHERE c.name != 'Red'; -- Фильтрация после соединенияCREATE INDEX idx_employees_manager_id ON employees(manager_id);WITH RECURSIVE — оптимизировано в PostgreSQL-- ОШИБКА: ambiguous table reference
SELECT name, manager_id
FROM employees
JOIN employees ON employees.manager_id = employees.id;
-- Правильно:
SELECT e.name, m.name AS manager
FROM employees e
JOIN employees m ON e.manager_id = m.id;-- ОШИБКА: забыто условие JOIN
SELECT * FROM colors, sizes; -- 9 строк вместо ожидаемых 3
-- Правильно:
SELECT * FROM colors CROSS JOIN sizes; -- Явно указываем намерение-- Опасно: может зациклиться при циклических ссылках
WITH RECURSIVE hierarchy AS (
SELECT id, manager_id FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.manager_id
FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
-- Нет ограничения на глубину!
)
-- Безопасно: ограничиваем глубину
WITH RECURSIVE hierarchy AS (
SELECT id, manager_id, 1 AS level FROM employees WHERE id = 1
UNION ALL
SELECT e.id, e.manager_id, h.level + 1
FROM employees e
JOIN hierarchy h ON e.manager_id = h.id
WHERE h.level < 10 -- Ограничение глубины
)Далее: Сложные условия JOIN