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

Although you can join tables using the apply operator this is not what it was designed for. The primary purpose, from MSDN:

The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.

As the name suggests; a table-valued function is any function that returns a table. Here's a simple function:

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

And here is some sample data to play with:

-- Sample data.
DECLARE @SampleTable TABLE
    (
        Number INT
    )
;

INSERT INTO @SampleTable
    (
        Number
    )
VALUES
    (1),
    (2),
    (3)
;

Applying the function to our table, like so:

-- Using apply.
SELECT
    st.Number,
    ad.Result
FROM
    @SampleTable AS st
        CROSS APPLY AddOne(st.Number) AS ad
;

Returns:

Number  Result
1       2
2       3
3       4

This blog entry by Robert Sheldon explains the above in much more detail.

The apply operator can also be combined with the table value constructor to return the exact same result by another method:

-- Using TVC.
SELECT
    st.Number,
    ad.Result
FROM
    @SampleTable AS st
        CROSS APPLY 
            (
                VALUES  
                    (st.Number + 1)
            ) AS ad(Result)
;

This powerful technique allows you to perform calculations on your data, and give the result an alias.

This answer barely scratches the surface when it comes to the apply operator. It has many more tricks up its sleeve. I would highly recommend further research.

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.

Answer 3

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.