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