Внешние JOIN для работы с отсутствующими данными и NULL-значениями
Внешние JOIN позволяют сохранять строки из одной или обеих таблиц, даже если для них нет совпадений в другой таблице. Отсутствие совпадения обозначается NULL-значениями.
В отличие от INNER JOIN, который возвращает только совпадающие строки, внешние соединения (OUTER JOIN) сохраняют информацию из одной или обеих таблиц:
LEFT JOIN — все строки из левой таблицы + совпадения из правойRIGHT JOIN — все строки из правой таблицы + совпадения из левойFULL JOIN — все строки из обеих таблицCREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id),
product VARCHAR(100),
amount DECIMAL(10, 2)
);
INSERT INTO users (name) VALUES
('Alice'), -- id = 1
('Bob'), -- id = 2
('Charlie'), -- id = 3 — нет заказов
('Diana'); -- id = 4 — нет заказов
INSERT INTO orders (user_id, product, amount) VALUES
(1, 'Laptop', 1200.00), -- Alice
(1, 'Mouse', 50.00), -- Alice
(2, 'Keyboard', 150.00), -- Bob
(5, 'Monitor', 300.00); -- несуществующий пользователь!Данные:
users: orders:
+----+---------+ +----+---------+----------+--------+
| id | name | | id | user_id | product | amount |
+----+---------+ +----+---------+----------+--------+
| 1 | Alice | | 1 | 1 | Laptop | 1200.00|
| 2 | Bob | | 2 | 1 | Mouse | 50.00 |
| 3 | Charlie | | 3 | 2 | Keyboard | 150.00 |
| 4 | Diana | | 4 | 5 | Monitor | 300.00 |
+----+---------+ +----+---------+----------+--------+
LEFT JOIN возвращает все строки из левой таблицы, даже если нет совпадений в правой. Для строк без совпадений колонки правой таблицы заполняются NULL.
SELECT <колонки>
FROM левая_таблица
LEFT JOIN правая_таблица ON условие;SELECT u.name, o.product, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;Результат:
+---------+----------+--------+
| name | product | amount |
+---------+----------+--------+
| Alice | Laptop | 1200.00|
| Alice | Mouse | 50.00 |
| Bob | Keyboard | 150.00 |
| Charlie | NULL | NULL |
| Diana | NULL | NULL |
+---------+----------+--------+
Что произошло:
NULLuser_id = 5 не попал — нет пользователя с таким idЧастый паттерн — найти строки из левой таблицы, для которых нет совпадений в правой:
-- Пользователи без заказов
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;Результат:
+---------+
| name |
+---------+
| Charlie |
| Diana |
+---------+
WHERE o.id IS NULL фильтрует только те строки, где совпадения не было найдено.
RIGHT JOIN — это симметричная версия LEFT JOIN. Возвращает все строки из правой таблицы, даже если нет совпадений в левой.
SELECT <колонки>
FROM левая_таблица
RIGHT JOIN правая_таблица ON условие;SELECT u.name, o.product, o.amount
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;Результат:
+-------+----------+--------+
| name | product | amount |
+-------+----------+--------+
| Alice | Laptop | 1200.00|
| Alice | Mouse | 50.00 |
| Bob | Keyboard | 150.00 |
| NULL | Monitor | 300.00 |
+-------+----------+--------+
Что произошло:
user_id = 5 нет пользователя — name = NULLRIGHT JOIN можно заменить на LEFT JOIN, поменяв таблицы местами:
-- Эти два запроса эквивалентны:
FROM users u RIGHT JOIN orders o ON u.id = o.user_id
FROM orders o LEFT JOIN users u ON o.user_id = u.idРекомендация: Используйте
LEFT JOINвместоRIGHT JOINдля единообразия кода.RIGHT JOINможет запутать, так как читается слева направо, но сохраняет правую таблицу.
FULL JOIN (или FULL OUTER JOIN) возвращает все строки из обеих таблиц. Если совпадения нет — NULL с соответствующей стороны.
SELECT <колонки>
FROM таблица1
FULL JOIN таблица2 ON условие;SELECT u.name, o.product, o.amount
FROM users u
FULL JOIN orders o ON u.id = o.user_id;Результат:
+---------+----------+--------+
| name | product | amount |
+---------+----------+--------+
| Alice | Laptop | 1200.00|
| Alice | Mouse | 50.00 |
| Bob | Keyboard | 150.00 |
| Charlie | NULL | NULL |
| Diana | NULL | NULL |
| NULL | Monitor | 300.00 |
+---------+----------+--------+
Что произошло:
NULL заполняет отсутствующие данныеПолезный паттерн — найти все несоответствия между таблицами:
-- Все строки, где нет совпадения в одной из таблиц
SELECT u.name, o.product
FROM users u
FULL JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL OR o.id IS NULL;Результат:
+---------+----------+
| name | product |
+---------+----------+
| Charlie | NULL |
| Diana | NULL |
| NULL | Monitor |
+---------+----------+
Это показывает:
Наглядное сравнение на наших данных:
| Тип JOIN | Количество строк | Описание |
|---|---|---|
INNER JOIN | 3 | Только Alice и Bob с заказами |
LEFT JOIN | 5 | Все пользователи + их заказы |
RIGHT JOIN | 4 | Все заказы + имена пользователей |
FULL JOIN | 6 | Все пользователи и все заказы |
Для фильтрации строк без совпадений используйте IS NULL:
-- Правильно:
WHERE o.id IS NULL
-- НЕПРАВИЛЬНО (никогда не вернёт строки):
WHERE o.id = NULLФункция COALESCE заменяет NULL на значение по умолчанию:
SELECT
u.name,
COALESCE(o.product, 'Нет заказов') AS product,
COALESCE(o.amount, 0) AS amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;Результат:
+---------+---------------+--------+
| name | product | amount |
+---------+---------------+--------+
| Alice | Laptop | 1200.00|
| Alice | Mouse | 50.00 |
| Bob | Keyboard | 150.00 |
| Charlie | Нет заказов | 0 |
| Diana | Нет заказов | 0 |
+---------+---------------+--------+
Для внешних соединений критично важно, где размещено условие:
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.amount > 100;Результат:
+---------+----------+
| name | product |
+---------+----------+
| Alice | Laptop |
| Alice | NULL | -- Mouse отфильтрован, но Alice сохранена
| Bob | Keyboard |
| Charlie | NULL |
| Diana | NULL |
+---------+----------+
SELECT u.name, o.product
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.amount > 100;Результат:
+-------+----------+
| name | product |
+-------+----------+
| Alice | Laptop |
| Bob | Keyboard |
+-------+----------+
WHERE отфильтровал строки с NULL (Charlie, Diana, Mouse), превратив LEFT JOIN в эффективный INNER JOIN.
Правило: Для внешних соединений помещайте условия фильтрации правой таблицы в
ON, если хотите сохранить строки левой таблицы.
Можно комбинировать несколько внешних JOIN:
SELECT
u.name,
o.product,
p.category
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
LEFT JOIN products p ON o.product_id = p.id;Или смешивать типы JOIN:
SELECT u.name, o.product, c.name AS category_name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
INNER JOIN categories c ON o.category_id = c.id;Индексы на внешних ключах важны так же, как для INNER JOIN:
CREATE INDEX idx_orders_user_id ON orders(user_id);LEFT JOIN обычно эффективнее RIGHT JOIN — оптимизатор лучше обрабатывает левую таблицу.
Избегайте FULL JOIN на больших таблицах — может быть медленным из-за необходимости обработки всех строк.
-- Пользователи без заказов
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;-- Количество заказов на пользователя (включая 0)
SELECT
u.name,
COUNT(o.id) AS order_count -- COUNT(o.id), не COUNT(*)!
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;Результат:
+---------+-------------+
| name | order_count |
+---------+-------------+
| Alice | 2 |
| Bob | 1 |
| Charlie | 0 | -- COUNT(o.id) = 0, так как o.id = NULL
| Diana | 0 |
+---------+-------------+
Важно: Используйте
COUNT(o.id)илиCOUNT(o.*), а неCOUNT(*), чтобы правильно подсчитать нулевые значения.
-- Пометить пользователей без заказов
UPDATE users
SET status = 'inactive'
WHERE id IN (
SELECT u.id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL
);LEFT JOIN сохраняет все строки из левой таблицы, заполняя NULL отсутствующие совпаденияRIGHT JOIN сохраняет все строки из правой таблицы (редко используется)FULL JOIN сохраняет все строки из обеих таблицLEFT JOIN ... WHERE ... IS NULLON, а не в WHERECOALESCE для замены NULL на значения по умолчаниюLEFT JOIN используйте COUNT(колонка_правой_таблицы), а не COUNT(*)Вопросы ещё не добавлены
Вопросы для этой подтемы ещё не добавлены.