Раздел JOIN используется для связывания данных двух таблиц между собой. Но почему лучше использовать JOIN, чем простое перечисление таблиц в разделе FROM и перечисление условий присоединения с использованием операторов =, *=, =* и т.п. в разделе WHERE? Давайте разберемся.
Как правило, нормализованные распределенные базы данных строятся таким образом, что в них есть "главные таблицы", а к ним уже присоединяются несколько справочников по каким-нибудь специальным ID-полям или по нескольким простым полям. Сами же "главные таблицы" тоже могут являться справочниками для других таблиц.
Для начала создайте в тестовой базе таблицы с помощью следующего скрипта:
CREATE TABLE Master1
(
Master1ID int IDENTITY (1,1) NOT NULL,
Detail1ID int NULL,
Detail2ID int NULL,
Name varchar(200),
CONSTRAINT PK_Master1 PRIMARY KEY CLUSTERED
(Master1ID)
)
GO
CREATE TABLE Detail1
(
Detail1ID int IDENTITY (1,1) NOT NULL,
Name varchar(200),
CONSTRAINT PK_Detail1 PRIMARY KEY CLUSTERED
(Detail1ID)
)
GO
CREATE TABLE Detail2
(
Detail2ID int IDENTITY (1,1) NOT NULL,
Name varchar(200),
CONSTRAINT PK_Detail2 PRIMARY KEY CLUSTERED
(Detail2ID)
)
GO
insert Detail1 (Name) values('1')
insert Detail1 (Name) values('2')
insert Detail1 (Name) values('3')
insert Detail1 (Name) values('4')
GO
insert Detail2 (Name) values('d1')
insert Detail2 (Name) values('d2')
insert Detail2 (Name) values('d3')
insert Detail2 (Name) values('d4')
GO
insert Master1 (Detail1ID,Detail2ID,Name) values(1,1,'1')
insert Master1 (Detail1ID,Detail2ID,Name) values(2,2,'2')
insert Master1 (Detail1ID,Detail2ID,Name) values(1,2,'3')
insert Master1 (Detail1ID,Detail2ID,Name) values(4,3,'1')
insert Master1 (Detail1ID,Detail2ID,Name) values(null,1,'n1')
insert Master1 (Detail1ID,Detail2ID,Name) values(3,null,'n2')
GO
С помощью JOIN-ов выборки из таких структур становятся читабельнее, быстрее и лучше разбираются оптимизатором и соответственно быстрее работают.
В секции ON пишется условие, по которому присоединяется таблица.
Без JOIN простая выборка будет следующей:
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a, Detail1 b, Detail2 c
where a.Detail1ID=b.Detail1ID
and a.Detail2ID=c.Detail2ID
С использованием JOIN эта же выборка:
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a
join Detail1 b on b.Detail1ID=a.Detail1ID-- одна строка - один присоединенный справочник
join Detail2 c on c.Detail2ID=a.Detail2ID
Сразу следует сказать, что писать лишние буковки совершенно ни к чему. И на данный момент лучше всего использовать только три вида JOIN. Остальные виды чаще всего приводят только к запутыванию кода.
Краткий вид и полный вид:
join = inner join
left join = left outer join
cross join = указание таблиц через запятую в разделе from.
В нашем случае задача усложняется за счет пустых ключевых полей справочников. Т.е. хотелось бы получить все 6 строк. Вот две выборки - без JOIN и с ним:
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a, Detail1 b, Detail2 c
where a.Detail1ID*=b.Detail1ID
and a.Detail2ID*=c.Detail2ID
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a
left join Detail1 b on b.Detail1ID=a.Detail1ID
left join Detail2 c on c.Detail2ID=a.Detail2ID
Если Вы полностью уверены, что в главной таблице нет пустых ключевых полей, то лучше использовать JOIN без LEFT, т.к. такая конструкция работает быстрее.
Стоит немного усложнить задачу и конструкция без JOIN уже не поможет. Случай присоединения таблицы не по одному ключевому полю, а по двум и более. У нас это будет ID и Name. Попробуйте выполнить следующий скрипт:
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a, Detail1 b, Detail2 c
where a.Detail1ID*=b.Detail1ID and b.Name*=a.Name
and a.Detail2ID*=c.Detail2ID and c.Name*=a.Name
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a
left join Detail1 b on b.Detail1ID=a.Detail1ID and b.Name=a.Name
left join Detail2 c on c.Detail2ID=a.Detail2ID and c.Name=a.Name
Что и требовалось доказать. Без JOIN никуда.
Если нужно написать дополнительные условия, то их лучше помещать в разделе WHERE. Хотя если используется JOIN без LEFT, то нет никакой разницы, где писать условие - хочешь в секции ON, хочешь в разделе WHERE. Вот два идентичных запроса:
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a
join Detail1 b on b.Detail1ID=a.Detail1ID
join Detail2 c on c.Detail2ID=a.Detail2ID and a.Name='1'
select a.Master1ID, a.Name, b.Name, c.Name
from Master1 a
join Detail1 b on b.Detail1ID=a.Detail1ID
join Detail2 c on c.Detail2ID=a.Detail2ID
where a.Name='1'
И еще использовать JOIN удобнее, чем перечисление в разделе FROM тем, что в любой момент можно поставить LEFT и в результат запроса сразу будут включены все строки с пустым ключевым полем справочника.
А CROSS JOIN больше относиться к теме "маньячества", когда условия на присоединение нет (чистое перемножение), а писать больше двух таблиц в секции FROM уже не хочется.
И еще. Иногда бывает разница в быстродействии при присоединении маленькой таблицы к большой и наоборот. Для оптимизации стоит поэкспериментировать.
Надеюсь, Вы нашли эту информацию полезной. Вопросы, предложения и пожелания шлите на адрес sql@likor.ru С уважением, Сергей Кошкин.
