Пошук уроків, статей та іншого контенту
Порівняєте RIGHT JOIN і FULL JOIN та навчитеся зберігати незбіглі записи з однієї або обох таблиць.
RIGHT JOIN і FULL JOINRIGHT JOIN та FULL JOIN використовують, коли потрібно зберегти рядки, для яких не знайшлося відповідності в іншій таблиці.
На відміну від INNER JOIN, ці об’єднання не відкидають усі незбіглі записи:
RIGHT JOIN зберігає всі рядки правої таблиці;
FULL JOIN зберігає всі рядки обох таблиць.
Якщо для рядка немає відповідності, PostgreSQL заповнює стовпці іншої таблиці значеннями NULL.
RIGHT JOINСинтаксис:
SELECT columns
FROM left_table
RIGHT JOIN right_table
ON join_condition;Права таблиця — це таблиця, яка вказана після RIGHT JOIN. Усі її рядки потраплять до результату, навіть якщо відповідного рядка в лівій таблиці немає.
Створимо таблиці відділів і працівників:
CREATE TABLE departments (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE employees (
id integer PRIMARY KEY,
name text NOT NULL,
department_id integer REFERENCES departments(id)
);
INSERT INTO departments (id, name) VALUES
(1, 'Розробка'),
(2, 'Дизайн'),
(3, 'Маркетинг');
INSERT INTO employees (id, name, department_id) VALUES
(1, 'Олена', 1),
(2, 'Андрій', 1),
(3, 'Максим', NULL);Потрібно показати всі відділи, навіть якщо в них немає працівників:
SELECT
employees.name AS employee_name,
departments.name AS department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id
ORDER BY departments.id, employees.name;Результат міститиме приблизно такі рядки:
Олена — Розробка
Андрій — Розробка
NULL — Дизайн
NULL — Маркетинг
Відділи Дизайн і Маркетинг збереглися, хоча працівників із такими department_id немає.
У цьому запиті:
employees — ліва таблиця;
departments — права таблиця;
усі записи з departments гарантовано потрапляють до результату.
RIGHT JOIN як альтернативний записRIGHT JOIN можна переписати за допомогою LEFT JOIN, помінявши таблиці місцями:
SELECT
employees.name AS employee_name,
departments.name AS department_name
FROM departments
LEFT JOIN employees
ON employees.department_id = departments.id
ORDER BY departments.id, employees.name;Цей запит повертає той самий результат. На практиці LEFT JOIN часто використовують частіше, оскільки зліва зазвичай розміщують таблицю, записи якої потрібно зберегти. Проте RIGHT JOIN корисний, коли логіка запиту природніше читається справа наліво.
FULL JOINСинтаксис:
SELECT columns
FROM left_table
FULL JOIN right_table
ON join_condition;FULL JOIN об’єднує поведінку LEFT JOIN і RIGHT JOIN:
зберігає всі рядки лівої таблиці;
зберігає всі рядки правої таблиці;
поєднує рядки, якщо умова ON виконується;
підставляє NULL для відсутньої частини рядка.
SELECT
employees.name AS employee_name,
employees.department_id,
departments.name AS department_name,
departments.id AS department_id
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id
ORDER BY departments.id NULLS LAST, employees.id NULLS LAST;У результаті будуть:
працівники, які належать до відділів;
відділи без працівників;
працівники, які не належать до жодного відділу.
Для наведених даних:
Олена та Андрій поєднаються з відділом Розробка;
Дизайн і Маркетинг матимуть NULL у стовпці працівника;
Максим матиме NULL у стовпцях відділу.
Таким чином, FULL JOIN допомагає побачити всі записи з обох джерел, зокрема всі невідповідності.
Нехай є дві таблиці:
A — ліва;
B — права.
Тоді:
RIGHT JOIN повертає всі рядки з B і відповідні рядки з A;
FULL JOIN повертає всі рядки з A і всі рядки з B;
збіглі рядки об’єднуються в один рядок;
незбіглі рядки доповнюються NULL.
Спрощено:
RIGHT JOIN: усі записи праворуч
FULL JOIN: усі записи ліворуч і праворучNULLЗначення NULL означає, що відповідного рядка не знайдено. Перевіряти його потрібно за допомогою IS NULL або IS NOT NULL, а не через оператор =.
Наприклад, знайти відділи без працівників:
SELECT
departments.id,
departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id
WHERE employees.id IS NULL
ORDER BY departments.id;Умова employees.id IS NULL залишає тільки ті рядки, для яких працівник не знайшовся.
За допомогою FULL JOIN можна знайти всі невідповідності з обох боків:
SELECT
employees.name AS employee_name,
departments.name AS department_name
FROM employees
FULL JOIN departments
ON employees.department_id = departments.id
WHERE employees.id IS NULL
OR departments.id IS NULL;Цей запит поверне:
відділи без працівників;
працівників без відповідного відділу.
ON і умова в WHEREДля зовнішніх об’єднань важливо, де саме розміщено фільтр.
Наприклад, потрібно показати всі відділи та лише працівників із певним іменем. Якщо фільтр додати до WHERE, рядки без працівника можуть зникнути:
SELECT
employees.name AS employee_name,
departments.name AS department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id
WHERE employees.name LIKE 'О%';Для відділів без працівника employees.name дорівнює NULL. Умова employees.name LIKE 'О%' не виконується, тому такі відділи будуть відфільтровані.
Якщо потрібно зберегти всі відділи, а обмежити лише спосіб поєднання працівників, умову можна перенести до ON:
SELECT
employees.name AS employee_name,
departments.name AS department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id
AND employees.name LIKE 'О%'
ORDER BY departments.id;Тепер усі відділи залишаються в результаті. Працівник з’явиться лише тоді, коли його ім’я починається з О.
Це правило особливо важливе для RIGHT JOIN і FULL JOIN:
умова в ON впливає на те, які рядки поєднуються;
умова в WHERE фільтрує вже готовий результат, разом із рядками, де є NULL.
JOINВикористовуйте RIGHT JOIN, коли:
потрібно зберегти всі записи правої таблиці;
для лівої таблиці відповідність може бути відсутньою;
структура запиту з правою таблицею як основною читається зрозуміліше.
Використовуйте FULL JOIN, коли:
потрібно зберегти записи з обох таблиць;
необхідно знайти невідповідності в обох джерелах;
таблиці потрібно повністю зіставити, навіть якщо частина записів не має пари.
Такий запит може прибрати незбіглі рядки:
SELECT
employees.name,
departments.name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id
WHERE employees.department_id = 1;Через WHERE рядки без працівника відсіюються. Якщо потрібно зберегти всі відділи, обмеження краще додати до ON або правильно сформулювати умову з урахуванням NULL.
NULL через =Неправильно:
WHERE employees.id = NULLПравильно:
WHERE employees.id IS NULLNULL не є звичайним значенням, тому для перевірки використовують IS NULL та IS NOT NULL.
У запиті:
FROM employees
RIGHT JOIN departmentsправою є departments, а не employees. Саме всі рядки з departments будуть збережені.
Якщо одному відділу відповідає кілька працівників, відділ з’явиться в результаті кілька разів — по одному разу для кожного працівника. JOIN не видаляє дублікати автоматично.
RIGHT JOIN зберігає всі рядки правої таблиці.
FULL JOIN зберігає всі рядки обох таблиць.
Для незбіглих частин результату PostgreSQL повертає NULL.
RIGHT JOIN можна замінити на LEFT JOIN, помінявши таблиці місцями.
Для перевірки відсутньої відповідності використовуйте IS NULL.
Фільтр у WHERE може прибрати незбіглі рядки зовнішнього об’єднання.
FULL JOIN зручний для пошуку записів, які існують лише в одній із двох таблиць.