OUTER apply without subquery

Question

I went through an article about CROSS APPLY and OUTER APPLYin SQL Server. The following tables were used to illustrate both.

Employee 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 understood that OUTER APPLY is similar to LEFT OUTER JOIN. But when I applied OUTER APPLY between tables as below,

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

I got below results (Same as INNER JOIN results)

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 APPLY between tables as below( with right table as a subquery).

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

I got below results (Same as LEFT OUTER JOIN results)

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 queries gave different outputs?

Answer 1

I think the key to understanding this is seeing the output of this query:

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

Which simply gives you the cartesian product of the two tables:

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   

Now when you add back in the where clause where d.DepartmentID = e.DepartmentID, you eliminate most of these rows:

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   

This query is semantically equivalent to:

SELECT * FROM Department e
CROSS JOIN Employee d
WHERE d.DepartmentID = e.DepartmentID;

Which is equabalent to:

SELECT * FROM Department e
INNER JOIN Employee d
ON d.DepartmentID = e.DepartmentID;

So even though you have an OUTER APPLY your where clause turns it into an INNER JOIN, thus removing the departments with no employees.

Answer 2

You can see the plan below for your first query outer apply between Department and employee. It is converted to inner join because of your where clause.

And execution plan for second query showing Left outer join between Department and employee table. In your second query for each department your checking employee if no employee present sub query will return null.

But in the first query rows with NULL values got eliminated because of your where clause.

In image 'e' and 'd' are employee and department tables.