Как исключить пересечения в LEFT JOIN

Как исключить пересечения в LEFT JOIN

Пересечения (дубли строк) при LEFT JOIN возникают, когда таблица справа имеет несколько совпадающих записей на одну строку слева. Это частая проблема при работе с внешними ключами, когда есть отношения один-ко-многим.

Проблема

-- Таблица users
id | name
1  | Alice
2  | Bob

-- Таблица orders
id | user_id | amount
1  | 1       | 100
2  | 1       | 200
3  | 2       | 150

-- Результат LEFT JOIN - ДУБЛИ
SELECT users.id, users.name, orders.amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Результат (пересечения!):
id | name  | amount
1  | Alice | 100
1  | Alice | 200  -- Дубль Alice
2  | Bob   | 150

Решение 1: GROUP BY с DISTINCT

Самый простой способ — убрать дубли через GROUP BY:

SELECT DISTINCT users.id, users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Или с GROUP BY:
SELECT users.id, users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- Результат:
id | name
1  | Alice
2  | Bob

Решение 2: Агрегирование данных

Если нужны данные из таблицы справа, используй агрегирующие функции:

SELECT 
    users.id,
    users.name,
    COUNT(orders.id) as order_count,
    SUM(orders.amount) as total_amount,
    MAX(orders.amount) as max_amount
FROM users
LEFT JOIN orders ON users.id = orders.user_id
GROUP BY users.id, users.name;

-- Результат:
id | name  | order_count | total_amount | max_amount
1  | Alice | 2           | 300          | 200
2  | Bob   | 1           | 150          | 150

Решение 3: Подзапрос (Subquery)

Для более сложных случаев используй подзапрос с агрегацией:

SELECT 
    users.id,
    users.name,
    o.recent_amount
FROM users
LEFT JOIN (
    SELECT user_id, MAX(amount) as recent_amount
    FROM orders
    GROUP BY user_id
) o ON users.id = o.user_id;

-- Результат:
id | name  | recent_amount
1  | Alice | 200
2  | Bob   | 150

Решение 4: CROSS JOIN с LIMITED

Если нужны только определённые строки из правой таблицы:

SELECT 
    users.id,
    users.name,
    o.amount
FROM users
LEFT JOIN LATERAL (
    SELECT amount
    FROM orders
    WHERE user_id = users.id
    ORDER BY id DESC
    LIMIT 1
) o ON true;

-- Результат (только последний заказ):
id | name  | amount
1  | Alice | 200
2  | Bob   | 150

Решение 5: ROW_NUMBER() для дубликатов

Если нужны некоторые данные из правой таблицы, но по одной записи на пользователя:

WITH ranked_orders AS (
    SELECT 
        user_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) as rn
    FROM orders
)
SELECT 
    users.id,
    users.name,
    ro.amount
FROM users
LEFT JOIN ranked_orders ro ON users.id = ro.user_id AND ro.rn = 1;

-- Результат:
id | name  | amount
1  | Alice | 200
2  | Bob   | 150

Решение 6: UNION для исключения конкретных критериев

Если пересечения от специфических условий:

SELECT users.id, users.name
FROM users
LEFT JOIN orders ON users.id = orders.user_id AND orders.amount > 100
WHERE orders.id IS NULL OR orders.amount > 100
GROUP BY users.id, users.name;

Best Practices

1. DISTINCT для простых случаев — если нужны только поля из левой таблицы:

SELECT DISTINCT u.id, u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

1. GROUP BY с агрегацией — если нужны числовые данные:

SELECT u.id, u.name, COUNT(*) as cnt
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

1. Подзапросы — для сложных условий на правую таблицу:

SELECT u.*, o.latest_order
FROM users u
LEFT JOIN (
    SELECT user_id, MAX(created_at) as latest_order
    FROM orders
    GROUP BY user_id
) o ON u.id = o.user_id;

1. ROW_NUMBER() — для выбора конкретной строки по критерию:

WITH latest_orders AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY id DESC) rn
    FROM orders
)
SELECT u.*, lo.*
FROM users u
LEFT JOIN latest_orders lo ON u.id = lo.user_id AND lo.rn = 1;

Тестирование и отладка

Проверь дубли через COUNT:

SELECT COUNT(*) as total_rows,
       COUNT(DISTINCT users.id) as unique_users
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- Если total_rows != unique_users, есть пересечения

Итоги

• DISTINCT — быстро убирает дубли из полей левой таблицы
• GROUP BY — контролирует агрегацию и выбирает специфические строки
• Подзапросы — лучший выбор для сложной логики фильтрации
• ROW_NUMBER() — элегантный способ выбрать N-ую запись по группе
• Всегда проверяй результаты на COUNT для валидации