23.10.2017
Эффективная Навигация М2М
Меню сайта
Категории раздела
Учет топлива [6]
Статьи посвященные учету топлива
Прочие статьи [7]
SQL - учимся работать [5]
Форма входа
Вход через Google
Вход через Вконтакте
Вход через Facebook
Партнеры
Реклама

SQL: CyberFleet: Занятие №3 Объединение таблиц Часть 2/2 (прочие способы объединения)
 
Занятие №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)
idNameDevice_id
1е3451
2т5673
3н3452
4р369null
5н5676

Таблица Devices
idcode
10001
20005
30003
40004
50006

Теперь сравним три простых запроса - что именно они вернут

код для Managemnt Studio 

create table #Objects (id int,Name varchar(16),Device_id int)
insert into #Objects 
values (1,'е345',1)
insert into #Objects 
values (2,'т567',3)
insert into #Objects 
values (3,'н345',2)
insert into #Objects 
values (4,'р369',null)
insert into #Objects 
values (5,'н567',6)

create table #Devices (id int,code varchar(16))
insert into #Devices 
values (1,'0001')
insert into #Devices 
values (2,'0005')
insert into #Devices 
values (3,'0003')
insert into #Devices 
values (4,'0004')
insert into #Devices 
values (5,'0006')
-- запрос 1
select * from #Objects as O 
inner join #Devices as D on d.id=O.Device_id
-- запрос 2
select * from #Objects as O 
left join #Devices as D on d.id=O.Device_id
-- запрос 3
select * from #Objects as O 
right join #Devices as D on d.id=O.Device_id

drop table #Objects
drop table #Devices

Для запроса через 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 #Devices as D 

Так же верно и обратное, если при использовании метода 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 
cross join #Devices as D 
where d.id=O.Device_id




Источник: SQL, обучение, CYberFleet, left,right,cross,full,join
Похожие материалы
Категория: SQL - учимся работать | Добавил: logoff (23.08.2012) | Автор: Бондарь Михаил aka LogOff
Просмотров: 1895 | Теги: SqL, SQL-Учимся работать, объединение таблиц, join table, CyberFleet, Уроки, скрипты | Рейтинг: 0.0/0
Всего комментариев: 0
Добавлять комментарии могут только зарегистрированные пользователи.
[ Регистрация | Вход ]
Поиск
TOP 10 Популярное
1 Оборудование> Гранит-Навигатор 4.14
2 Оборудование> Абонентский терминал M2M Cyber GLX
3 Оборудование> Гранит-Навигатор 4.14: Настройка терминала
4 Прочее> "Красные" против "Синих" - статистка космических запусков России (СССР) и США
5 CyberFleet, CrossPoint> CyberFleet: Переустановка CyberFleet
6 CyberFleet, CrossPoint> КиберФлит: Учет топлива при помощи датчиков
7 Оборудование> M2M Cyber GLX: отправка команд
8 Прочее> SQL: CyberFleet: Занятие №3 Объединение таблиц Часть 1/2 (теория, inner join)
9 CyberFleet, CrossPoint> КиберФлит: Причины расхождения пробега по данным одометра и карте
10 CyberFleet, CrossPoint> КиберФлит: Подключаем карту Google
Наш опрос
Тахограф это?
Всего ответов: 23
LogOff © 2017
Сайт создан в системе uCoz Рейтинг GPS Клуба. GPS навигаторы. GPS мониториг. GPS трекеры. ГЛОНАСС