Как реализовать соединение «левый любой», чтобы получить только одну запись из правой таблицы?

← На главную

При работе с базами данных, особенно в системах на базе Oracle или MS SQL Server, разработчики часто сталкиваются с ситуацией, когда стандартное LEFT JOIN (левое внешнее соединение) возвращает избыточное количество строк. Это происходит, если в правой (присоединяемой) таблице одной записи из левой таблицы соответствует несколько записей. В итоге результат «раздувается», что мешает корректному расчету итогов или просто перегружает выборку лишними данными. Понятие «левый любой» подразумевает, что для каждой строки из левой таблицы мы хотим получить ровно одну (первую попавшуюся или наиболее подходящую) строку из правой таблицы.

Рассмотрим подробнее, какими способами можно добиться такого поведения в SQL, и проанализируем ситуацию применительно к платформе 1С.

Метод 1. Использование группировки и агрегатных функций

Это классический и самый универсальный способ, который работает практически в любой реляционной СУБД. Суть его заключается в том, чтобы сначала «схлопнуть» правую таблицу до уникальных ключей связи, выбрав для каждого ключа одно значение агрегатной функцией.

Разберем по шагам логику этого подхода:

Выбираем из правой таблицы ключевое поле связи и поле, которое нам нужно получить (например, максимальный ID или дату).
Группируем результат по ключевому полю.
Соединяем левую таблицу с полученным результатом.

Пример реализации на стандартном SQL:


SELECT 
    t1.ID, 
    t1.Name, 
    t2_grouped.MaxVal
FROM Table1 t1
LEFT JOIN (
    SELECT 
        RefID, 
        MAX(ValueField) as MaxVal
    FROM Table2
    GROUP BY RefID
) t2_grouped ON t1.ID = t2_grouped.RefID;

Этот метод гарантирует, что количество строк в итоговой выборке останется равным количеству строк в Table1, однако он позволяет получить только агрегированные значения (минимум, максимум, сумма). Если нам нужно получить сразу несколько полей из «той самой» одной строки, этот метод потребует дополнительных вложенных запросов.

Метод 2. Применение оператора OUTER APPLY (Oracle 12c+ и MS SQL)

Оператор OUTER APPLY (и его аналог CROSS APPLY) — это мощный инструмент, который позволяет выполнить подзапрос для каждой строки основной таблицы. В отличие от обычного JOIN, здесь мы можем ограничить количество возвращаемых строк внутри самого подзапроса.

Рассмотрим пример для Oracle, где мы выбираем только одну запись из правой таблицы:


SELECT 
    t1.ID, 
    t2.SomeField, 
    t2.AnotherField
FROM Table1 t1
OUTER APPLY (
    SELECT * 
    FROM Table2 t2_sub 
    WHERE t2_sub.RefID = t1.ID
    ORDER BY t2_sub.CreateDate DESC
    FETCH FIRST 1 ROW ONLY
) t2;

Проанализируем ситуацию: использование FETCH FIRST 1 ROW ONLY внутри OUTER APPLY гарантирует, что база данных прекратит поиск в Table2 сразу после нахождения первой (в данном случае — самой свежей по дате) записи. Это не только решает проблему дублей, но и зачастую работает быстрее традиционных соединений.

Метод 3. Оконные функции и ROW_NUMBER()

Если ваша версия СУБД поддерживает оконные функции, то использование ROW_NUMBER() является одним из самых гибких решений. Мы можем пронумеровать все строки в правой таблице в разрезе групп и оставить только строки с номером 1.

Выясним причину популярности этого метода: он позволяет гибко управлять тем, какую именно запись считать «первой». Если порядок не важен (нужна действительно «любая»), можно использовать ORDER BY (SELECT NULL), что сэкономит ресурсы на сортировке.

Пример кода:


SELECT *
FROM Table1 t1
LEFT JOIN (
    SELECT 
        RefID, 
        ValueField,
        ROW_NUMBER() OVER (PARTITION BY RefID ORDER BY ValueField DESC) as rn
    FROM Table2
) t2 ON t1.ID = t2.RefID AND t2.rn = 1;

Здесь PARTITION BY RefID разбивает правую таблицу на группы по ключу связи, а rn = 1 в условии соединения отсекает все лишние записи, кроме одной для каждого ключа.

Метод 4. Специфический синтаксис Oracle: KEEP (DENSE_RANK)

Для разработчиков на Oracle существует уникальная возможность получить значение из первой или последней строки группы без использования подзапросов в JOIN. Это делается с помощью конструкции KEEP (DENSE_RANK FIRST/LAST ORDER BY ...).

Посмотрим на пример:


SELECT 
    t1.ID,
    MAX(t2.Value) KEEP (DENSE_RANK FIRST ORDER BY t2.Priority DESC) as TopValue
FROM Table1 t1
LEFT JOIN Table2 t2 ON t1.ID = t2.RefID
GROUP BY t1.ID;

Важный момент: эта конструкция работает внутри агрегации. Она говорит серверу: «Среди всех строк, попавших в группу, отсортируй их по приоритету и возьми значение поля Value из самой первой строки».

Метод 5. Особенности реализации в 1С:Предприятие

Поскольку вопрос часто возникает у разработчиков 1С, важно понимать, как платформа транслирует запросы. В языке запросов 1С нет прямого аналога OUTER APPLY или ROW_NUMBER(). При выполнении ЛЕВОЕ СОЕДИНЕНИЕ платформа всегда генерирует стандартный SQL-запрос, который может привести к дублированию записей.

Чтобы реализовать логику «левый любой» в 1С, мы рекомендуем следующий алгоритм:

Создайте временную таблицу, в которую выберите данные из правой таблицы.
В этой временной таблице используйте СГРУППИРОВАТЬ ПО ключевому полю и выберите МАКСИМУМ (или МИНИМУМ) по тем полям, которые вам нужны.
В основном запросе соедините вашу главную таблицу с этой подготовленной временной таблицей.

Пример на языке запросов 1С:


// Сначала подготавливаем уникальные записи
ВЫБРАТЬ
    Продажи.Контрагент КАК Контрагент,
    МАКСИМУМ(Продажи.Период) КАК ДатаПоследнейПродажи
ПОМЕСТИТЬ ВТ_ПоследниеПродажи
ИЗ
    РегистрНакопления.Продажи КАК Продажи
СГРУППИРОВАТЬ ПО
    Продажи.Контрагент
;

// Затем соединяем
ВЫБРАТЬ
    Контрагенты.Ссылка,
    ВТ_ПоследниеПродажи.ДатаПоследнейПродажи
ИЗ
    Справочник.Контрагенты КАК Контрагенты
ЛЕВОЕ СОЕДИНЕНИЕ ВТ_ПоследниеПродажи КАК ВТ_ПоследниеПродажи
ПО Контрагенты.Ссылка = ВТ_ПоследниеПродажи.Контрагент

Подведение итогов

Мы проанализировали ситуацию с соединением «левый любой» и выяснили, что универсального ключевого слова для этого в SQL не существует, но есть эффективные обходные пути. Для высоконагруженных систем на Oracle лучшим выбором будет OUTER APPLY или оконные функции с оптимизированной сортировкой. В среде 1С наиболее надежным и понятным методом остается предварительная подготовка данных во временных таблицах с использованием группировки. Это позволяет избежать «размножения» строк и обеспечивает предсказуемую производительность системы.