Разница в производительности между левым соединением и внутренним соединением

Есть по крайней мере один случай, когда LEFT [OUTER] JOIN - лучший вариант, чем [INNER] JOIN. Я говорю о получении тех же результатов, используя OUTER вместо INNER.

Пример (я использую базу данных AdventureWorks 2008):

-- Some metadata infos
SELECT  fk.is_not_trusted,  fk.name
FROM    sys.foreign_keys fk
WHERE   fk.parent_object_id=object_id('Sales.SalesOrderDetail');
GO

CREATE VIEW View1
AS 
SELECT  h.OrderDate, d.SalesOrderDetailID, o.ModifiedDate
FROM    Sales.SalesOrderDetail d
INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
INNER JOIN Sales.SpecialOfferProduct o ON d.SpecialOfferID=o.SpecialOfferID AND d.ProductID=o.ProductID;
GO

CREATE VIEW View2
AS
SELECT  h.OrderDate, d.SalesOrderDetailID, o.ModifiedDate
FROM    Sales.SalesOrderDetail d
INNER JOIN Sales.SalesOrderHeader h ON d.SalesOrderID = h.SalesOrderID
LEFT JOIN Sales.SpecialOfferProduct o ON d.SpecialOfferID=o.SpecialOfferID AND d.ProductID=o.ProductID;
GO

SELECT  SalesOrderDetailID
FROM    View1;

SELECT  SalesOrderDetailID
FROM    View2;

Результаты по первому запросу:

is_not_trusted name
-------------- ---------------------------------------------------------------
0              FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID
0              FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID

Планы выполнения для двух последних запросов:

Примечание 1 / представление 1: если мы посмотрим на план выполнения для SELECT SalesOrderDetailID FROM View1, мы увидим FK-исключение, потому что ограничение FK_SalesOrderDetail_SalesOrderHeader_SalesOrderID является доверенным и имеет один столбец. Но сервер вынужден (из-за INNER JOIN Sales.SpecialOfferProduct) читать данные из третьей таблицы (SpecialOfferProduct), даже если предложения SELECT/WHERE не содержат столбцов из этой таблицы, а ограничение FK (FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID) (также) является доверенным. Это происходит потому, что последний FK является многоколоночным.

Примечание 2 / представление 2: Что, если мы хотим удалить чтение (_11 _ / _ 12_) на Sales.SpecialOfferProduct? Этот второй FK является многоколоночным, и в таких случаях SQL Server не может удалить FK (см. Предыдущее сообщение в блоге Конора Каннигэма). В этом случае нам нужно заменить INNER JOIN Sales.SpecialOfferProduct на LEFT OUTER JOIN Sales.SpecialOfferProduct, чтобы получить исключение FK. И SpecialOfferID, и ProductID столбцы NOT NULL, и у нас есть доверенный FK, ссылающийся на SpecialOfferProduct таблицу.

Помимо проблемы, связанной с тем, что внешнее соединение может возвращать больший набор результатов из-за сохраненных дополнительных строк, еще один момент заключается в том, что оптимизатор имеет больший диапазон возможностей при создании плана выполнения, поскольку INNER JOIN является коммутативным и ассоциативным.

Итак, в следующем примере B индексируется, а A - нет.

CREATE TABLE A(X INT, Filler CHAR(8000))

INSERT INTO A 
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY @@SPID), ''
FROM sys.all_columns

CREATE TABLE B(X INT PRIMARY KEY, Filler CHAR(8000))

INSERT INTO B
SELECT TOP 10000 ROW_NUMBER() OVER (ORDER BY @@SPID), ''
FROM sys.all_columns

SELECT *
FROM B INNER JOIN A ON A.X = B.X

SELECT *
FROM B LEFT JOIN A ON A.X = B.X

Оптимизатор знает, что B INNER JOIN A и A INNER JOIN B одинаковы, и создает план с вложенными циклами, ищущими в таблице B.

Это преобразование недопустимо только для внешнего соединения и вложенных циклов поддерживает левое внешнее соединение, а не правое внешнее соединение, поэтому необходимо использовать другой тип соединения.

Но с практической точки зрения вам следует просто выбрать нужный тип соединения, который даст вам правильную семантику.