Внешние соединения: LEFT, RIGHT, FULL — SQL JOIN:

Внешние соединения: LEFT, RIGHT, FULL JOIN

Внешние 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

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   |
+---------+----------+--------+

Что произошло:

Alice и Bob имеют заказы — показаны их данные
Charlie и Diana не имеют заказов — показаны с NULL
Заказ для user_id = 5 не попал — нет пользователя с таким id

#LEFT JOIN для поиска отсутствующих строк

Частый паттерн — найти строки из левой таблицы, для которых нет совпадений в правой:

-- Пользователи без заказов
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

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 = NULL
Charlie и Diana не попали — у них нет заказов

#RIGHT JOIN vs LEFT JOIN

RIGHT 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 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 |
+---------+----------+--------+

Что произошло:

Все пользователи показаны (включая Charlie и Diana)
Все заказы показаны (включая заказ без пользователя)
NULL заполняет отсутствующие данные

#FULL JOIN для поиска несоответствий

Полезный паттерн — найти все несоответствия между таблицами:

-- Все строки, где нет совпадения в одной из таблиц
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  |
+---------+----------+

Это показывает:

Пользователей без заказов (Charlie, Diana)
Заказы без пользователей (Monitor)

#Сравнение типов JOIN

Наглядное сравнение на наших данных:

Тип JOIN	Количество строк	Описание
`INNER JOIN`	3	Только Alice и Bob с заказами
`LEFT JOIN`	5	Все пользователи + их заказы
`RIGHT JOIN`	4	Все заказы + имена пользователей
`FULL JOIN`	6	Все пользователи и все заказы

#NULL во внешних соединениях

#Проверка на NULL

Для фильтрации строк без совпадений используйте IS NULL:

-- Правильно:
WHERE o.id IS NULL

-- НЕПРАВИЛЬНО (никогда не вернёт строки):
WHERE o.id = NULL

#COALESCE для замены 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      |
+---------+---------------+--------+

#Условия в ON vs WHERE для внешних соединений

Для внешних соединений критично важно, где размещено условие:

#Условие в ON (фильтрует правую таблицу до соединения)

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     |
+---------+----------+

#Условие в WHERE (фильтрует после соединения)

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 на больших таблицах — может быть медленным из-за необходимости обработки всех строк.

#Распространённые паттерны

#1. Найти строки без совпадений (анти-соединение)

-- Пользователи без заказов
SELECT u.*
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

#2. Подсчёт с учётом отсутствующих строк

-- Количество заказов на пользователя (включая 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(*), чтобы правильно подсчитать нулевые значения.

#3. Обновление с использованием LEFT JOIN

-- Пометить пользователей без заказов
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
);

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 | +----+---------+ +----+---------+----------+--------+

+-------+----------+--------+ | name | product | amount | +-------+----------+--------+ | Alice | Laptop | 1200.00| | Alice | Mouse | 50.00 | | Bob | Keyboard | 150.00 | | NULL | Monitor | 300.00 | +-------+----------+--------+

Тип JOIN

Количество строк

Описание

INNER JOIN

Только Alice и Bob с заказами

LEFT JOIN

Все пользователи + их заказы

RIGHT JOIN

Все заказы + имена пользователей

FULL JOIN

Все пользователи и все заказы

+---------+---------------+--------+ | name | product | amount | +---------+---------------+--------+ | Alice | Laptop | 1200.00| | Alice | Mouse | 50.00 | | Bob | Keyboard | 150.00 | | Charlie | Нет заказов | 0 | | Diana | Нет заказов | 0 | +---------+---------------+--------+

+---------+-------------+ | name | order_count | +---------+-------------+ | Alice | 2 | | Bob | 1 | | Charlie | 0 | -- COUNT(o.id) = 0, так как o.id = NULL | Diana | 0 | +---------+-------------+