Соединения в запросе
7 мин
Описание
Переходя к более сложным запросам, вы неизбежно столкнетесь с необходимостью использовать более чем одну таблицу базы данных в одном запросе. Эта возможность языка запросов является одной из самых востребованных в процессе разработки. Использовать несколько источников данных можно с помощью двух основных операторов Соединение и Объединение. Сейчас мы подробнее остановимся на соединениях, а объединения рассмотрим в отдельной статье Объединения.
Секция ИЗ в запросе предоставляет возможность нескольких вариантов использования соединений.
Условимся что у нас есть две вот такие таблицы с данными:
В базе данных хранится таблица Фрукты, в которой мы храним названия фруктов и их количество. И таблица Цвета фруктов, в которой хранится цвет для фруктов, но не для всех.
| Фрукты | Цвета фруктов | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
|
Левое соединение
Один из самых распространенных вариантов соединения Левое соединение. Синтаксис этой конструкции имеет следующий вид:
<Таблица1> Левое Соединение <Таблица2>
ПО <УсловиеСоединения>
И|ИЛИ <Еще одно условие соединения>.
Количество условий соединений не ограничено.
В этом варианте соединения следует запомнить основное правило, к Таблица1 присоединяется Таблица2. Таблица1 при этом остается в исходном виде, а из Таблица2 в результат попадают только те записи, которые соответствуют условию соединения после оператора ПО.
Взяв таблицы Фрукты, ЦветаФруктов и использовав оператор Левое Соединение мы можем добиться следующего результата.
Фрукты.Название,
Фрукты.Количество,
ЦветаФруктов.Цвет
ИЗ
Фрукты КАК Фрукты
ЛЕВОЕ СОЕДИНЕНИЕ ЦветаФруктов КАК ЦветаФруктов
ПО Фрукты.Название = ЦветаФруктов.Фрукт
Результат:
| Название | Количество | Цвет |
|---|---|---|
| Яблоко | 5 | Зеленый |
| Апельсин | 6 | NULL |
| Банан | 12 | Желтый |
Как видим таблица Фрукты осталась в исходном виде, а из таблицы Цвет фруктов в результат попали только значения, которые есть в таблице Фрукты. Значения тех полей, которые не нашли соответствия в присоединяемой таблице, не присоединились, заполняются значением NULL.
Так же можно представить это соединение в графическом виде
Правое соединение
Синтаксис этой конструкции имеет следующий вид:
<Таблица1> Правое Соединение <Таблица2>
ПО <УсловиеСоединения>
И|ИЛИ <Еще одно условие соединения>.
Этот вариант соединения аналогичен левому соединению, с единственным отличием, здесь Таблица1 будет присоединяться к Таблица2.
Пример запроса:
Фрукты.Название,
Фрукты.Количество,
ЦветаФруктов.Цвет
ИЗ
Фрукты КАК Фрукты
ПРАВОЕ СОЕДИНЕНИЕ ЦветаФруктов КАК ЦветаФруктов
ПО Фрукты.Название = ЦветаФруктов.Фрукт
Результат:
| Название | Количество | Цвет |
|---|---|---|
| Яблоко | 5 | Зеленый |
| Банан | 12 | Желтый |
Так как теперь основной таблицей соединения является таблица Цвета фруктов, то и результат видим соответствующий, в таблице цветов у нас всего две строки, и она осталась неизменной, и к ней присоединилась таблица Фрукты только теми записями, которые соответствуют условию.
Так же это соединение можно представить в графическом виде
Следует помнить, что при написании запросов в конструкторе запросов 1С, он автоматически заменяет правое соединение на левое, меняя таблицы местами. Это не влияет на результат, и никак не мешает, следует это просто помнить, чтобы не удивляться почему мы писали правое, а потом оно стало левым)
Внутреннее соединение
Внутреннее соединение имеет похожий синтаксис:
<Таблица1> Внутреннее Соединение <Таблица2>
ПО <УсловиеСоединения>
И|ИЛИ <Еще одно условие соединения>,
но в результате соединения в результат попадают только записи соотвествующие условию в обеих таблицах.
Добавим в таблицу цветов фруктов еще одну запись, чтобы увидеть результат более наглядно
| Фрукт | Цвет |
|---|---|
| Яблоко | Зеленый |
| Банан | Желтый |
| Кокос | Коричневый |
Фрукты.Название,
Фрукты.Количество,
ЦветаФруктов.Цвет
ИЗ
Фрукты КАК Фрукты
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ЦветаФруктов КАК ЦветаФруктов
ПО Фрукты.Название = ЦветаФруктов.Фрукт
Результат:
| Название | Количество | Цвет |
|---|---|---|
| Яблоко | 5 | Зеленый |
| Банан | 12 | Желтый |
Как видим результат явно показывает, что остались только те записи из обеих таблиц, которые соответствуют условию.
Графически это можно изобразить так:
Полное соединение
Синтаксис полного соединения:
<Таблица1> Полное Соединение <Таблица2>
ПО <УсловиеСоединения>
И|ИЛИ <Еще одно условие соединения>.
Это соединение позволяет нам показать все записи из обеих таблиц, но соединить те, которые соответствуют условию.
Рассмотрим пример запроса:
Фрукты.Название,
Фрукты.Количество,
ЦветаФруктов.Цвет
ИЗ
Фрукты КАК Фрукты
ПОЛНОЕ СОЕДИНЕНИЕ ЦветаФруктов КАК ЦветаФруктов
ПО Фрукты.Название = ЦветаФруктов.Фрукт
Результат:
| Название | Количество | Цвет |
|---|---|---|
| Яблоко | 5 | Зеленый |
| Апельсин | 6 | NULL |
| Банан | 12 | Желтый |
| NULL | NULL | Коричневый |
Как видим, результат содержит данные из обеих таблиц, и так же те записи которые соответствуют условию успешно соединились. Те значения, которые не были получены из соединения заполнены значением NULL
Графически это можно изобразить вот так
Перекрестное соединение (Декартово произведение)
В самом простом, и редко используемом варианте соединения мы можем перечислить необходимые таблицы через запятую. Рассмотрим пример.
Запрос может выглядеть следующим образом:
Фрукты.Название,
Фрукты.Количество,
ЦветаФруктов.Цвет
ИЗ
Фрукты КАК Фрукты,
ЦветаФруктов КАК ЦветаФруктов
Результат будет следующий:
| Название | Количество | Цвет |
|---|---|---|
| Яблоко | 5 | Зеленый |
| Яблоко | 5 | Желтый |
| Апельсин | 6 | Зеленый |
| Апельсин | 6 | Желтый |
| Банан | 12 | Зеленый |
| Банан | 12 | Желтый |
Как видно, мы получили данные из двух таблиц в одном запросе, но соединились они довольно странно. Каждая запись из одной таблицы соединилась с каждой записью из другой таблицы, результат получился не корректный. Математика такого результата крайне проста, если знать как перемножаются матрицы. В самом простом объяснении можно сказать, что если в одной таблице 3 записи и в другой таблице 2 записи, то количество строк в результате будет 3*2 = 6. Пока что ценности в этом немного. Обычно такие соединения используют одновременно с отбором в секции ГДЕ. Мы видим, что в обеих таблицах есть поля с одинаковыми значениями Название и Фрукт. Если мы добавим условие на равенство этих полей, то запрос получится следующим:
Фрукты.Название,
Фрукты.Количество,
ЦветаФруктов.Цвет
ИЗ
Фрукты КАК Фрукты,
ЦветаФруктов КАК ЦветаФруктов
ГДЕ
Фрукты.Название = ЦветаФруктов.Фрукт
А результат удовлетворит нашим ожиданиям, цвета фруктов соответствуют фруктам:
| Название | Количество | Цвет |
|---|---|---|
| Яблоко | 5 | Зеленый |
| Банан | 12 | Желтый |
Такой вариант использования соединений хоть в целом и работоспособен, но крайне плохо показывает себя при большом количестве данных в таблицах. Запрос будет работать гораздо медленнее чем при использовании других видов соединений. Так же при этом соединении мы потеряли часть данных по апельсинам из таблицы фруктов. А значит в результате запроса остались только фрукты которые есть в обеих таблицах. Этот результат будет аналогичен внутреннему соединению.
Дополнительные примеры использования
Множественные условия в соединении.
Выведутся все записи из таблицы Фрукты, но из таблицы цветов присоединится только цвет для записи "Яблоко":
Фрукты.Название,
Фрукты.Количество,
ЦветаФруктов.Цвет
ИЗ
Фрукты КАК Фрукты
ЛЕВОЕ СОЕДИНЕНИЕ ЦветаФруктов КАК ЦветаФруктов
ПО Фрукты.Название = ЦветаФруктов.Фрукт
И Фрукты.Название = "Яблоко"
Результат:
| Название | Количество | Цвет |
|---|---|---|
| Яблоко | 5 | Зеленый |
| Апельсин | 6 | NULL |
| Банан | 12 | NULL |
Множественные соединения в запросе
Присоединим к нашей таблице Фрукты еще одну таблицу, которая содержит цены фруктов. В результате получим еще одну колонку, содержащую цены
Фрукты.Название,
Фрукты.Количество,
ЦветаФруктов.Цвет,
ЦеныФруктов.Цена
ИЗ
Фрукты КАК Фрукты
ЛЕВОЕ СОЕДИНЕНИЕ ЦветаФруктов КАК ЦветаФруктов
ПО Фрукты.Название = ЦветаФруктов.Фрукт
ЛЕВОЕ СОЕДИНЕНИЕ ЦеныФруктов КАК ЦеныФруктов
ПО Фрукты.Название = ЦеныФруктов.Фрукт
Результат:
| Название | Количество | Цвет | Цена |
|---|---|---|---|
| Яблоко | 5 | Зеленый | 50 |
| Апельсин | 6 | NULL | 60 |
| Банан | 12 | Желтый | 70 |
Задачи на эту тему:
- Задача 29. Применение сложных соединений, использование временных таблиц
- Задача 39. Использование объединений и агрегатных функций в запросах
- Задача № 9. Простая выборка записей из табличной части и шапки справочника №2
- Задача 22. Применение агрегатных функций
- Задача № 60. Рассчитать итоговую стоимостную оценку склада «Готовая продукция» в розничных ценах номенклатуры
- Задача № 43. Вывести предыдущие и текущие актуальные розничные цены, процент изменения данных цен
- Задача № 10. Определить общее количество ингредиентов для каждой продукции с отбором
- Задача 30. Применение сложных соединений, использование временных таблиц
- Задача № 11. Определить продукцию, у которой нет ингредиентов
- Задача № 37. Вывести среднюю себестоимость, среднюю розничную цену и среднюю наценку для всех товаров с видом номенклатуры «Готовая продукция»
- Задача 38. Использование объединений и агрегатных функций в запросах
- Задача № 30. Вывести самую высокую розничную цену для номенклатуры с видом номенклатуры «Готовая продукция».
- Задача 69. Получение представления поля. Отбор по полям от реквизита ссылочного типа
- Задача № 29. Вывести себестоимость и розничную цену для всей номенклатуры с видом номенклатуры «Полуфабрикат»
- Задача № 34. Вывести итоговую себестоимость для товара «Торт Прага»
- Задача № 40. Вывести предпоследнюю закупочную цену и текущую закупочную цену для товара «Сыр маскорпоне»
- Задача 20. Применение агрегатных функций.
- Задача 49. Использование агрегатных функций и объединений
- Задача 48. Использование агрегатных функций
- Задача № 35. Вывести себестоимость, розничную цену и наценку для товара "Торт Прага"
- Задача 66. Применение параметров
- Задача 72. Применение агрегатных функций
- Задача 58. Выборка элементов из табличных частей документов
- Задача 50. Использование агрегатных функций и объединений
- Задача № 27. Вывести себестоимость для всей номенклатуры с видом номенклатуры «Готовая продукция»
- Задача № 28. Вывести себестоимость и розничную цену для всей номенклатуры.
- Задача № 45. Вывести самые первые розничные цены товаров и текущие актуальные розничные цены. Посчитать процент изменения данных цен по отношению к друг другу.
- Задача № 8. Простая выборка записей из табличной части и из шапки справочника
- Задача № 12. Определить продукцию с самым большим количеством ингредиентов (по количеству)
- Задача № 32. Вывести закупочные цены для каждого ингредиента товара «Торт Прага»
- Задача 27. Поиск дублей
- Задача № 41. Вывести процент изменения закупочной цены по отношению к предпоследней цене
- Задача № 44. Вывести самые первые закупочные цены и текущие актуальные закупочные цены для всей номенклатуры, у которой заданы такие цены.
- Задача 47. Использование агрегатных функций
- Задача № 13. Определить вес каждой продукции по ингредиентам, входящих в ее состав. Применение агрегатных функций
- Задача № 36. Вывести итоговую себестоимость, розничные цены и наценку для всех товаров с видом «Готовая продукция».
- Задача 28. Поиск дублей по набору условий
- Задача № 21. Применение агрегатных функций к элементам табличных частей по определенному условию