This page has 0 threads | Add post

OUTER and CROSS APPLY joins

Written by Andy Brown

In this tutorial

Our Example Table-Valued Function
Using Standard Joins
Getting to the point: CROSS APPLY and OUTER 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

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!

Previous
tutorial

Dates and times

Previous
part

Cross Joins using SQL

Top of page

Next
part

Self-Joins in SQL (joining a table to itself)

Next
tutorial

Grouping data

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

Joins

OUTER and CROSS APPLY joins

Our Example Table-Valued Function

Using Standard Joins

Getting to the point: CROSS APPLY and OUTER joins

Head office

London

Manchester