Язык запросов 1С 8.3 для начинающих программистов: соединения

Автор уроков и преподаватель школы: Владимир Милькин

Соединения в запросах

Соединение - одна из самых важных и нужных операций, выполняемых реляционными системами управления базами данных.

Соединения используются для того, чтобы сопоставить строки одной таблицы строкам другой таблицы.

Для того, чтобы осознать необходимость соединений давайте решим следующую задачу.

У нас в базе есть справочник Клиенты:

Справочник Цвета:

И справочник Ассоциации:

Наша задача вывести любимые ассоциации клиентов, основываясь на цвете.

Таким образом для Наташи любимой ассоциацией будет трава, так как её любимый цвет зелёный. А для Петра - солнце. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь.

Для Андрея вообще нет подходящей ассоциации, так его любимый цвет красный, а ассоциаций красного цвета в базе нет.

Будем решать задачу постепенно.

Сначала запросим всех клиентов и их любимые цвета:

ВЫБРАТЬ
    Наименование,
    ЛюбимыйЦвет
ИЗ
    Справочник.Клиенты

Затем запросим все ассоциации и их цвета:

ВЫБРАТЬ
    Наименование,
    Цвет
ИЗ
    Справочник.Ассоциации

Теперь нам каким-то образом следует совместить первую и вторую таблицу. Чтобы это сделать запросим информацию сразу из двух таблиц. Для этого перечислим обе таблицы в секции ИЗ через запятую. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь. А в секции ВЫБРАТЬ укажем поля из обеих таблиц:

ВЫБРАТЬ
    Наименование,
    ЛюбимыйЦвет,
    Наименование,
    Цвет
ИЗ
    Справочник.Клиенты,
    Справочник.Ассоциации

Если мы попробуем выполнить этот запрос, то получим ошибку:

Причина ошибки в том, что поле Наименование присутствует сразу в обеих таблицах (Клиенты и Ассоциации) и система просто не знает поле из какой именно таблицы имеется в виду.

Чтобы устранять подобные неоднозначности при выборке из более чем одной таблицы принято указывать полные названия полей.  Полное название поля включает в себя полное имя таблицы (например, Справочник.Клиенты) и имя самого поля (например, Наименование).

Таким образом полное название поля Наименование из таблицы Клиенты будет Справочник.Клиенты.Наименование.

А полное названия поля Наименование из таблицы Ассоциации будет Справочник.Ассоциации.Наименование.

Перекрёстное соединение

Перепишем предыдущий запрос с полными именами полей:

ВЫБРАТЬ
    Справочник.Клиенты.Наименование,
    Справочник.Клиенты.ЛюбимыйЦвет,
    Справочник.Ассоциации.Наименование,
    Справочник.Ассоциации.Цвет
ИЗ
    Справочник.Клиенты,
    Справочник.Ассоциации

Только что мы произвели перекрёстное соединение двух таблиц. Обратите внимание на то, каким образом сформировался результат:

Внутреннее соединение

Очевидно, что результат перекрестного соединения двух таблиц не есть решение нашей задачи. Нам нужны не все записи из перекрёстного соединения, а только те у которых поля ЛюбимыйЦвет и Цвет имеют одинаковое значение:

Чтобы получить эти записи добавим к предыдущему запросу секцию ГДЕ:

ВЫБРАТЬ
    Справочник.Клиенты.Наименование,
    Справочник.Клиенты.ЛюбимыйЦвет,
    Справочник.Ассоциации.Наименование,
    Справочник.Ассоциации.Цвет
ИЗ
    Справочник.Клиенты,
    Справочник.Ассоциации
ГДЕ
    Справочник.Клиенты.ЛюбимыйЦвет = Справочник.Ассоциации.Цвет

Это то, что нужно - мы решили, поставленную задачу!

В последнем запросе мы использовали перекрёстное соединение с дополнительным условием (в секции ГДЕ). Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь. Такое соединение называется внутренним.

Есть ещё один вариант написания того же самого внутреннего соединения :

ВЫБРАТЬ
    Справочник.Клиенты.Наименование,
    Справочник.Клиенты.ЛюбимыйЦвет,
    Справочник.Ассоциации.Наименование,
    Справочник.Ассоциации.Цвет
ИЗ
    Справочник.Клиенты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
    Справочник.Ассоциации
ПО
    Справочник.Клиенты.ЛюбимыйЦвет = Справочник.Ассоциации.Цвет

Сравните этот и предыдущий запрос. Они совершенно одинаковы с точки зрения платформы, просто имеют разный синтаксис. И этот и предыдущий запросы содержат внутреннее соединение таблицы Клиенты с таблицей Ассоциации по полям ЛюбимыйЦвет и Цвет соответственно.

Левое внешнее соединение

Обратите внимание на то, что в результат внутреннего соединения не попал Андрей. А всё потому, что его любимый цвет красный, а красных ассоциаций у нас в базе нет вовсе.

Получается, что для Андрея с его красным цветом просто не нашлось пары из таблицы ассоциаций.

Перепишем запрос так, чтобы в результат попадали в том числе те записи из первой таблицы, для которых не нашлось ни одной пары из второй таблицы (в данном случае Андрей):

ВЫБРАТЬ
    Справочник.Клиенты.Наименование,
    Справочник.Клиенты.ЛюбимыйЦвет,
    Справочник.Ассоциации.Наименование,
    Справочник.Ассоциации.Цвет
ИЗ
    Справочник.Клиенты
ЛЕВОЕ СОЕДИНЕНИЕ
    Справочник.Ассоциации
ПО
    Справочник.Клиенты.ЛюбимыйЦвет = Справочник.Ассоциации.Цвет

Такое соединение называется левым внешним соединением (слово внешнее можно опускать для простоты).

Результат левого внешнего соединения представляет из себя: все записи из внутреннего соединения ПЛЮС все записи из первой таблицы, не попавшие во внутреннее соединение (для которых не нашлось пары).

Правое внешнее соединение

Но давайте снова вернёмся к внутреннему соединению:

Обратите внимание на то, что результат внутреннего соединения не содержит ассоциацию белый снег, так как не нашлось ни одного клиента, у которого любимым цветом был бы белый.

Перепишем запрос так, чтобы в результат попадали в том числе те записи из второй таблицы, для которых не нашлась ни одной пары из первой таблицы (в данном случае белый снег):

ВЫБРАТЬ
    Справочник.Клиенты.Наименование,
    Справочник.Клиенты.ЛюбимыйЦвет,
    Справочник.Ассоциации.Наименование,
    Справочник.Ассоциации.Цвет
ИЗ
    Справочник.Клиенты
ПРАВОЕ СОЕДИНЕНИЕ
    Справочник.Ассоциации
ПО
    Справочник.Клиенты.ЛюбимыйЦвет = Справочник.Ассоциации.Цвет

Такое соединение называется правым внешним соединением (слово внешнее можно опускать для простоты).

Результат правого внешнего соединения представляет из себя: все записи из внутреннего соединения ПЛЮС все записи из второй таблицы, не попавшие во внутреннее соединение (для которых не нашлось пары).

Полное соединение

А что если нам нужно, чтобы в результат запроса попадали помимо внутреннего соединения Андрей и Снег одновременно?

Для этого потребуется совместить результаты левого и правого соединений. Такой вид соединения уже придуман и называется полным соединением:

ВЫБРАТЬ
    Справочник.Клиенты.Наименование,
    Справочник.Клиенты.ЛюбимыйЦвет,
    Справочник.Ассоциации.Наименование,
    Справочник.Ассоциации.Цвет
ИЗ
    Справочник.Клиенты
ПОЛНОЕ СОЕДИНЕНИЕ
    Справочник.Ассоциации
ПО
    Справочник.Клиенты.ЛюбимыйЦвет = Справочник.Ассоциации.Цвет

Результат полного соединения представляет из себя: все записи из внутреннего соединения ПЛЮС все записи из первой таблицы, не попавшие во внутреннее соединение (для которых не нашлось пары) ПЛЮС все записи из второй таблицы, не попавшие во внутреннее соединение (для которых не нашлось пары).

Псевдонимы таблиц

Согласитесь, что все запросы, которые мы писали в этом уроке выглядят довольно громоздко. Это связано с тем, что мы вынуждены указывать полные имена полей, чтобы избежать возникновение неоднозначности.

Чтобы сократить полное написание имени таблицы (например, Справочник.Клиенты) допустимо (как и для самих полей)  использовать псевдонимы.

Давайте перепишем последний запрос так, чтобы при формировании полных имён полей вместо Справочник.Клиенты можно было использовать псевдоним К, а вместо Справочник.Ассоциации - псевдоним А:

ВЫБРАТЬ
    К.Наименование,
    К.ЛюбимыйЦвет,
    А.Наименование,
    А.Цвет
ИЗ
    Справочник.Клиенты КАК К
ПОЛНОЕ СОЕДИНЕНИЕ
    Справочник.Ассоциации КАК А
ПО
    К.ЛюбимыйЦвет = А.Цвет

А чтобы результат запроса был ещё нагляднее добавим псевдонимы полей, которые мы уже рассматривали на одном из прошлых уроков:

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

Обработка NULL

Присмотритесь к результатам последнего запроса (как впрочем и многих предыдущих на этом уроке).

Чему равны значения полей Ассоциация и ЕёЦвет для первой строчки? А что вы скажете насчет полей Клиент и ЕгоЦвет для последней строки?

Они равны NULL, которое как мы уже знаем означает отсутствие какого либо значения:

А так как NULL означает отсутствие значения, то любая попытка выполнить с ним какую-либо операцию (сравнение, сложение ...) вызовет неопределенное поведение базы данных, непредсказуемую ошибку.

Поэтому обязательной считается обработка значений NULL всегда, когда они могут возникнуть.

Под обработкой подразумевается то, что мы должны сказать в нашем запросе, что если одно из полей будет равно NULL, то в это поле следует подставить какое-то другое значение.

В данном случае для полей Клиент и Ассоциация в случае обнаружения NULL мы будем подставлять пустую строку "".

А вот поля ЕгоЦвет и ЕёЦвет являются ссылками на элементы справочника Цвета, поэтому в них можно подставлять только значения являющиеся ссылками указанных типов. Каждый ссылочный тип (например, Справочник или Документ) имеет предопределенный элемент ПустаяСсылка. Чтобы указать его значение в запросе воспользуемся функцией ЗНАЧЕНИЕ.

Для определения того, что в поле попало NULL будем использовать уже знакомую нам по прошлым урокам функцию ЕСТЬNULL:

ВЫБРАТЬ
    ЕСТЬNULL(
        К.Наименование, ""
    ) КАК Клиент,
 
    ЕСТЬNULL(
        К.ЛюбимыйЦвет, ЗНАЧЕНИЕ(Справочник.Цвета.ПустаяСсылка)
    ) КАК ЕгоЦвет,
 
    ЕСТЬNULL(
        А.Наименование, ""
    ) КАК Ассоциация,
 
    ЕСТЬNULL(
        А.Цвет, ЗНАЧЕНИЕ(Справочник.Цвета.ПустаяСсылка)
    ) КАК ЕёЦвет
ИЗ
    Справочник.Клиенты КАК К
ПОЛНОЕ СОЕДИНЕНИЕ
    Справочник.Ассоциации КАК А
ПО
    К.ЛюбимыйЦвет = А.Цвет

С виду (из консоли запросов) результат не изменился. Мы по-прежнему видим пустые поля. Но это только потому, что строковые представления у NULL и у пустых полей всех типов совпадают и равны пустой строке.

На самом же деле эти пустые поля уже не есть NULL (отсутствие значения), теперь в них появились значения (пустые), с которыми уже можно работать (совершать операции).

Запомните пустое значение и отсутствие значение - это две большие разницы.

Соединение более двух таблиц

Можно последовательно соединять сколько угодно таблиц.

Предположим нужно решить следующую задачу. Вы читаете ознакомительную версию урока, полноценные уроки находятся здесь. Вывести все возможные варианты клиентов и их любимых ассоциаций и их любимых продуктов исходя из их любимого цвета.

Для этого последовательно соединим по цвету таблицу Клиенты с таблицей Ассоциации, а затем (получившийся результат) с таблицей Еда:

ВЫБРАТЬ
    К.Наименование,
    К.ЛюбимыйЦвет,
    А.Наименование,
    А.Цвет,
    Е.Наименование,
    Е.Цвет
ИЗ
    Справочник.Клиенты КАК К
 
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
    Справочник.Ассоциации КАК А
ПО
    К.ЛюбимыйЦвет = А.Цвет
 
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
    Справочник.Еда КАК Е
ПО
    К.ЛюбимыйЦвет = Е.Цвет

Пройдите тест