Объясните разницу между INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL OUTER JOIN.

INNER JOIN, LEFT JOIN, RIGHT JOIN и FULL OUTER JOIN

Эти четыре типа JOIN определяют, какие строки из двух таблиц будут включены в результат. Различаются тем, как обрабатывают совпадающие и несовпадающие строки.

Визуализация

Таблица A (левая)    Таблица B (правая)
  ID  | Name          ID  | Department
  1   | Alice         1   | HR
  2   | Bob           3   | IT
  3   | Charlie       4   | Finance

Общий ключ: ID

1. INNER JOIN

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

SELECT a.id, a.name, b.department
FROM employees a
INNER JOIN departments b ON a.id = b.id;

-- Результат:
-- ID | Name    | Department
-- 1  | Alice   | HR
-- 3  | Charlie | IT
-- (Bob и Finance исключены, т.к. нет совпадений)

# Python Pandas
import pandas as pd

df_employees = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie']
})

df_departments = pd.DataFrame({
    'id': [1, 3, 4],
    'department': ['HR', 'IT', 'Finance']
})

result = df_employees.merge(df_departments, on='id', how='inner')
# id  name     department
# 1   Alice    HR
# 3   Charlie  IT

# PySpark
result = df_employees.join(df_departments, on='id', how='inner')

Когда использовать: когда нужны только записи, существующие в обеих таблицах (например, заказы с известными клиентами).

2. LEFT JOIN (LEFT OUTER JOIN)

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

SELECT a.id, a.name, b.department
FROM employees a
LEFT JOIN departments b ON a.id = b.id;

-- Результат:
-- ID | Name    | Department
-- 1  | Alice   | HR
-- 2  | Bob     | NULL       <- Bob есть слева, но нет в departments
-- 3  | Charlie | IT

# Python Pandas
result = df_employees.merge(df_departments, on='id', how='left')
# id  name     department
# 1   Alice    HR
# 2   Bob      NaN
# 3   Charlie  IT

# Заполнение пустых значений
result['department'].fillna('Unknown', inplace=True)

Когда использовать: когда нужны все записи из основной таблицы с дополнительной информацией из вспомогательной (например, все сотрудники с их отделами, если известны).

3. RIGHT JOIN (RIGHT OUTER JOIN)

Возвращает все строки из правой таблицы и совпадающие из левой. Противоположность LEFT JOIN.

SELECT a.id, a.name, b.department
FROM employees a
RIGHT JOIN departments b ON a.id = b.id;

-- Результат:
-- ID | Name    | Department
-- 1  | Alice   | HR
-- 3  | Charlie | IT
-- 4  | NULL    | Finance    <- Finance есть справа, но нет в employees

# Python Pandas
result = df_employees.merge(df_departments, on='id', how='right')
# id   name     department
# 1.0  Alice    HR
# 3.0  Charlie  IT
# 4.0  NaN      Finance

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

4. FULL OUTER JOIN (FULL JOIN)

Возвращает все строки из обеих таблиц. Несовпадающим строкам присвоится NULL с обеих сторон.

SELECT a.id, a.name, b.id, b.department
FROM employees a
FULL OUTER JOIN departments b ON a.id = b.id;

-- Результат:
-- a.id | name    | b.id | department
-- 1    | Alice   | 1    | HR
-- 2    | Bob     | NULL | NULL        <- только в левой
-- 3    | Charlie | 3    | IT
-- NULL | NULL    | 4    | Finance     <- только в правой

# Python Pandas
result = df_employees.merge(df_departments, on='id', how='outer')
# id   name     department
# 1.0  Alice    HR
# 2.0  Bob      NaN
# 3.0  Charlie  IT
# 4.0  NaN      Finance

Когда использовать: когда нужны все данные из обеих таблиц, включая несовпадающие (например, полное сравнение двух списков).

Практические примеры

Пример 1: Левый JOIN (INNER)

-- Какие заказы у каких клиентов
SELECT c.customer_id, c.name, COUNT(o.order_id) as order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

-- Включит всех клиентов, даже без заказов (с count=0)

Пример 2: Поиск несовпадений

-- Клиенты БЕЗ заказов (отвалившиеся клиенты)
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Пример 3: FULL OUTER JOIN

-- Сравнение между исходными и загруженными данными
SELECT COALESCE(old.id, new.id) as id,
       old.value as old_value,
       new.value as new_value
FROM old_table old
FULL OUTER JOIN new_table new ON old.id = new.id
WHERE old.value <> new.value OR old.id IS NULL OR new.id IS NULL;

Таблица сравнения

CROSS JOIN (бонус)

-- Декартово произведение (каждая строка левой с каждой из правой)
SELECT a.id, a.name, b.id, b.department
FROM employees a
CROSS JOIN departments b;

-- Результат: 3 × 3 = 9 строк
-- Используется редко (опасно на больших таблицах)

На примере рейтингов

df_movies = pd.DataFrame({
    'movie_id': [1, 2, 3],
    'title': ['Dune', 'Matrix', 'Inception']
})

df_ratings = pd.DataFrame({
    'movie_id': [1, 2, 4],
    'rating': [9.2, 8.7, 7.5]
})

# INNER: только фильмы с рейтингами
inner = df_movies.merge(df_ratings, on='movie_id', how='inner')
# Inception выпадет

# LEFT: все фильмы, рейтинг=NaN если нет
left = df_movies.merge(df_ratings, on='movie_id', how='left')
# Inception будет с рейтингом NaN

# FULL: все фильмы и все рейтинги
full = df_movies.merge(df_ratings, on='movie_id', how='outer')
# movie_id=4 появится с title=NaN

Производительность

-- INNER JOIN обычно быстрее
EXPLAIN ANALYZE
SELECT * FROM a INNER JOIN b ON a.id = b.id;

-- LEFT/RIGHT/FULL требуют больше памяти
EXPLAIN ANALYZE
SELECT * FROM a LEFT JOIN b ON a.id = b.id;

Вывод: INNER JOIN используется когда нужны только совпадающие данные, LEFT JOIN — когда нужна полнота левой таблицы, FULL OUTER JOIN — редко, для полного сравнения. ПРАВИЛО: начните с INNER, добавьте LEFT/FULL только если требуется полнота данных.