INNER, LEFT, RIGHT, FULL JOIN, USING, самосоединения
JOIN (соединение) — это операция SQL, которая объединяет строки из двух или более таблиц на основе связанного условия между ними.
💡 Главная идея: Нормализованные базы данных хранят данные в отдельных таблицах. JOIN позволяет «собрать» эти данные обратно в единый результат.
┌─────────────────────────────────────────────────────────────┐
│ НОРМАЛИЗОВАННАЯ БАЗА ДАННЫХ │
├─────────────────────────────────────────────────────────────┤
│ │
│ Таблица users: Таблица orders: │
│ ┌─────┬──────┐ ┌─────┬─────────┬──────────┐ │
│ │ id │ name │ │ id │ user_id │ amount │ │
│ ├─────┼──────┤ ├─────┼─────────┼──────────┤ │
│ │ 1 │ Alice│ │ 1 │ 1 │ 1000 │ │
│ │ 2 │ Bob │ │ 2 │ 1 │ 2500 │ │
│ │ 3 │ Carol│ │ 3 │ 2 │ 500 │ │
│ └─────┴──────┘ └─────┴─────────┴──────────┘ │
│ │
│ ❌ Проблема: имя пользователя хранится отдельно от заказов │
│ ✅ Решение: JOIN объединяет данные по user_id │
│ │
└─────────────────────────────────────────────────────────────┘
SELECT столбцы
FROM таблица1
[тип] JOIN таблица2 ON условие_соединения;Компоненты:
FROM таблица1 — левая таблицаJOIN таблица2 — правая таблицаON условие — условие соединения (обычно foreign key = primary key)┌─────────────────────────────────────────────────────────────┐
│ ВИЗУАЛИЗАЦИЯ JOIN │
├─────────────────────────────────────────────────────────────┤
│ │
│ Таблица A: ○○○ Таблица B: △△△ │
│ ○●● △△△ │
│ ○○○ △△△ │
│ │
│ INNER JOIN: ●●△△ (только пересечение) │
│ LEFT JOIN: ○○○●●△△ (все левые + пересечение) │
│ RIGHT JOIN: ●●△△△△△ (все правые + пересечение) │
│ FULL JOIN: ○○○●●△△△△ (все строки обеих таблиц) │
│ CROSS JOIN: ○×△ для каждой комбинации │
│ │
└─────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────┐
│ ДИАГРАММЫ ВЕННА │
├─────────────────────────────────────────────────────────────┤
│ │
│ INNER JOIN: LEFT JOIN: │
│ ┌─────┐ ┌─────┐ │
│ │ A │ │ A │ │
│ │ ●●●│ │█████│ │
│ └─────┘ └─────┘ │
│ ●●● ●●● │
│ ┌─────┐ ┌─────┐ │
│ │ B │ │ B │ │
│ └─────┘ └─────┘ │
│ (пересечение) (все A + пересечение) │
│ │
│ RIGHT JOIN: FULL JOIN: │
│ ┌─────┐ ┌─────┐ │
│ │ A │ │█████│ │
│ │ ●●●│ │●●●●●│ │
│ └─────┘ └─────┘ │
│ ●●● ●●● │
│ ┌─────┐ ┌─────┐ │
│ │ B │ │█████│ │
│ └─────┘ └─────┘ │
│ (все B + пересечение) (всё полностью) │
│ │
└─────────────────────────────────────────────────────────────┘
INNER JOIN (внутреннее соединение) возвращает только строки, где есть совпадение в обеих таблицах.
SELECT u.name, o.id AS order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- INNER можно опустить (по умолчанию)
SELECT u.name, o.id AS order_id
FROM users u
JOIN orders o ON u.id = o.user_id;-- Исходные данные
-- users (4 строки)
┌─────┬─────────┬────────────┐
│ id │ name │ city │
├─────┼─────────┼────────────┤
│ 1 │ Alice │ Moscow │
│ 2 │ Bob │ London │
│ 3 │ Carol │ Paris │
│ 4 │ David │ Berlin │
└─────┴─────────┴────────────┘
-- orders (5 строк)
┌─────┬─────────┬──────────────┐
│ id │ user_id │ total_amount │
├─────┼─────────┼──────────────┤
│ 1 │ 1 │ 1000 │
│ 2 │ 1 │ 2500 │
│ 3 │ 2 │ 500 │
│ 4 │ 5 │ 3000 │ ← Заказ несуществующего пользователя
│ 5 │ 3 │ 1500 │
└─────┴─────────┴──────────────┘
-- INNER JOIN
SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.id;Шаг 1: Берём первую строку users (id=1, Alice)
Ищем совпадения в orders где user_id=1
Найдено: order_id=1, order_id=2
Результат: (1, Alice, 1, 1000), (1, Alice, 2, 2500)
Шаг 2: Берём вторую строку users (id=2, Bob)
Ищем совпадения в orders где user_id=2
Найдено: order_id=3
Результат: (2, Bob, 3, 500)
Шаг 3: Берём третью строку users (id=3, Carol)
Ищем совпадения в orders где user_id=3
Найдено: order_id=5
Результат: (3, Carol, 5, 1500)
Шаг 4: Берём четвёртую строку users (id=4, David)
Ищем совпадения в orders где user_id=4
Найдено: нет совпадений
Результат: ничего не добавляем
Шаг 5: orders id=4 (user_id=5) не имеет совпадения в users
Результат: пропускаем
Итоговый результат (4 строки):
┌─────┬─────────┬──────────┬──────────────┐
│ id │ name │ order_id │ total_amount │
├─────┼─────────┼──────────┼──────────────┤
│ 1 │ Alice │ 1 │ 1000 │
│ 1 │ Alice │ 2 │ 2500 │
│ 2 │ Bob │ 3 │ 500 │
│ 3 │ Carol │ 5 │ 1500 │
└─────┴─────────┴──────────┴──────────────┘
❌ David не попал (нет заказов)
❌ Заказ id=4 не попал (нет пользователя)
| Сценарий | Пример |
|---|---|
| Только связанные данные | Заказы с пользователями |
| Отчёт по активным данным | Продажи по существующим товарам |
| Фильтрация «сирот» | Только пользователи с заказами |
LEFT JOIN (левое внешнее соединение) возвращает все строки из левой таблицы + совпадения из правой. Если совпадения нет — правая часть будет NULL.
SELECT u.name, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- LEFT OUTER JOIN — то же самое
SELECT u.name, o.id AS order_id
FROM users u
LEFT OUTER JOIN orders o ON u.id = o.user_id;-- Те же данные что и выше
-- LEFT JOIN
SELECT u.id, u.name, o.id AS order_id, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.id;Шаг 1: users id=1 (Alice)
orders user_id=1 → order_id=1, 2
Результат: (1, Alice, 1, 1000), (1, Alice, 2, 2500)
Шаг 2: users id=2 (Bob)
orders user_id=2 → order_id=3
Результат: (2, Bob, 3, 500)
Шаг 3: users id=3 (Carol)
orders user_id=3 → order_id=5
Результат: (3, Carol, 5, 1500)
Шаг 4: users id=4 (David)
orders user_id=4 → нет совпадений
Результат: (4, David, NULL, NULL) ← NULL для правой таблицы
Итоговый результат (5 строк):
┌─────┬─────────┬──────────┬──────────────┐
│ id │ name │ order_id │ total_amount │
├─────┼─────────┼──────────┼──────────────┤
│ 1 │ Alice │ 1 │ 1000 │
│ 1 │ Alice │ 2 │ 2500 │
│ 2 │ Bob │ 3 │ 500 │
│ 3 │ Carol │ 5 │ 1500 │
│ 4 │ David │ NULL │ NULL │ ← Все пользователи!
└─────┴─────────┴──────────┴──────────────┘
✅ Все пользователи попали (включая David без заказов)
❌ Заказ id=4 не попал (нет пользователя с id=5)
Левая таблица (users): Правая таблица (orders):
┌─────────────────┐ ┌─────────────────┐
│ Alice ────┼──────▶│ order 1 │
│ Bob ────┼──────▶│ order 2 │
│ Carol ────┼──────▶│ order 3 │
│ David ────┘ │ order 4 (user=5)│
│ (нет заказов) └─────────────────┘
│ (order 4 не попадёт)
Результат:
┌─────────────────────────────────┐
│ Alice → order 1, order 2 │
│ Bob → order 3 │
│ Carol → order 5 │
│ David → NULL │ ← Сохранён!
└─────────────────────────────────┘
-- Пользователи БЕЗ заказов
SELECT u.id, u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL; ← Ключевой фильтр!
Результат:
┌─────┬─────────┐
│ id │ name │
├─────┼─────────┤
│ 4 │ David │ ← Только пользователи без заказов
└─────┴─────────┘-- Заказы БЕЗ товаров (если товар удалён)
SELECT o.id, o.user_id, oi.product_id
FROM orders o
LEFT JOIN order_items oi ON o.id = oi.order_id
WHERE oi.product_id IS NULL;| Сценарий | Пример |
|---|---|
| Все записи + связанные | Все пользователи + их заказы |
| Поиск отсутствующих | Пользователи без заказов |
| Отчёт с нулями | Продажи по всем категориям (включая пустые) |
| Сохранение левой таблицы | Клиенты даже без заказов |
RIGHT JOIN (правое внешнее соединение) — зеркальная версия LEFT JOIN: возвращает все строки из правой таблицы + совпадения из левой.
SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;-- RIGHT JOIN
SELECT u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- То же самое с LEFT JOIN (таблицы поменяны местами)
SELECT u.name, o.id AS order_id
FROM orders o
LEFT JOIN users u ON o.id = o.user_id;-- RIGHT JOIN
SELECT u.id AS user_id, u.name, o.id AS order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
Результат:
┌──────────┬─────────┬──────────┐
│ user_id │ name │ order_id │
├──────────┼─────────┼──────────┤
│ 1 │ Alice │ 1 │
│ 1 │ Alice │ 2 │
│ 2 │ Bob │ 3 │
│ 3 │ Carol │ 5 │
│ NULL │ NULL │ 4 │ ← Заказ без пользователя!
└──────────┴─────────┴──────────┘💡 Совет: Избегайте RIGHT JOIN. Перепишите с LEFT JOIN (поменяв таблицы местами) — это более привычно и читается легче.
FULL JOIN (полное внешнее соединение) возвращает все строки из обеих таблиц. Если совпадения нет — отсутствующая часть будет NULL.
SELECT u.name, o.id AS order_id
FROM users u
FULL JOIN orders o ON u.id = o.user_id;-- FULL JOIN
SELECT u.id AS user_id, u.name, o.id AS order_id, o.total_amount
FROM users u
FULL JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.id;Шаг 1: users id=1 (Alice) ↔ orders user_id=1
Результат: (1, Alice, 1, 1000), (1, Alice, 2, 2500)
Шаг 2: users id=2 (Bob) ↔ orders user_id=2
Результат: (2, Bob, 3, 500)
Шаг 3: users id=3 (Carol) ↔ orders user_id=3
Результат: (3, Carol, 5, 1500)
Шаг 4: users id=4 (David) ↔ нет совпадений
Результат: (4, David, NULL, NULL) ← Пользователь без заказов
Шаг 5: orders id=4 (user_id=5) ↔ нет совпадений в users
Результат: (NULL, NULL, 4, 3000) ← Заказ без пользователя
Итоговый результат (6 строк):
┌──────────┬─────────┬──────────┬──────────────┐
│ user_id │ name │ order_id │ total_amount │
├──────────┼─────────┼──────────┼──────────────┤
│ 1 │ Alice │ 1 │ 1000 │
│ 1 │ Alice │ 2 │ 2500 │
│ 2 │ Bob │ 3 │ 500 │
│ 3 │ Carol │ 5 │ 1500 │
│ 4 │ David │ NULL │ NULL │ ← Пользователь без заказов
│ NULL │ NULL │ 4 │ 3000 │ ← Заказ без пользователя
└──────────┴─────────┴──────────┴──────────────┘
✅ Все пользователи + все заказы (включая «сирот»)
┌─────────────────────────────────────────────────────────────┐
│ FULL JOIN │
├─────────────────────────────────────────────────────────────┤
│ │
│ users: orders: │
│ ┌─────────┐ ┌─────────┐ │
│ │ Alice │───────────▶│ order 1 │ │
│ │ Bob │───────────▶│ order 2 │ │
│ │ Carol │───────────▶│ order 3 │ │
│ │ David │ (нет) │ order 4 │ (нет пользователя) │
│ └─────────┘ └─────────┘ │
│ ↓ ↓ │
│ Результат: ВСЕ строки из обеих таблиц │
│ │
└─────────────────────────────────────────────────────────────┘
| Сценарий | Пример |
|---|---|
| Полный аудит данных | Все пользователи и все заказы |
| Сравнение двух источников | Синхронизация данных |
| Отчёт с «сиротами» | Заказы без пользователей + пользователи без заказов |
CROSS JOIN (декартово произведение) возвращает все возможные комбинации строк из обеих таблиц.
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s;
-- Без указания условия (явный CROSS JOIN)
SELECT c.color, s.size
FROM colors c, sizes s; -- Старый синтаксис, не рекомендуется-- Исходные данные
-- colors (3 строки)
┌──────────┐
│ color │
├──────────┤
│ Red │
│ Green │
│ Blue │
└──────────┘
-- sizes (3 строки)
┌──────┐
│ size │
├──────┤
│ S │
│ M │
│ L │
└──────┘
-- CROSS JOIN
SELECT c.color, s.size
FROM colors c
CROSS JOIN sizes s
ORDER BY c.color, s.size;Формула: количество строк = rows(colors) × rows(sizes) = 3 × 3 = 9
┌──────────┬──────┐
│ color │ size │
├──────────┼──────┤
│ Red │ S │
│ Red │ M │
│ Red │ L │
│ Green │ S │
│ Green │ M │
│ Green │ L │
│ Blue │ S │
│ Blue │ M │
│ Blue │ L │
└──────────┴──────┘
Для каждой строки из colors:
Для каждой строки из sizes:
Добавить комбинацию в результат
Red:
Red + S = (Red, S)
Red + M = (Red, M)
Red + L = (Red, L)
Green:
Green + S = (Green, S)
Green + M = (Green, M)
Green + L = (Green, L)
Blue:
Blue + S = (Blue, S)
Blue + M = (Blue, M)
Blue + L = (Blue, L)
-- Генерация всех комбинаций дат и магазинов
SELECT d.date, s.store_id
FROM (
SELECT generate_series('2026-01-01', '2026-01-31', '1 day')::date AS date
) d
CROSS JOIN (SELECT id AS store_id FROM stores) s;
-- Заполнение пропусков в отчёте (все даты × все категории)
WITH dates AS (
SELECT generate_series(
'2026-01-01'::date,
'2026-12-31'::date,
'1 day'
)::date AS date
),
categories AS (
SELECT id FROM categories
)
SELECT d.date, c.id AS category_id
FROM dates d
CROSS JOIN categories c;-- Случайный CROSS JOIN через забытое условие
SELECT u.name, o.total_amount
FROM users u, orders o; -- ❌ Нет WHERE!
-- Результат: 1000 users × 10000 orders = 10 000 000 строк!
-- Производительность: катастрофа⚠️ Внимание: CROSS JOIN с большими таблицами создаёт огромное количество строк. Используйте осознанно!
SELF JOIN — это соединение таблицы с самой собой. Полезно для иерархических данных.
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;-- employees (иерархия)
┌─────┬─────────┬────────────┐
│ id │ name │ manager_id │
├─────┼─────────┼────────────┤
│ 1 │ Alice │ NULL │ ← CEO (нет менеджера)
│ 2 │ Bob │ 1 │ ← Менеджер: Alice
│ 3 │ Carol │ 1 │ ← Менеджер: Alice
│ 4 │ Dave │ 2 │ ← Менеджер: Bob
│ 5 │ Eve │ 2 │ ← Менеджер: Bob
│ 6 │ Frank │ 4 │ ← Менеджер: Dave
└─────┴─────────┴────────────┘
-- SELF JOIN
SELECT
e.id AS employee_id,
e.name AS employee,
m.id AS manager_id,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY e.id;┌─────────────┬──────────┬────────────┬──────────┐
│ employee_id │ employee │ manager_id │ manager │
├─────────────┼──────────┼────────────┼──────────┤
│ 1 │ Alice │ NULL │ NULL │ ← Нет менеджера
│ 2 │ Bob │ 1 │ Alice │
│ 3 │ Carol │ 1 │ Alice │
│ 4 │ Dave │ 2 │ Bob │
│ 5 │ Eve │ 2 │ Bob │
│ 6 │ Frank │ 4 │ Dave │
└─────────────┴──────────┴────────────┴──────────┘
Организационная структура:
Alice (CEO)
/ \
Bob Carol
/ \
Dave Eve
|
Frank
SELF JOIN «расплющивает» иерархию в таблицу.
-- Все подчинённые Alice (рекурсивно)
WITH RECURSIVE subordinates AS (
-- Базовый случай: непосредственные подчинённые
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id = 1 -- Alice
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 │ Bob │ 1 │ 1 │ ← Прямой подчинённый
│ 3 │ Carol │ 1 │ 1 │
│ 4 │ Dave │ 2 │ 2 │ ← Подчинённый Bob
│ 5 │ Eve │ 2 │ 2 │
│ 6 │ Frank │ 4 │ 3 │ ← Подчинённый Dave
└─────┴─────────┴────────────┴───────┘-- Поиск дубликатов email
SELECT u1.id AS id1, u2.id AS id2, u1.email
FROM users u1
JOIN users u2 ON u1.email = u2.email
AND u1.id < u2.id; -- Избегаем дублирования пар
-- Сравнение сотрудников с одинаковой должностью
SELECT e1.name AS emp1, e2.name AS emp2, e1.position
FROM employees e1
JOIN employees e2 ON e1.position = e2.position
AND e1.id < e2.id;
-- Поиск сотрудников в одном отделе
SELECT e1.name, e2.name, e1.department
FROM employees e1
JOIN employees e2 ON e1.department = e2.department
AND e1.id != e2.id;Множественные JOIN позволяют соединять три и более таблиц в одном запросе.
SELECT
u.name AS user_name,
o.id AS order_id,
p.name AS product_name,
oi.quantity,
oi.price
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.created_at >= '2026-01-01';┌─────────────────────────────────────────────────────────────┐
│ Шаг 1: users JOIN orders │
│ └─> Получаем заказы с пользователями │
└─────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Шаг 2: Результат JOIN order_items │
│ └─> Добавляем позиции заказов │
└─────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Шаг 3: Результат JOIN products │
│ └─> Добавляем информацию о товарах │
└─────────────────────────────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────┐
│ Шаг 4: WHERE фильтр │
│ └─> Оставляем только заказы с 2026 года │
└─────────────────────────────────────────────────────────────┘
┌─────────┐ ┌─────────┐ ┌─────────────┐ ┌───────────┐
│ users │────▶│ orders │────▶│ order_items │────▶│ products │
│ │ │ │ │ │ │ │
│ id=1 │ │ id=1 │ │ order_id=1 │ │ id=1 │
│ Alice │ │ user_id=1│ │ product_id=1│ │ iPhone │
└─────────┘ └─────────┘ └─────────────┘ └───────────┘
│ │ │ │
└───────────────┴─────────────────┴──────────────────┘
│
▼
┌─────────────────────────┐
│ Alice, order 1, iPhone │
└─────────────────────────┘
-- Комбинация INNER и LEFT JOIN
SELECT
u.name,
o.id AS order_id,
p.name AS product_name,
c.name AS category_name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id -- Все пользователи
LEFT JOIN order_items oi ON o.id = oi.order_id -- Даже без заказов
INNER JOIN products p ON oi.product_id = p.id -- Только с товарами
LEFT JOIN categories c ON p.category_id = c.id; -- Даже без категории-- ✅ Хороший порядок (логическая цепочка)
SELECT *
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;
-- ❌ Плохой порядок (трудно понять связь)
SELECT *
FROM products p
JOIN order_items oi ON p.id = oi.product_id
JOIN orders o ON oi.order_id = o.id
JOIN users u ON o.user_id = u.id;Критически важное различие для LEFT/RIGHT/FULL JOIN!
-- Условие в ON фильтрует ПРАВУЮ таблицу ДО соединения
SELECT u.name, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
AND o.total_amount > 1000;Результат:
┌─────────┬──────────────┐
│ name │ total_amount │
├─────────┼──────────────┤
│ Alice │ 2500 │ ← Только заказы > 1000
│ Alice │ NULL │ ← Заказ 1000 отфильтрован, но Alice осталась
│ Bob │ NULL │ ← Заказ 500 отфильтрован
│ Carol │ 1500 │
│ David │ NULL │ ← Нет заказов
└─────────┴──────────────┘
✅ Все пользователи сохранены (LEFT JOIN работает)
✅ Только заказы > 1000 показаны (остальные → NULL)
-- Условие в WHERE фильтрует ПОСЛЕ соединения
SELECT u.name, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.total_amount > 1000;Результат:
┌─────────┬──────────────┐
│ name │ total_amount │
├─────────┼──────────────┤
│ Alice │ 2500 │
│ Carol │ 1500 │
└─────────┴──────────────┘
❌ Bob, David исчезли (у них NULL total_amount)
❌ LEFT JOIN превратился в INNER JOIN!
┌─────────────────────────────────────────────────────────────┐
│ ПОРЯДОК ВЫПОЛНЕНИЯ │
├─────────────────────────────────────────────────────────────┤
│ │
│ 1. FROM users u │
│ → Берём всех пользователей │
│ │
│ 2. LEFT JOIN orders ON ... AND o.total > 1000 │
│ → Присоединяем заказы, фильтруя ПРАВУЮ таблицу │
│ → Если заказ не проходит фильтр → NULL (но строка есть)│
│ │
│ 3. WHERE o.total > 1000 │
│ → Фильтруем РЕЗУЛЬТАТ │
│ → Строки с NULL total_amount отбрасываются! │
│ │
└─────────────────────────────────────────────────────────────┘
| Тип JOIN | Условие в ON | Условие в WHERE |
|---|---|---|
| INNER | Фильтрует обе таблицы | Фильтрует результат |
| LEFT | Фильтрует правую таблицу | Фильтрует весь результат |
| RIGHT | Фильтрует левую таблицу | Фильтрует весь результат |
| FULL | Фильтрует соответствующую таблицу | Фильтрует весь результат |
-- ✅ LEFT JOIN с фильтром правой таблицы в ON
SELECT u.name, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
AND o.created_at >= '2026-01-01'; -- Фильтр заказов
-- ✅ LEFT JOIN с фильтром результата в WHERE
SELECT u.name, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Moscow'; -- Фильтр пользователей
-- ✅ Комбинация
SELECT u.name, o.total_amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
AND o.created_at >= '2026-01-01' -- Фильтр заказов (ON)
WHERE u.city = 'Moscow' -- Фильтр пользователей (WHERE)
AND (o.total_amount > 1000 OR o.total_amount IS NULL); -- Сохраняем NULL-- Foreign keys должны быть индексированы!
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);
-- Без индекса: Seq Scan (медленно)
-- С индексом: Index Scan (быстро)EXPLAIN ANALYZE
SELECT u.name, o.total_amount
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.city = 'Moscow';QUERY PLAN
─────────────────────────────────────────────────────────────
Hash Join (cost=25.00..300.00 rows=100 width=40)
(actual time=0.5..15.0 rows=95 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..250.00 rows=10000)
(actual time=0.01..10.0 rows=10000 loops=1)
-> Hash (cost=20.00..20.00 rows=100 width=20)
(actual time=0.3..0.3 rows=100 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 15kB
-> Seq Scan on users u (cost=0.00..20.00 rows=100)
(actual time=0.01..0.2 rows=100 loops=1)
Filter: (city = 'Moscow'::text)
Rows Removed by Filter: 900
Planning Time: 0.5 ms
Execution Time: 15.2 ms
-- 1. Используйте INNER JOIN когда possible (быстрее OUTER)
SELECT * FROM users u
INNER JOIN orders o ON u.id = o.user_id; -- ✅ Быстрее
-- 2. Фильтруйте перед JOIN (уменьшайте размер таблиц)
SELECT u.name, o.total_amount
FROM (SELECT * FROM users WHERE city = 'Moscow') u -- ← Фильтр сначала
JOIN orders o ON u.id = o.user_id;
-- 3. Избегайте функций в условиях JOIN
-- ❌ Медленно:
JOIN orders o ON LOWER(u.email) = LOWER(o.customer_email);
-- ✅ Быстро (с индексом):
JOIN orders o ON u.email = o.customer_email;
-- 4. Используйте EXISTS вместо IN для подзапросов
-- ❌ Медленно:
SELECT * FROM users u
WHERE u.id IN (SELECT user_id FROM orders);
-- ✅ Быстро:
SELECT * FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);| Алгоритм | Когда используется | Производительность |
|---|---|---|
| Nested Loop | Маленькие таблицы, индексы | Хорошо для малых данных |
| Hash Join | Большие таблицы, нет индексов | Отлично для больших данных |
| Merge Join | Отсортированные данные | Отлично если уже отсортировано |
SELECT
u.id,
u.name,
u.email,
COUNT(o.id) AS order_count,
SUM(o.total_amount) AS total_spent,
AVG(o.total_amount) AS avg_order
FROM users u
INNER JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email
HAVING SUM(o.total_amount) > 10000
ORDER BY total_spent DESC
LIMIT 10;SELECT p.id, p.name, p.price
FROM products p
LEFT JOIN order_items oi ON p.id = oi.product_id
WHERE oi.product_id IS NULL;SELECT DISTINCT u.id, u.name, u.email
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= NOW() - INTERVAL '30 days'
ORDER BY u.name;SELECT
c.name AS category,
COUNT(DISTINCT p.id) AS product_count,
COUNT(DISTINCT oi.order_id) AS order_count,
SUM(oi.quantity) AS total_sold,
SUM(oi.quantity * oi.price) AS revenue
FROM categories c
LEFT JOIN products p ON c.id = p.category_id
LEFT JOIN order_items oi ON p.id = oi.product_id
GROUP BY c.id, c.name
ORDER BY revenue DESC;WITH user_cohorts AS (
SELECT
u.id AS user_id,
DATE_TRUNC('month', u.created_at)::date AS cohort_month
FROM users u
),
user_orders AS (
SELECT
u.id AS user_id,
DATE_TRUNC('month', u.created_at)::date AS cohort_month,
DATE_TRUNC('month', o.created_at)::date AS order_month,
EXTRACT(MONTH FROM AGE(o.created_at, u.created_at)) AS months_since_signup
FROM users u
JOIN orders o ON u.id = o.user_id
)
SELECT
uc.cohort_month,
uo.months_since_signup,
COUNT(DISTINCT uo.user_id) AS active_users
FROM user_cohorts uc
JOIN user_orders uo ON uc.user_id = uo.user_id
GROUP BY uc.cohort_month, uo.months_since_signup
ORDER BY uc.cohort_month, uo.months_since_signup;-- Дубликаты email
SELECT
email,
COUNT(*) AS count,
STRING_AGG(id::text, ', ') AS ids
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
-- Дубликаты с подробностями
SELECT u1.id AS id1, u2.id AS id2, u1.email, u1.name, u2.name
FROM users u1
JOIN users u2 ON u1.email = u2.email
AND u1.id < u2.id -- Избегаем дублирования и самосравнения
ORDER BY u1.email;WITH RECURSIVE category_tree AS (
-- Базовый случай: корневые категории
SELECT id, name, parent_id, 0 AS level, name AS path
FROM categories
WHERE parent_id IS NULL
UNION ALL
-- Рекурсивный случай: подкатегории
SELECT c.id, c.name, c.parent_id, ct.level + 1,
ct.path || ' → ' || c.name
FROM categories c
INNER JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree
ORDER BY path;| Что нужно? | Тип JOIN |
|---|---|
| Только совпадения | INNER JOIN |
| Все левые + совпадения | LEFT JOIN |
| Все правые + совпадения | RIGHT JOIN |
| Всё из обеих таблиц | FULL JOIN |
| Все комбинации | CROSS JOIN |
| Таблица с самой собой | SELF JOIN |
| Ситуация | Где условие? |
|---|---|
| INNER JOIN | ON или WHERE (одинаково) |
| LEFT JOIN, фильтр правой таблицы | ON |
| LEFT JOIN, фильтр левой таблицы | WHERE |
| Сохранение NULL в LEFT JOIN | ON |
-- ✅ Индексируйте foreign keys
CREATE INDEX idx_table_fk ON table(foreign_key);
-- ✅ Используйте EXPLAIN ANALYZE
EXPLAIN ANALYZE SELECT ... JOIN ...;
-- ✅ Фильтруйте перед JOIN
SELECT * FROM (SELECT * FROM users WHERE active) u JOIN orders o ON ...
-- ❌ Избегайте функций в ON
JOIN orders o ON LOWER(u.email) = o.email -- Медленно!JOIN — это фундаментальная операция SQL для работы с нормализованными данными.
Запомните:
Следующий шаг: Проверьте знания в разделе вопросов по JOIN!
Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.