Как работает INNER JOIN?

Как работает INNER JOIN в реляционных базах данных

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

Логическая модель работы

Операция INNER JOIN работает по следующему алгоритму:

1. Для каждой строки в первой (левой) таблице система ищет все строки во второй (правой) таблице, которые удовлетворяют условию соединения (ON или USING).
2. Если совпадение найдено, строки из обеих таблиц объединяются в одну результирующую строку.
3. Если для строки из левой таблицы нет ни одного соответствия в правой, такая строка не включается в итоговый набор данных.

Синтаксис и примеры

Базовый синтаксис в SQL:

SELECT column_list
FROM table1
INNER JOIN table2
    ON table1.matching_column = table2.matching_column;
-- или с использованием USING, если имя столбца идентично
SELECT column_list
FROM table1
INNER JOIN table2
    USING (matching_column);

Рассмотрим на практическом примере. У нас есть две таблицы:

Таблица Users:

Таблица Departments:

Запрос с INNER JOIN:

SELECT Users.name AS user_name, Departments.name AS department_name
FROM Users
INNER JOIN Departments
    ON Users.department_id = Departments.id;

Результат:

Обратите внимание:

• Строка с пользователем Петр не попала в результат, так как его department_id равен NULL и не соответствует ни одному id в таблице Departments.
• Отдел Бухгалтерия не появился в результате, так как на него нет ссылок из таблицы Users.

Ключевые особенности и внутренняя реализация

С точки зрения производительности, СУБД может выполнять INNER JOIN с помощью различных алгоритмов, выбор которых зависит от размера таблиц, наличия индексов и статистики:

• Nested Loops Join: Для каждой строки внешней таблицы сканируется внутренняя таблица. Эффективно при небольших наборах данных или наличии индексов на столбцах соединения.
• Hash Join: Создает хэш-таблицу по столбцам соединения для одной из таблиц, затем сканирует вторую таблицу, вычисляя хэш и находя совпадения. Часто используется для больших таблиц без индексов.
• Merge Join: Предварительно сортирует обе таблицы по ключу соединения, а затем выполняет однократное слияние, подобно алгоритму слияния двух отсортированных массивов. Эффективен, если данные уже отсортированы или есть подходящие индексы.

Важные аспекты:

• Условие связи (ON) является обязательным. Без него в большинстве СУБД запрос завершится ошибкой, либо (в случае CROSS JOIN) вернет декартово произведение строк.
• Последовательность соединения в запросе (table1 JOIN table2) для INNER JOIN с точки зрения результата не важна, так как это коммутативная операция. Однако для оптимизатора запросов порядок может влиять на выбранный план выполнения и производительность.
• INNER — ключевое слово по умолчанию. Запрос FROM A JOIN B ON ... без указания типа подразумевает именно внутреннее соединение.
• Использование с агрегацией и фильтрацией: INNER JOIN выполняется до применения операторов WHERE и GROUP BY. Это может критически влиять на результат, если фильтрация по NULL выполняется в WHERE.

Отличие от других типов JOIN

• LEFT JOIN: Вернет все строки из левой таблицы, даже если для них нет совпадений справа. Вместо отсутствующих значений будут NULL.
• RIGHT JOIN: Аналогично LEFT JOIN, но приоритет отдается правой таблице.
• FULL OUTER JOIN: Вернет все строки из обеих таблиц, объединяя совпадающие и дополняя NULL при отсутствии совпадения.
• CROSS JOIN: Возвращает декартово произведение всех строк обеих таблиц без какого-либо условия.

Таким образом, INNER JOIN служит мощным инструментом для извлечения связанных данных, хранящихся в нормализованном виде в разных таблицах, и является краеугольным камнем при построении реляционных запросов.