Эффективность условия в join

У меня вопрос касательно условия в запросах join
В чем разница касательно производительности между следующими запросами:
Select c.FIO from Clients c Inner Join ClientsDuplicates cd On cd.client_id = c.client_id and cd.amount = 1
Select c.FIO from Clients c Inner Join ClientsDuplicates cd On cd.client_id = c.client_Id Where cd.amount = 1

Ответ

Для join (inner) никакой разницы. Query processor построит идентичные планы в обоих случаях. Также, используя, например, свойство коммутативности внутреннего соединения (A join B = B join A) можно записать ещё одну форму:
select c.FIO from ClientsDuplicates cd inner join Clients c on c.client_id = cd.client_id where cd.amount = 1
или вот другая форма:
;with cd1 as (select client_id from ClientsDuplicates where amount = 1) select c.FIO from Clients c inner join cd1 on cd1.client_id = cd1.client_id
ещё одна вариация:
;with cd1 as (select client_id from ClientsDuplicates where amount = 1) select c.FIO from cd1 inner join Clients c on c.client_id = cd1.client_id
Все эти формы данного запроса вернут одинаковый результат, и будут иметь идентичный план исполнения, поскольку, с точки зрения реляционной алгебры, эти отношения эквиваленты, или, точнее сказать, они приводимы друг к другу.
Query processor не выполняет написанное прямо так как это написано (исключая случаи с различными hint), перед выполнением проводится оптимизация запроса. Приведу аналогию. Допустим у нас есть математическое выражение 5*3 + 4*3. Можно вычислить его прямо так как написано, и на это потребуется три действия, а можно заметить, что это то же самое, что (5+4)*3, и это уже два действия. И хотя это весьма отдалённая аналогия, но задача оптимизатора видеть подобное, видеть выгоду там, где её можно извлечь. и, что касается данного конкретного случая, то Sql Server это умеет достаточно давно.
Последняя форма запроса (with cd1 as () select ... from cd1 join c) наиболее наглядно отражает реальный план исполнения данного запроса. Сначала разрешаются известности, потом неизвестности. Если какой то предикат позволяет уменьшить число строк, которые будут участвовать в соединении на следующем этапе, и его можно применить перед непосредственно соединением, не искажая результат, то он будет применён.
Разница в положении условия в on либо where будет тогда, когда это будет не inner join, а outer (например, left join). Разница в первую очередь семантическая, и, как следствие, в производительности. Это можно видеть на следующем примере.
Пусть есть данные вида:
declare @group table (id int, code varchar(10), name varchar(10)) declare @data table (groupid int, data varchar(10)) insert into @group values (1, 'G1', 'Group 1'), (2, 'G2', 'Group 2') insert into @data values (1, 'D1.1'), (1, 'D1.2'), (2, 'D2.1'), (2, 'D2.2'), (NULL, 'D_.1'), (NULL, 'D_.2')
Сравните результат следующих двух запросов:
select d.data, g.name from @data d left join @group g on g.id = d.groupid where g.code = 'G1'
и
select d.data, g.name from @data d left join @group g on g.id = d.groupid and g.code = 'G1'
Самантика следующая:
Первый запрос: 1) хочу элементы данных и имя группы, если оно есть; 2) из всего этого хочу только те пары, у которых код группы G1. Фактически исполнится как inner join. Второй запрос: хочу все элементы данных и имя группы, но имя группы я хочу только, если это группа с кодом G1
Так как второй запрос берёт все элементы данных, а первый не все, то, естественно, производительность будет отличаться. В зависимости от количества данных в обеих таблицах и индексов это могут быть и тысячи раз. Однако, при разной семантике сравнивать производительность, полагаю не совсем корректно.