Что такое FULL JOIN?

Что такое FULL JOIN?

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

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

Механизм работы можно описать так:

1. Сначала выполняются условия соединения (ON), возвращаются все совпадающие пары строк.
2. Затем добавляются все несовпадающие строки из левой таблицы, с NULL в полях правой таблицы.
3. После этого добавляются все несовпадающие строки из правой таблицы, с NULL в полях левой таблицы.

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

Предположим, есть две таблицы: Employees (сотрудники) и Projects (проекты).

Таблица Employees:

| id | name     |
|----|----------|
| 1  | Иван     |
| 2  | Мария    |
| 3  | Алексей  |

Таблица Projects:

| id | project_name | employee_id |
|----|--------------|-------------|
| 1  | Проект А     | 1           |
| 2  | Проект Б     | 3           |
| 3  | Проект В     | NULL        |

Запрос с FULL JOIN:

SELECT 
    e.name AS employee_name,
    p.project_name AS project
FROM 
    Employees e
FULL JOIN 
    Projects p ON e.id = p.employee_id;

Результат:

| employee_name | project   |
|---------------|-----------|
| Иван          | Проект А  |
| Мария         | NULL      |
| Алексей       | Проект Б  |
| NULL          | Проект В  |

В результате:

• Иван и Алексей имеют совпадения по условию e.id = p.employee_id, поэтому их строки объединены.
• Мария не имеет связанного проекта, поэтому в столбце project указан NULL.
• Проект В не назначен ни одному сотруднику, поэтому в столбце employee_name указан NULL.

Особенности и нюансы

• Поддержка в СУБД: FULL JOIN поддерживается не во всех системах управления базами данных. Например, в MySQL его нет, но можно эмулировать через UNION LEFT JOIN и RIGHT JOIN. В PostgreSQL, SQL Server, Oracle он поддерживается напрямую.
• Условия соединения: Как и в других JOIN, условие задаётся после ON. Если условие не выполняется, строки всё равно включаются в результат с NULL.
• Производительность: FULL JOIN может быть ресурсоёмким, особенно на больших таблицах, так требует обработки всех строк из обоих источников. Важно иметь индексы на полях соединения для оптимизации.

Сравнение с другими JOIN

• INNER JOIN: Возвращает только совпадающие строки из обеих таблиц.
• LEFT JOIN: Возвращает все строки из левой таблицы и совпадающие из правой, с NULL при отсутствии совпадений.
• RIGHT JOIN: Возвращает все строки из правой таблицы и совпадающие из левой, с NULL при отсутствии совпадений.
• FULL JOIN: Комбинация LEFT и RIGHT JOIN, возвращает все строки из обеих таблиц.

Практическое применение

В контексте тестирования (QA) понимание FULL JOIN важно для:

• Верификации данных: Проверка полноты и целостности данных при интеграции систем, например, при сравнении списков пользователей из двух источников.
• Аналитики тестовых результатов: Например, сравнение логов тестовых прогонов из разных сред для выявления расхождений.
• Написания сложных SQL-запросов в автотестах, которые проверяют корректность связей в базе данных.

Эмуляция FULL JOIN в MySQL

Поскольку MySQL не поддерживает FULL JOIN напрямую, его можно реализовать так:

SELECT e.name AS employee_name, p.project_name AS project
FROM Employees e
LEFT JOIN Projects p ON e.id = p.employee_id
UNION
SELECT e.name AS employee_name, p.project_name AS project
FROM Employees e
RIGHT JOIN Projects p ON e.id = p.employee_id;

Этот запрос объединяет результаты LEFT JOIN и RIGHT JOIN через UNION, удаляя дубликаты (если нужны все строки, включая дубли, используйте UNION ALL).

Таким образом, FULL JOIN — мощный инструмент для работы с данными, позволяющий получить полную картину по двум таблицам, что особенно полезно в задачах анализа и отладки. В QA-автоматизации его применение актуально при проверках сложных данных, где требуется убедиться в отсутствии потерянных или некорректных связей.