Using OUTER and CROSS APPLY joins to link to table-valued functions
For the sake of completeness, this blog explains how to join to the results of table-valued functions (like a correlated subquery on steroids).

This blog is part of our tutorial on learning SQL.  Wise Owl also run impressively good training courses in SQL.

Posted by Andy Brown on 03 May 2013

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

# OUTER and CROSS APPLY joins

As the old music hall joke doesn't go: "What do you get if you cross a join with a correlated subquery?".  Answer: an outer or cross apply join.

Usefulness warning: this is by no stretch of the imagination a core SQL subject.  You'll need to know about joins and table-valued functions to understand it, and you can survive without reading any further!

## Our Example Table-Valued Function

Let's say that you have written a table-valued function to return all of the films in a movies database made by a particular director:

CREATE FUNCTION fnFilms(

@DirectorId int

)

RETURNS TABLE

AS

RETURN

-- return all of the films for a given director

SELECT

FilmId,

FilmName,

FilmDirectorId

FROM

tblFilm

WHERE

FilmDirectorId = @DirectorId

Each film's director has a number - so you could use this, for example, to show all of the films made by director number 17, whoever that might be:

SELECT * FROM dbo.fnFilms(17)

Here's what this would return for our database:

If you're trying to guess who director number 17 is, think Kevin Reynolds.

Suppose you now want to return a list of all of the directors born in 1952, together with the films they've made.

## Using Standard Joins

Let's first look at how we'd solve this problem without using a table-valued function.  We could do this with a simple join:

-- show all directors born in 1952, with their films

SELECT

d.DirectorName,

f.FilmName,

YEAR(d.DirectorDob) AS Yob

FROM

tblDirector AS d

INNER JOIN tblFilm AS f

ON d.DirectorId = f.FilmDirectorId

WHERE

YEAR(d.DirectorDob) = 1952

ORDER BY

d.DirectorName,

f.FilmName

That is, show all the directors, with any corresponding films.  Here's what this might show:

Each director appears with the films they've made. Sammo Hung is in the database and was born in 1952, but doesn't have any corresponding film records, and so doesn't appear.

If you wanted to do this with an outer join, you'd see Sammo Hung listed too:

This row would appear also if you changed INNER to LEFT OUTER in the above SQL.

## Getting to the point: CROSS APPLY and OUTER joins

We want to run our table-valued function once for each director (this is basically a correlated subquery with a twist):

-- show all directors born in 1952, with their films

SELECT

d.DirectorId,

d.DirectorName,

f.FilmName,

YEAR(d.DirectorDob) AS Yob

FROM

tblDirector AS d

CROSS APPLY fnFilms(d.DirectorId) AS f

WHERE

YEAR(d.DirectorDob) = 1952

ORDER BY

DirectorName,

FilmName

We are running the fnFilms function for each director, and showing all of the rows returned:

As before, we see for each director a list of the films they've made.

The difference between CROSS APPLY and OUTER APPLY is like the difference between an inner and an outer join.  Here's what OUTER APPLY would look like:

-- show all directors born in 1952, with their films

SELECT

d.DirectorId,

d.DirectorName,

f.FilmName,

YEAR(d.DirectorDob) AS Yob

FROM

tblDirector AS d

OUTER APPLY fnFilms(d.DirectorId) AS f

WHERE

YEAR(d.DirectorDob) = 1952

ORDER BY

DirectorName,

FilmName

This would include in the returned results all directors born in 1952, even where they haven't made any films:

Sammo Hung is back in the list, even though he has no corresponding films in our database.

Disappointed at CROSS APPLY and OUTER APPLY joins?  Me too!