Соединение таблиц в запросах
Автор: Дмитрий Котельников
Когда мы хотим видеть данные из нескольких таблиц одновременно, т.е. собрать несколько таблиц в одну возникает понятие соединения таблиц и связей между ними. Соединения бывают четырех типов:
левое;
правое,
внутреннее;
полное.
Каждый тип мы рассмотрим на абстрактном примере. Имеется 2 таблицы, в первой храним описательную информацию о номенклатуре, во второй о ее остатках:
Для того, чтобы получить из этих таблиц одну нам необходимо явным образом указать какие поля мы будем связывать, по какому условию и типу. Сейчас станет более понятно.
Левое соединение
Используя левое соединение мы говорим системе, что в результате хотим видеть все записи из левой таблицы и записи из правой удовлетворяющие условию связи. Допустим мы связываем таблицы по полю товар с условием равно, тогда получим таблицу вида:
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет КАК ЦветНоменклатура,
| Остатки.Цвет КАК ЦветОстатки,
| Остатки.Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар";
Для стула не нашлось сопоставлений из таблицы остатков, поэтому поля заполнились значениями NULL, которые обязательно нужно обработать функцией ЕСТЬNULL, см. Функции языка запросов 1С 8.
Левое соединение работает примерно как цикл в цикле - берется первая запись из левой таблицы и пробегаются все записи из правой на предмет удовлетворения условию связи. Затем берется вторая запись из левой таблицы и т.д. Если вдруг условию связи удовлетворяют несколько записей из правой таблицы, то в результирующую таблицу будет добавлено несколько строк (по количеству удачных связей).Как видим, полученная таблица не информативна, данные не отражают реальную суть, поэтому лучше связать эти таблицы по двум полям: Товар и Цвет, только на этот раз обработаем NULLы:
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет,
| ЕСТЬNULL(Остатки.Количество, 0) КАК Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ЛЕВОЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар
| И Номенклатура.Цвет = Остатки.Цвет";
Правое соединение
Правое соединение по сути ничем не отличается от левого. Если поменять таблицы местами, то правое соединение превратится в левое, более того, при использовании конструктора система сама преобразует все правые соединения в левые.
Внутреннее соединение
Используя внутреннее соединение мы говорим системе, что в результате хотим видеть только те записи, которые удовлетворяют условию связи как из правой таблицы, так и из левой. Таким образом, количество результирующих записей будет меньше или равно количеству записей самой "короткой таблицы", участвующей в соединении. Применим внутреннее соединение к полям Товар и Цвет наших таблиц:
Запрос.Текст =
"ВЫБРАТЬ
| Номенклатура.Товар,
| Номенклатура.Цвет,
| Остатки.Количество КАК Количество
|ИЗ
| Номенклатура КАК Номенклатура
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ Остатки КАК Остатки
| ПО Номенклатура.Товар = Остатки.Товар
| И Номенклатура.Цвет = Остатки.Цвет";
Полное соединение
Полное соединение передаст в результат все записи из обоих таблиц, те записи, которые удовлетворяет условию связи - будут связаны, те записи, которые не удовлетворяют условию связи все равно окажутся в результате запроса, но с частью NULLовых полей. Полное это как бы левое и правое соединения в одном.
Задач на эту тему может быть много, давайте попробуем решить одну из них. Наша организация является дилером 2 мебельных фабрик: "Заря" и "Рассвет". Ассортимент со стоимостью каждой из фабрик хранится в разных таблицах. Необходимо составить единый прайс лист, причем брать в него продукцию по минимальной цене:
Применим полное соединение с выборкой всех полей, связывать будем по товару:
Запрос.Текст =
"ВЫБРАТЬ
| НоменклатураЗаря.Товар КАК ТоварЗаря,
| НоменклатураЗаря.Цена КАК ЦенаЗаря,
| НоменклатураРассвет.Товар КАК ТоварРассвет,
| НоменклатураРассвет.Цена КАК ЦенаРассвет
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар";
Это не совсем то, что нам нужно, давайте соединим поле товар в одно и обработаем NULLы:
Запрос.Текст =
"ВЫБРАТЬ
//конструкция ЕСТЬNULL рассматривалась в разделе функции языка запросов
| ЕСТЬNULL(НоменклатураЗаря.Товар, НоменклатураРассвет.Товар) КАК Товар,
//если цена не определена, то инициализируем ее
//почему 1000000 см. пояснения ниже
| ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000) КАК ЦенаЗаря,
| ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000) КАК ЦенаРассвет
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар";
Осталось только выбрать минимальную цену. Итоговый тект запроса будет выглядеть следующим образом:
Запрос.Текст =
"ВЫБРАТЬ
| ЕСТЬNULL(НоменклатураЗаря.Товар, НоменклатураРассвет.Товар) КАК Товар,
| ВЫБОР
| КОГДА ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000) > ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000)
| ТОГДА ЕСТЬNULL(НоменклатураРассвет.Цена, 1000000)
| ИНАЧЕ ЕСТЬNULL(НоменклатураЗаря.Цена, 1000000)
| КОНЕЦ КАК Цена
|ИЗ
| НоменклатураЗаря КАК НоменклатураЗаря
| ПОЛНОЕ СОЕДИНЕНИЕ НоменклатураРассвет КАК НоменклатураРассвет
| ПО НоменклатураЗаря.Товар = НоменклатураРассвет.Товар";
Если цена не определена (NULL), то ее необходимо инициализировать каким либо значением, иначе операция сравнения на больше/меньше вывалится с ошибкой. инициализируем цену нереально большой суммой, чтобы она "проиграла" в операции сравнения, ведь по условию задачи мы подбираем наименьшую цену.