Какие знаешь типы JOIN?

Question

claude-haiku-4.5 · Accepted Answer

## Типы JOIN в SQL

JOIN — это операция объединения данных из двух и более таблиц по условию связи. Знание всех типов критично для работы Data Scientist'а. Разберу все основные и продвинутые варианты.

### Основные 4 типа JOIN

**1. INNER JOIN (Внутреннее объединение)**

```sql
-- Синтаксис
SELECT orders.order_id, customers.name, orders.amount
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

-- Визуально:
orders:           customers:
order_id cust_id  id name
1        101      101 Alice
2        102      102 Bob
3        103      103 Charlie
4        999      (нет в customers)

-- Результат INNER JOIN:
order_id cust_id name
1        101     Alice
2        102     Bob
3        103     Charlie
-- order_id=4 не попадает (нет соответствия в customers)
```

**Особенность:** Возвращает только строки, где есть совпадение в обеих таблицах.

```python
# Аналог в pandas
df_orders = pd.DataFrame({
    'order_id': [1, 2, 3, 4],
    'customer_id': [101, 102, 103, 999]
})

df_customers = pd.DataFrame({
    'id': [101, 102, 103],
    'name': ['Alice', 'Bob', 'Charlie']
})

# INNER JOIN
result = df_orders.merge(df_customers, 
                        left_on='customer_id', 
                        right_on='id',
                        how='inner')  # inner — по умолчанию
print(result)
```

**2. LEFT JOIN (Левое внешнее объединение)**

```sql
-- Синтаксис
SELECT orders.order_id, customers.name, orders.amount
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id;

-- Результат LEFT JOIN:
order_id cust_id name
1        101     Alice
2        102     Bob
3        103     Charlie
4        999     NULL  ← order_id=4 сохраняется, но name=NULL
```

**Особенность:** Все строки из LEFT таблицы (orders) + совпадения из RIGHT (customers).

```python
# В pandas
result = df_orders.merge(df_customers,
                        left_on='customer_id',
                        right_on='id',
                        how='left')

# Результат:
# order_id  customer_id  id   name
# 0        1           101    101.0  Alice
# 1        2           102    102.0  Bob
# 2        3           103    103.0  Charlie
# 3        4           999    NaN    NaN
```

**3. RIGHT JOIN (Правое внешнее объединение)**

```sql
-- Синтаксис
SELECT orders.order_id, customers.name
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;

-- Результат RIGHT JOIN:
order_id name
1        Alice
2        Bob
3        Charlie
NULL     Dave  ← customer (Dave) есть, но заказов нет
```

**Особенность:** Все строки из RIGHT таблицы (customers) + совпадения из LEFT (orders).

```python
# В pandas
result = df_orders.merge(df_customers,
                        left_on='customer_id',
                        right_on='id',
                        how='right')
```

**4. FULL OUTER JOIN (Полное объединение)**

```sql
-- Синтаксис
SELECT orders.order_id, customers.name
FROM orders
FULL OUTER JOIN customers ON orders.customer_id = customers.id;

-- Результат FULL OUTER JOIN:
order_id name
1        Alice
2        Bob
3        Charlie
4        NULL  ← order без customer
NULL     Dave  ← customer без order
```

**Особенность:** Все строки из обеих таблиц.

```python
# В pandas
result = df_orders.merge(df_customers,
                        left_on='customer_id',
                        right_on='id',
                        how='outer')
```

### Визуальное представление (диаграмма Венна)

```
Таблица A | Таблица B

INNER JOIN:        Только пересечение
     ___
    /   \          ✓ ✓ ✓
   |  ●  |         (совпадения)
    \___/

LEFT JOIN:         Вся таблица A + пересечение
   _____
  /  ●  |\         ✓ ✓ ✓ (A)
  | ●●●●|         ✓ ✓ ✓ (пересечение)
  \_____|/         с NULL справа

RIGHT JOIN:        Пересечение + вся таблица B
   _____
  /|●  \           ✓ ✓ ✓ (пересечение)
  |●●●●|
  \|___/           с NULL слева + ✓ ✓ ✓ (B)

FULL OUTER JOIN:   Всё
   _____
  / \   / \        ✓ ✓ ✓ (всё из A)
  |●  ●  ●|        ✓ ✓ ✓ (пересечение)
  \ /   \ /        ✓ ✓ ✓ (всё из B)
   -----            с NULLами где нет совпадений
```

### Продвинутые типы JOIN

**5. CROSS JOIN (Декартово произведение)**

```sql
-- Синтаксис
SELECT orders.order_id, customers.name
FROM orders
CROSS JOIN customers;

-- Результат: каждая строка из orders сочетается с каждой из customers
order_id name
1        Alice
1        Bob
1        Charlie
2        Alice
2        Bob
2        Charlie
3        Alice
...
-- Если в orders 4 строки, в customers 3 строки
-- Результат: 4 * 3 = 12 строк
```

```python
# В pandas
df_orders['key'] = 1
df_customers['key'] = 1
result = df_orders.merge(df_customers, on='key').drop('key', axis=1)
```

**6. SELF JOIN (Объединение таблицы с самой собой)**

```sql
-- Пример: найти сотрудников и их руководителей
SELECT e1.name as employee, e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

-- Таблица employees:
employee_id name       manager_id
1           Alice      NULL
2           Bob        1
3           Charlie    1
4           Dave       2

-- Результат:
employee manager
Alice    NULL
Bob      Alice
Charlie  Alice
Dave     Bob
```

```python
# В pandas
df_emp = pd.DataFrame({
    'employee_id': [1, 2, 3, 4],
    'name': ['Alice', 'Bob', 'Charlie', 'Dave'],
    'manager_id': [None, 1, 1, 2]
})

result = df_emp.merge(
    df_emp.rename(columns={'employee_id': 'manager_id', 'name': 'manager_name'}),
    on='manager_id',
    how='left'
)[['name', 'manager_name']]
```

**7. Anti-Join (в SQL как NOT IN или NOT EXISTS)**

```sql
-- Найти заказы, у которых нет соответствующего покупателя
SELECT orders.order_id, orders.customer_id
FROM orders
LEFT JOIN customers ON orders.customer_id = customers.id
WHERE customers.id IS NULL;

-- Или эквивалентно:
SELECT order_id FROM orders
WHERE customer_id NOT IN (SELECT id FROM customers);
```

```python
# В pandas
result = df_orders[~df_orders['customer_id'].isin(df_customers['id'])]
```

**8. Semi-Join (SELECT с WHERE IN)**

```sql
-- Найти покупателей, которые сделали заказы
SELECT DISTINCT customers.id, customers.name
FROM customers
WHERE customers.id IN (
    SELECT DISTINCT customer_id FROM orders
);

-- Более эффективно с INNER JOIN (но без дублей):
SELECT DISTINCT customers.id, customers.name
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;
```

```python
# В pandas
result = df_customers[df_customers['id'].isin(df_orders['customer_id'])]
```

### Практический пример: Анализ данных

```sql
-- Задача: найти сумму заказов для каждого покупателя
-- Включить всех покупателей, даже без заказов

SELECT 
    c.id,
    c.name,
    COUNT(o.order_id) as order_count,
    COALESCE(SUM(o.amount), 0) as total_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_amount DESC;

-- Результат:
id   name      order_count  total_amount
101  Alice     3            3500
102  Bob       2            2100
103  Charlie   1            500
104  Dave      0            0  ← Без заказов
```

```python
# В pandas
result = df_customers.merge(
    df_orders.groupby('customer_id').agg({
        'order_id': 'count',
        'amount': 'sum'
    }).reset_index(),
    left_on='id',
    right_on='customer_id',
    how='left'
)

result.rename(columns={
    'order_id': 'order_count',
    'amount': 'total_amount'
}, inplace=True)

result['total_amount'].fillna(0, inplace=True)
result = result.sort_values('total_amount', ascending=False)
```

### Производительность и оптимизация

```sql
-- Плохо: FULL OUTER JOIN с WHERE (теряет смысл)
SELECT *
FROM orders o
FULL OUTER JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NOT NULL;  -- ← Превращается в INNER JOIN!

-- Хорошо: сразу INNER JOIN
SELECT *
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- Оптимизация: индексы на колонках в JOIN
CREATE INDEX idx_customer_id ON orders(customer_id);
CREATE INDEX idx_customer_pk ON customers(id);
```

### Чеклист для Data Scientist

- **INNER JOIN** — общие данные (самый частый)
- **LEFT JOIN** — все данные слева + совпадения
- **RIGHT JOIN** — все данные справа + совпадения (редко)
- **FULL OUTER JOIN** — всё (осторожно с NULL'ами)
- **CROSS JOIN** — комбинаторика (редко)
- **SELF JOIN** — иерархии, связи в таблице
- **Anti-Join** — исключения
- **Semi-Join** — фильтрация по наличию

### Итог

Для Data Scientist'а критично знать:
1. **INNER JOIN** — для связи таблиц
2. **LEFT JOIN** — для сохранения всех данных с левой стороны
3. Различие между **NULL в результате** и **отсутствием строк**
4. Как JOIN влияет на количество строк в результате
5. Производительность — индексы на колонках объединения

Какие знаешь типы JOIN?

Комментарии (2)

Типы JOIN в SQL

Основные 4 типа JOIN

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

Продвинутые типы JOIN

Практический пример: Анализ данных

Производительность и оптимизация

Чеклист для Data Scientist

Итог