В чем разница между видами JOIN?

Разница между видами JOIN

JOIN — это один из самых фундаментальных операторов SQL, и за 10+ лет я видел, как неправильное использование JOIN может превратить быстрый запрос в 10-минутный кошмар. Давайте разберёмся во всех типах.

Визуальное представление

Представьте две таблицы: Users и Orders.

Users: [U1, U2, U3, U4, U5]
Orders: [O1-U1, O2-U2, O3-U2, O4-U6]

Только U1, U2 и U3 имеют заказы. U4, U5 без заказов. U6 в Orders но не в Users.

1. INNER JOIN

Возвращает только совпадающие записи из обеих таблиц.

SELECT u.id, u.name, o.order_id, o.amount
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

// Результат: только U1, U2 (с их заказами)
// U3 с заказом, U4, U5 (без заказов) — исключены
// O4 (U6 не существует) — исключён

Использование в JPA Hibernate:

@Entity
public class User {
    @Id private Long id;
    private String name;
    
    @OneToMany(fetch = FetchType.LAZY)
    private List<Order> orders;
}

List<User> users = session.createQuery(
    "SELECT u FROM User u JOIN u.orders o WHERE o.amount > :minAmount",
    User.class
)
.setParameter("minAmount", 100.0)
.getResultList();
// Вернёт только юзеров с заказами > 100

Когда использовать: ✅ Нужны только совпадающие данные ✅ Фильтрация по связанной таблице ✅ Большинство случаев в production

2. LEFT JOIN (LEFT OUTER JOIN)

Возвращает все записи из левой таблицы и совпадающие из правой.

SELECT u.id, u.name, o.order_id, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

// Результат:
// U1 с его заказом
// U2 с его заказами
// U3 с его заказом
// U4 с NULL в колонках orders (нет заказов)
// U5 с NULL в колонках orders (нет заказов)
// U6 НЕ включается (он не в левой таблице)

Реальный пример:

// Найти всех пользователей и их последний заказ (если есть)
SELECT u.id, u.name, o.order_id, o.created_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id;

// Это покажет активных и неактивных пользователей одновременно

Использование в JPA:

List<Object[]> results = session.createQuery(
    "SELECT u.id, u.name, o.order_id FROM User u " +
    "LEFT JOIN u.orders o",
    Object[].class
).getResultList();

for (Object[] row : results) {
    Long userId = (Long) row[0];
    String name = (String) row[1];
    Long orderId = (Long) row[2]; // null если нет заказов
}

Когда использовать: ✅ Нужно все записи из левой таблицы ✅ Опциональные связи (может не быть связанных данных) ✅ Подсчёт: "все пользователи и сколько у них заказов"

3. RIGHT JOIN (RIGHT OUTER JOIN)

Зеркало LEFT JOIN — все записи из правой таблицы и совпадающие из левой.

SELECT u.id, u.name, o.order_id, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

// Результат:
// U1 с его заказом
// U2 с его заказами
// U3 с его заказом
// U6 с NULL в колонках users (пользователь не существует)
// U4, U5 НЕ включаются (у них нет заказов)

Практический совет: RIGHT JOIN редко используется. Обычно можно просто переписать как LEFT JOIN с перёрыванием таблиц:

// Вместо RIGHT JOIN
FROM users u RIGHT JOIN orders o ON u.id = o.user_id;

// Пишут как LEFT JOIN с переменой порядка
FROM orders o LEFT JOIN users u ON u.id = o.user_id;

Когда использовать: ✅ Данные справа критичны, слева опциональны ✅ Редко! Обычно переписывают как LEFT JOIN

4. FULL OUTER JOIN (FULL JOIN)

Возвращает ВСЕ записи из обеих таблиц, совпадающие объединяет, не совпадающие дополняет NULL.

SELECT u.id, u.name, o.order_id, o.amount
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

// Результат включает:
// U1 с его заказом
// U2 с его заказами
// U3 с его заказом
// U4 с NULL в колонках orders
// U5 с NULL в колонках orders
// U6 с NULL в колонках users

Важно: MySQL НЕ поддерживает FULL OUTER JOIN! Приходится использовать UNION с LEFT и RIGHT JOIN.

// PostgreSQL
SELECT u.id, u.name, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

// MySQL (нет FULL OUTER)
SELECT u.id, u.name, o.order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
UNION
SELECT u.id, u.name, o.order_id
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

Когда использовать: ✅ Reconciliation (сверка данных) ✅ Data migration (проверка консистентности) ✅ Найти orphaned records (заказы без пользователей)

5. CROSS JOIN

Декартово произведение — каждая строка левой таблицы соединяется с каждой строкой правой.

SELECT u.name, p.product_name
FROM users u
CROSS JOIN products p;

// Если users = 5, products = 10
// Результат = 5 * 10 = 50 строк

Опасность:

// Если случайно забыли ON условие — получится CROSS JOIN!
SELECT *
FROM users u, orders o; // CROSS JOIN!
// На production с миллионами записей = system crash

Когда использовать: ✅ Генерирование всех комбинаций ✅ Матрицы данных ❌ Обычно НЕ используется

6. SELF JOIN

Таблица соединяется сама с собой. Технически не отдельный тип, но важный паттерн.

// Найти пары сотрудников в одном отделе
SELECT e1.name, e2.name
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
WHERE e1.id < e2.id; // Избегаем дубликатов

// Найти иерархию: сотрудник и его менеджер
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Сравнительная таблица

┌──────────────┬─────────────┬──────────────┬────────────────────┐
│ JOIN тип     │ Левая (все) │ Правая (все) │ Только совпадения  │
├──────────────┼─────────────┼──────────────┼────────────────────┤
│ INNER        │      ✓      │      ✓       │        Да          │
│ LEFT         │      ✓      │      ✓       │        Нет         │
│ RIGHT        │      ✓      │      ✓       │        Нет         │
│ FULL OUTER   │      ✓      │      ✓       │        Нет         │
│ CROSS        │      ✓      │      ✓       │        N/A         │
└──────────────┴─────────────┴──────────────┴────────────────────┘

Performance советы

1. Индексы на JOIN колонки:

// Без индекса на orders.user_id — очень медленно
CREATE INDEX idx_orders_user_id ON orders(user_id);

2. Выбирайте правильный тип JOIN:

// ❌ Плохо: FULL OUTER JOIN требует более дорогой обработки
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

// ✅ Хорошо: если нужны только активные пользователи
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

3. Используйте EXPLAIN для анализа:

EXPLAIN ANALYZE
SELECT *
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
// Покажет план выполнения и время

Вывод: INNER JOIN в 90% случаев. LEFT JOIN для опциональных данных. FULL OUTER JOIN редко. RIGHT JOIN практически не используется.