Какие знаешь виды JOIN?

Какие знаешь виды JOIN?

JOIN — это операция в SQL для объединения данных из двух или более таблиц по общему ключу. Правильный выбор типа JOIN критичен для производительности и корректности запросов.

1. INNER JOIN

Возвращает только строки, которые есть в ОБЕИХ таблицах:

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

Диаграмма Венна: пересечение двух множеств (жёлтый центр)

Когда использовать:

• Нужны только записи, имеющие соответствие в обеих таблицах
• Стандартный случай для связанных данных
• Самый часто используемый JOIN

2. LEFT JOIN (LEFT OUTER JOIN)

Возвращает ВСЕ строки из левой таблицы + соответствующие из правой (или NULL):

SELECT 
    u.user_id,
    u.name,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.amount), 0) as total_spent
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.name;

Результат: все пользователи, даже без заказов (order_count = 0)

Когда использовать:

• Нужны все записи из основной таблицы
• Поиск записей БЕЗ соответствия (WHERE o.order_id IS NULL)
• Сводка данных (количество заказов на пользователя)

3. RIGHT JOIN (RIGHT OUTER JOIN)

Возвращает ВСЕ строки из правой таблицы + соответствующие из левой:

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

Эквивалент: LEFT JOIN с переставленными таблицами:

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

Когда использовать: редко (обычно переделывают на LEFT JOIN)

4. FULL OUTER JOIN

Возвращает все строки из ОБЕИХ таблиц (объединение):

SELECT 
    COALESCE(u.user_id, o.user_id) as user_id,
    u.name,
    o.order_id,
    o.amount
FROM users u
FULL OUTER JOIN orders o ON u.user_id = o.user_id
ORDER BY user_id;

Результат:

• Пользователи с заказами (левая + правая части)
• Пользователи БЕЗ заказов (левая часть, u.* = NULL)
• Заказы БЕЗ пользователя (правая часть, o.* = NULL)

Когда использовать:

• Полная синхронизация между таблицами
• Поиск несогласованности (orphaned records)
• Data reconciliation

5. CROSS JOIN

Это декартово произведение: каждая строка левой таблицы соединяется с КАЖДОЙ строкой правой:

-- Все возможные комбинации цветов и размеров
SELECT 
    c.color,
    s.size
FROM colors c
CROSS JOIN sizes s;
-- Если цветов 10, размеров 5 → 50 комбинаций

Когда использовать:

• Генерирование всех комбинаций
• Создание расписания
• Генерирование дат × товары

⚠️ Осторожно: может привести к explosion данных!

6. NATURAL JOIN

Автоматически соединяет по общим колонкам (редко используется):

-- Если обе таблицы имеют 'user_id'
SELECT *
FROM users
NATURAL JOIN orders;

-- Эквивалент:
SELECT *
FROM users
INNER JOIN orders USING(user_id);

Проблемы: опасно, если случайно есть общая колонка

7. SELF JOIN

Объединение таблицы с самой собой:

-- Найти менеджеров и их подчинённых
SELECT 
    e1.name as employee,
    e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id
ORDER BY e1.name;

Когда использовать:

• Иерархические структуры (employee-manager)
• Граф отношений (друзья, рекомендации)
• Сравнение строк в одной таблице

8. ANTI-JOIN (логический)

Вернуть строки из левой таблицы, которых НЕ в правой:

-- Пользователи БЕЗ заказов
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o.order_id IS NULL;

-- Альтернатива с NOT IN
SELECT *
FROM users
WHERE user_id NOT IN (SELECT DISTINCT user_id FROM orders);

-- Альтернатива с NOT EXISTS (более эффективно)
SELECT *
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

9. SEMI-JOIN (логический)

Вернуть строки из левой таблицы, которые есть в правой (без дублей):

-- Пользователи С заказами
SELECT DISTINCT u.*
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id;

-- Или с EXISTS (более эффективно)
SELECT *
FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

10. Сравнение производительности

import pandas as pd
import numpy as np
import time

# Пример: 10М пользователей, 100М заказов
users = pd.DataFrame({
    'user_id': range(1, 10_000_001),
    'name': [f'User{i}' for i in range(10_000_000)]
})

orders = pd.DataFrame({
    'order_id': range(1, 100_000_001),
    'user_id': np.random.randint(1, 10_000_001, 100_000_000),
    'amount': np.random.uniform(10, 1000, 100_000_000)
})

# Тест производительности
start = time.time()
result = users.merge(orders, on='user_id', how='inner')  # INNER JOIN
print(f"INNER JOIN: {time.time() - start:.2f}s, rows: {len(result)}")

start = time.time()
result = users.merge(orders, on='user_id', how='left')   # LEFT JOIN
print(f"LEFT JOIN: {time.time() - start:.2f}s, rows: {len(result)}")

11. Таблица выбора JOIN

12. Best Practices

-- ❌ Плохо: FULL OUTER JOIN может быть медленнее
FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id

-- ✅ Хорошо: явно указать, что нужно
FROM t1 LEFT JOIN t2 ON t1.id = t2.id WHERE t2.id IS NULL
UNION
FROM t2 LEFT JOIN t1 ON t1.id = t2.id WHERE t1.id IS NULL

-- ✅ Индексы на колонках JOIN
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- ✅ NOT EXISTS быстрее чем NOT IN с NULL
SELECT * FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id);

Правильный выбор JOIN — основа эффективных SQL-запросов в Data Engineering.