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.

Getting more out of Common Table Expressions (CTEs)

I've blogged previously on creating CTEs, but
thanks to Chepren from a recent course for showing me that there is quite a bit
more to them than meets the eye (or than met my eye, at any rate).

You can try the example SQL queries in this blog yourself if you first
download and execute this script
to generate the sample database.

A refresher - basic CTEs

Common Table Expressions (henceforth referred to by the snappier
abbreviation of CTEs) allow you to divide a complex query into two
parts - or so I thought.

For example, suppose that you want to show the names of the actors in our
database who have appeared in short films (defined here as lasting less than an
hour and a half). You could first write a query to get the short films:

SELECT

f.FilmId,

f.FilmName,

f.FilmOscarWins as Oscars,

f.FilmRunTimeMinutes as FilmLength,

f.FilmDirectorID as DirectorId

FROM

tblFilm AS f

WHERE

f.FilmRunTimeMinutes < 90

For our sample database, this would give 3 films:

The three short films in the database, out of the 260 in total.

Rather than saving this as a temporary table, table variable or view, you could
just directly refer to it immediately after you've run it using a CTE:

USE WiseOwlMovies

GO

-- get a list of films lasting less than
an hour and a half

WITH ShortFilms as (

SELECT

f.FilmId,

f.FilmName,

f.FilmOscarWins as Oscars,

f.FilmRunTimeMinutes as FilmLength,

f.FilmDirectorID as DirectorId

FROM

tblFilm AS f

WHERE

f.FilmRunTimeMinutes < 90

)

-- now show actors who appear in them

SELECTDISTINCT

a.ActorName

FROM

ShortFilms as sf

INNERJOIN tblCast as c ON sf.FilmID = c.CastFilmID

INNERJOIN tblActor AS a ON c.CastActorID = a.ActorID

Thus the CTE (here given the name ShortFilms) will behave in
every respect exactly like any other table, although it has a very limited
lifespan.

What I didn't know - extending CTEs

However, CTEs are even better than this, as you can keep creating them within
a query. This is best shown by example.

Suppose that you want to show the films made by actors who have at some stage
appeared in short films. Sound daunting? Not if you break it down
into 3 parts:

USE WiseOwlMovies

GO

-- get a list of the ids of films lasting less than

-- an hour and a half

WITH ShortFilms as (

SELECT

f.FilmId,

f.FilmName

FROM

tblFilm AS f

WHERE

f.FilmRunTimeMinutes < 90

),

ActorsForShortFilms as (

-- now show the ids of actors who appear in them

SELECTDISTINCT

c.CastActorID AS ActorId,

a.ActorName

FROM

ShortFilms as sf

INNERJOIN tblCast as c ON sf.FilmID = c.CastFilmID

INNERJOIN tblActor AS a ON c.CastActorID = a.ActorID

)

-- finally, get the films these actors appeared in

SELECTDISTINCT

f.FilmName

FROM

ActorsForShortFilms as sfActors

INNERJOIN tblCast AS c ON sfActors.ActorId = c.CastActorID

INNERJOIN tblFilm as f ON f.FilmID = c.CastFilmID

ORDERBY

f.FilmName ASC

This creates:

a CTE called ShortFilms to hold the films
lasting less than 90 minutes; then

a CTE based on this called ActorsForShortFilms
listing actors appearing in these films.

When run, this query produces the following list:

For example, Tommy Lee Jones appeared in
Men in Black 2, one of the
short films, but he also appeared in Men
in Black and The Fugitive,
so these also appear in the final list. If
you're wondering why no films starring Sacha
Baron-Cohen appear in the list, it's because the
Wise Owl movies database doesn't have any actors
listed for the Borat film.

Extending CTEs again

You can extend this, I presume, as many times as you like. The
following example uses 3 separate CTEs to show (deep breath) the 34 actors who
appear in films which feature actors appearing in the 3 shortest films:

USE WiseOwlMovies

GO

-- short films

WITH ShortFilms as (

SELECT f.FilmId

FROM tblFilm AS f

WHERE f.FilmRunTimeMinutes < 90

),

-- actors in them,

ActorsForShortFilms as (

SELECTDISTINCT

c.CastActorID AS ActorId

FROM

ShortFilms as sf

INNERJOIN tblCast as c ON sf.FilmID = c.CastFilmID

),

-- films made by these actors

FilmsByTheseActors AS (

SELECTDISTINCT

c.CastFilmId as FilmId

FROM

ActorsForShortFilms as sfActors

INNERJOIN tblCast AS c ON sfActors.ActorId = c.CastActorID

)

-- finally, which actors appeared in these films

SELECTDISTINCT

a.ActorName

FROM

FilmsByTheseActors AS FinalFilms

INNERJOIN tblCast AS c ON FinalFilms.FilmId = c.CastFilmID

INNERJOIN tblActor AS a ON c.CastActorID = a.ActorID

ORDERBY

a.ActorName ASC

The three CTEs created are:

CTE

What it returns

ShortFilms

Ids of those films which last less than 90 minutes

ActorsForShortFilms

The ids of those actors who appear in these films

FilmsByTheseActors

The ids of those films which feature these actors

Using CTEs declared earlier in SQL

Finally, you can also refer in a query to CTEs which you declared earlier.
We could use this to extend the last query to show the number of rows from each
CTE:

The CTEs returned 3, 7, 11 and 34 rows respectively (we've added a fourth CTE to list the actors appearing in the final set of films).

Here's a query which would produce this:

USE WiseOwlMovies

GO

-- short films

WITH ShortFilms as (

SELECT f.FilmId

FROM tblFilm AS f

WHERE f.FilmRunTimeMinutes < 90

),

-- actors in them,

ActorsForShortFilms as (

SELECTDISTINCT

c.CastActorID AS ActorId

FROM

ShortFilms as sf

INNERJOIN tblCast as c ON sf.FilmID = c.CastFilmID

),

-- films made by these actors

FilmsByTheseActors AS (

SELECTDISTINCT

c.CastFilmId as FilmId

FROM

ActorsForShortFilms as sfActors

INNERJOIN tblCast AS c ON sfActors.ActorId = c.CastActorID

),

-- which actors appeared in these films

ActorsForTheseFilms AS (

SELECTDISTINCT

a.ActorName

FROM

FilmsByTheseActors AS FinalFilms

INNERJOIN tblCast AS c ON FinalFilms.FilmId = c.CastFilmID

INNERJOIN tblActor AS a ON c.CastActorID = a.ActorID

)

-- now summarise the results

SELECT

(SELECTCOUNT(*) FROM ShortFilms) AS'Short films',

(SELECTCOUNT(*) FROM ActorsForShortFilms) AS'Short filma actors',

(SELECTCOUNT(*) FROM FilmsByTheseActors) AS'Films by these actors',

(SELECTCOUNT(*) FROM ActorsForTheseFilms) AS'Actors for these films'

The final SELECT statement in this query references all of
the four CTEs created!

It's worth emphasising at this point that this isn't the most efficient way to solve the above problem,
as is often the case with CTEs. However, it may well be
the most transparent and easiest to understand from a human point of view.