So far we've only considered using one CTE in a query, but that's about to change!
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 - but is that all?
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
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
)
SELECT DISTINCT
a.ActorName
FROM
ShortFilms as sf
INNER JOIN tblCast as c ON sf.FilmID = c.CastFilmID
INNER JOIN 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.
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
WITH ShortFilms as (
SELECT
f.FilmId,
f.FilmName
FROM
tblFilm AS f
WHERE
f.FilmRunTimeMinutes < 90
),
ActorsForShortFilms as (
SELECT DISTINCT
c.CastActorID AS ActorId,
a.ActorName
FROM
ShortFilms as sf
INNER JOIN tblCast as c ON sf.FilmID = c.CastFilmID
INNER JOIN tblActor AS a ON c.CastActorID = a.ActorID
)
SELECT DISTINCT
f.FilmName
FROM
ActorsForShortFilms as sfActors
INNER JOIN tblCast AS c ON sfActors.ActorId = c.CastActorID
INNER JOIN tblFilm as f ON f.FilmID = c.CastFilmID
ORDER BY
f.FilmName ASC
This creates:
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
WITH ShortFilms as (
SELECT f.FilmId
FROM tblFilm AS f
WHERE f.FilmRunTimeMinutes < 90
),
ActorsForShortFilms as (
SELECT DISTINCT
c.CastActorID AS ActorId
FROM
ShortFilms as sf
INNER JOIN tblCast as c ON sf.FilmID = c.CastFilmID
),
FilmsByTheseActors AS (
SELECT DISTINCT
c.CastFilmId as FilmId
FROM
ActorsForShortFilms as sfActors
INNER JOIN tblCast AS c ON sfActors.ActorId = c.CastActorID
)
SELECT DISTINCT
a.ActorName
FROM
FilmsByTheseActors AS FinalFilms
INNER JOIN tblCast AS c ON FinalFilms.FilmId = c.CastFilmID
INNER JOIN tblActor AS a ON c.CastActorID = a.ActorID
ORDER BY
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
WITH ShortFilms as (
SELECT f.FilmId
FROM tblFilm AS f
WHERE f.FilmRunTimeMinutes < 90
),
ActorsForShortFilms as (
SELECT DISTINCT
c.CastActorID AS ActorId
FROM
ShortFilms as sf
INNER JOIN tblCast as c ON sf.FilmID = c.CastFilmID
),
FilmsByTheseActors AS (
SELECT DISTINCT
c.CastFilmId as FilmId
FROM
ActorsForShortFilms as sfActors
INNER JOIN tblCast AS c ON sfActors.ActorId = c.CastActorID
),
ActorsForTheseFilms AS (
SELECT DISTINCT
a.ActorName
FROM
FilmsByTheseActors AS FinalFilms
INNER JOIN tblCast AS c ON FinalFilms.FilmId = c.CastFilmID
INNER JOIN tblActor AS a ON c.CastActorID = a.ActorID
)
SELECT
(SELECT COUNT(*) FROM ShortFilms) AS 'Short films',
(SELECT COUNT(*) FROM ActorsForShortFilms) AS 'Short filma actors',
(SELECT COUNT(*) FROM FilmsByTheseActors) AS 'Films by these actors',
(SELECT COUNT(*) 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.
