This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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

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!

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

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