ВНЕШНЕЕ применение без подзапроса
Я прочитал статью о CROSS APPLY а также OUTER APPLYв SQL Server. Следующие таблицы были использованы для иллюстрации обоих.
Таблица сотрудников:
EmployeeID FirstName LastName DepartmentID
1 Orlando Gee 1
2 Keith Harris 2
3 Donna Carreras 3
4 Janet Gates 3
Стол отдела:
DepartmentID Name
1 Engineering
2 Administration
3 Sales
4 Marketing
5 Finance
Я понял это OUTER APPLY похож на LEFT OUTER JOIN. Но когда я подал заявку OUTER APPLY между таблицами, как показано ниже,
select * from Department e
outer apply
Employee d
where d.DepartmentID = e.DepartmentID
Я получил ниже результатов (так же, как INNER JOIN Результаты)
DepartmentID Name EmployeeID FirstName LastName DepartmentID
1 Engineering 1 Orlando Gee 1
2 Administration 2 Keith Harris 2
3 Sales 3 Donna Carreras 3
3 Sales 4 Janet Gates 3
Когда я подал заявку OUTER APPLY между таблицами, как показано ниже (с right table как подзапрос).
select * from Department e
outer apply
(
select * from
Employee d
where d.DepartmentID = e.DepartmentID
)a
Я получил ниже результатов (так же, как LEFT OUTER JOIN Результаты)
DepartmentID Name EmployeeID FirstName LastName DepartmentID
1 Engineering 1 Orlando Gee 1
2 Administration 2 Keith Harris 2
3 Sales 3 Donna Carreras 3
3 Sales 4 Janet Gates 3
4 Marketing NULL NULL NULL NULL
5 Finance NULL NULL NULL NULL
Кто-то может объяснить, почему два запроса дали разные outputs?
3 ответа
Я думаю, что ключом к пониманию этого является вывод этого запроса:
select * from Department e
outer apply
Employee d
--where d.DepartmentID = e.DepartmentID
Что просто дает вам декартово произведение двух таблиц:
DepartmentID Name EmployeeID FirstName LastName DepartmentID
--------------------------------------------------------------------------------------
1 Engineering 1 Orlando Gee 1
2 Administration 1 Orlando Gee 1
3 Sales 1 Orlando Gee 1
4 Marketing 1 Orlando Gee 1
5 Finance 1 Orlando Gee 1
1 Engineering 2 Keith Harris 2
2 Administration 2 Keith Harris 2
3 Sales 2 Keith Harris 2
4 Marketing 2 Keith Harris 2
5 Finance 2 Keith Harris 2
1 Engineering 3 Donna Carreras 3
2 Administration 3 Donna Carreras 3
3 Sales 3 Donna Carreras 3
4 Marketing 3 Donna Carreras 3
5 Finance 3 Donna Carreras 3
1 Engineering 4 Janet Gates 3
2 Administration 4 Janet Gates 3
3 Sales 4 Janet Gates 3
4 Marketing 4 Janet Gates 3
5 Finance 4 Janet Gates 3
Теперь, когда вы добавляете обратно в предложении, где where d.DepartmentID = e.DepartmentIDВы удаляете большинство из этих строк:
DepartmentID Name EmployeeID FirstName LastName DepartmentID
--------------------------------------------------------------------------------------
1 Engineering 1 Orlando Gee 1
2 Administration 2 Keith Harris 2
3 Sales 3 Donna Carreras 3
3 Sales 4 Janet Gates 3
Этот запрос семантически эквивалентен:
SELECT * FROM Department e
CROSS JOIN Employee d
WHERE d.DepartmentID = e.DepartmentID;
Что эквивалентно:
SELECT * FROM Department e
INNER JOIN Employee d
ON d.DepartmentID = e.DepartmentID;
Так что, хотя у вас есть OUTER APPLY ваше положение where превращает его в INNER JOINТаким образом, удаляя отделы без сотрудников.
Вы можете увидеть план ниже для вашего первого запроса внешнего обращения между Департаментом и сотрудником. Из-за вашего where пункт. 
И план выполнения для второго запроса, показывающий левое внешнее соединение между таблицей Department и employee. Во втором запросе для каждого отдела ваш проверяющий сотрудник, если ни один из сотрудников не присутствует, подзапрос вернет значение NULL.
Но в первом запросе строки с NULL ценности были устранены из-за вашего where пункт.
На изображении "е" и "д" являются employee а также department столы.
Хотя вы можете объединять таблицы с помощью оператора apply, это не то, для чего он был разработан. Основное назначение от MSDN:
Оператор APPLY позволяет вам вызывать табличную функцию для каждой строки, возвращаемой внешним табличным выражением запроса.
Как следует из названия; Табличная функция - это любая функция, которая возвращает таблицу. Вот простая функция:
-- Function that takes a number, adds one and returns the result.
CREATE FUNCTION AddOne
(
@StartNumber INT
)
RETURNS TABLE
AS
RETURN
(
SELECT
@StartNumber + 1 AS [Result]
)
GO
И вот некоторые примеры данных для игры:
-- Sample data.
DECLARE @SampleTable TABLE
(
Number INT
)
;
INSERT INTO @SampleTable
(
Number
)
VALUES
(1),
(2),
(3)
;
Применяя функцию к нашей таблице, вот так:
-- Using apply.
SELECT
st.Number,
ad.Result
FROM
@SampleTable AS st
CROSS APPLY AddOne(st.Number) AS ad
;
Возвращает:
Number Result
1 2
2 3
3 4
Эта запись в блоге Роберта Шелдона объясняет вышеизложенное более подробно.
Оператор apply также может быть объединен с конструктором табличных значений для возврата того же результата другим методом:
-- Using TVC.
SELECT
st.Number,
ad.Result
FROM
@SampleTable AS st
CROSS APPLY
(
VALUES
(st.Number + 1)
) AS ad(Result)
;
Этот мощный метод позволяет вам выполнять вычисления на ваших данных и давать результату псевдоним.
Этот ответ едва царапает поверхность, когда дело доходит до оператора apply. У него есть еще много хитростей в рукаве. Я очень рекомендую дальнейшие исследования.