BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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!