Занятие №3 из цикла "SQL - Учимся писать скрипты" более подробно посвящено объединению связанных таблиц в одно целое в одном запросе.Часть 2/2 - left, right, cross,full join
В Части 1/2 Занятия №3 я рассказал о том, какие бывают типы соединения таблиц, привели пример соединения нескольких таблиц посредством inner join. Развиваю тему использования соединения.
Кроме inner join есть еще несколько типов соединения, которые используются для таблиц.
Left join, Right join
Второй по частоте использования типа соединения после inner join будет left (right) join. В отличии от inner данные типы более "мягкие", как допускают отсутствие парных строк в другой таблице. Например, если с Табл1, соединяется Табл2, то при inner join для каждой записи в Табл1 должна быть парная запись в Табл2 и наоборот. Если же для какой-то строи не удается найти другую "парную", то в результат она не будет включена. Т.е. таблицы при inner join являются равноправными.
В случае же с left join данного "равноправия" нет, и в отчет будут включены все строки из таблицы "слева" (или права при right join), даже если для них не найдено парных из таблицы "справа". Методы left join и right join друг друга полностью заменяют, если поменять таблицы местами в запросе - использование того или другого метода - дань удобству чтения кода. Однако, так как большинство читает код слева на право, то лучше использовать left join, как более читаемый и привычный (дело вкуса каждого).
Пример-пояснение разницы в работе методов: Таблица Objects (в колонке device_id ссылка на колонку id таблицы Devices) | id | Name | Device_id | | 1 | е345 | 1 | | 2 | т567 | 3 | | 3 | н345 | 2 | | 4 | р369 | null | | 5 | н567 | 6 |
Таблица Devices | id | code | | 1 | 0001 | | 2 | 0005 | | 3 | 0003 | | 4 | 0004 | | 5 | 0006 |
Теперь сравним три простых запроса - что именно они вернут
код для Managemnt Studio
create table #Objects (id int,Name varchar(16),Device_id int) create table #Devices (id int,code varchar(16)) select * from #Objects as O inner join #Devices as D on d.id=O.Device_id select * from #Objects as O left join #Devices as D on d.id=O.Device_id select * from #Objects as O right join #Devices as D on d.id=O.Device_id
Для запроса через inner join возврат будет (запрос 1) id Name Device_id id code
1 е345 1 1 0001 2 т567 3 3 0003 3 н345 2 2 0005 т.е. вернет только три строки, а не 5. Причина этого в том, что для строк таблицы #Objects с ID=4 и ID=5 нет парных строк из таблицы #Devices, так как для id=4 указано в device_id=null, а для id=5 указано в device_id=6. Но в таблице #Devices нет строк, для которых id=6 или id=null
Запрос же left join вернет другую таблицу (запрос 2) id Name Device_id id code
1 е345 1 1 0001 2 т567 3 3 0003 3 н345 2 2 0005 4 р369 NULL NULL NULL 5 н567 6 NULL NULL
Как видим, все строки таблицы #Objects были включены в запрос, но не для всех были найдены парные строки в таблице #Devices, потом в колонке code для таких строк стоит NULL (id=4,5)
запрос же right join вернет третью таблицу (запрос 3)
id Name Device_id id code
1 е345 1 1 0001 3 н345 2 2 0005 2 т567 3 3 0003 NULL NULL NULL 4 0004 NULL NULL NULL 5 0006
Как видим в этой таблице тоже 5 строк, но "ведущей" таблицей при этом является #Devices, и именно из нее взяты все строки, но для id=4,5 не найдены строки-пары (потому в name стоит null).
Обозначив разными кругами разные таблицы действие типа соединения на результат графически можно представить так:
Как видим, inner join включает в себя меньшую часть данных (т.е. более строгое соответствие).
Задача-пример:
Найти в CyberFleet, приборы, которые не привязаны к машинам. Решение:
Список приборов хранится в таблице dbo.SYS_DEV_Device, список машин в таблице dbo.FLEET_Object, привязка хранится в списке машин через указание в столбце Device_ID ссылку на колонку ID списка приборов (dbo.FLEET_Object.Device_ID=dbo.SYS_DEV_Device.ID). Тогда, если формулировать запрос на разговорном языке, то он будет звучать примерно так:
Выбери все строки из таблицы приборов, соедини с таблицей машин по нужным колонкам, профильтруй таблицу, оставив только приборы, для которых связи не найдено (т.е. dbo.FLEET_Object.Device_ID is null)
Метод соединения inner join нам не подойдет, так как в этом случае из двух таблиц будут выбраны только те стоки, для которых есть парные. А у нас задача как раз найти "не парные" строки из одной из таблиц - т.е. нужен тип соединения left join. При этом, главной таблице ("слева") будет именно таблица со списком приборов (dbo.SYS_DEV_Device)
use bn select d.id, d.code -- список колонок результата from dbo.SYS_DEV_Device as D -- таблица приборов left join -- тип соединения left join dbo.FLEET_Object as O -- таблица машин on O.Device_ID=D.id -- связь таблиц where o.Device_ID is null -- условие, что строки для которых не найдено пары
Full join Метод соединения таблиц full join, сочетает в себе три метода как бы inner, left,right join. Т.е. результирующая таблица будет состоять из всех строк с парами, так и из всех строк из 1-ой и 2-ой таблице, для которых пары нет. При этом, в соответствующих столбцах будет указано null.
Для нашего примера при использовании full join select * from #Objects as O
full join #Devices as D on d.id=O.Device_id
вернет такую таблицу
id Name Device_id id code
1 е345 1 1 0001 2 т567 3 3 0003 3 н345 2 2 0005 4 р369 NULL NULL NULL 5 н567 6 NULL NULL NULL NULL NULL 4 0004 NULL NULL NULL 5 0006
Как видим, для всех строк, где возможно пары найдены, строки без пар из обоих таблиц так же включены в список Графически данный метод можно представить вот так:
Cross join
Данный метод - наиболее редко используемый, так как позволяет получить "произведение" всех таблиц, когда для каждой строки из Таблицы 1 подходящей будет любая строка Таблицы 2, т.е. в нашем случае запрос:
select * from #Objects as O cross join #Devices as D
id Name Device_id id code
1 е345 1 1 0001 2 т567 3 1 0001 3 н345 2 1 0001 4 р369 NULL 1 0001 5 н567 6 1 0001 1 е345 1 2 0005 2 т567 3 2 0005 3 н345 2 2 0005 4 р369 NULL 2 0005 5 н567 6 2 0005 1 е345 1 3 0003 2 т567 3 3 0003 3 н345 2 3 0003 4 р369 NULL 3 0003 5 н567 6 3 0003 1 е345 1 4 0004 2 т567 3 4 0004 3 н345 2 4 0004 4 р369 NULL 4 0004 5 н567 6 4 0004 1 е345 1 5 0006 2 т567 3 5 0006 3 н345 2 5 0006 4 р369 NULL 5 0006 5 н567 6 5 0006
Точно такого же результата можно добиться в любым методом, если в качестве условия соединения таблиц (условия поиска парной строки) указать после on любое заранее истинное условие, ну например 1=1
select * from #Objects as O inner join #Devices as D on 1=1
однозначен запросу
select * from #Objects as O
Так же верно и обратное, если при использовании метода cross join указать в условиях (where) равенство значений колонок таблиц, то это будет приравнено к использованию метода Inner join т.е. запрос:
select * from #Objects as O inner join #Devices as D on d.id=O.Device_id
однозначен запросу select * from #Objects as O
Источник: SQL, обучение, CYberFleet, left,right,cross,full,join | 
