OUTER is applied without a subquery

Question

I looked at an article about CROSS APPLYand OUTER APPLYin SQL Server. The following tables were used to illustrate both.

Employees table:

EmployeeID  FirstName   LastName    DepartmentID

1           Orlando     Gee         1
2           Keith       Harris      2
3           Donna       Carreras    3
4           Janet       Gates       3

Department table:

DepartmentID    Name
1               Engineering
2               Administration
3               Sales
4               Marketing
5               Finance

I realized that it OUTER APPLYlooks like LEFT OUTER JOIN.But when I applied OUTER APPLYbetween the tables as shown below,

select * from Department e
outer apply
Employee d
where d.DepartmentID = e.DepartmentID

I got the results below (Same as the results 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

When I applied OUTER APPLYbetween tables as shown below (with right tableas a subquery).

select * from Department e
outer apply
(
select * from
Employee d
where d.DepartmentID = e.DepartmentID
)a

I got below results (Same as results 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

Can someone explain why the two requests gave different outputs?

Answer 1

, :

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 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, - .

Answer 2

, . - where.

, . , null.

NULL - where.

'e' 'd' employee department.

Answer 3

, , . , 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)
;

.

, . . .