Common Table Expressions are even better than I'd realised, as this blog shows
CTEs (or Common Table Expressions) allow you to divide complicated queries up into two or more simple discrete tasks, as this blog explains.

Posted by Andy Brown on 01 February 2016

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:

Short 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

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.

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

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

)

-- finally, get the films these actors appeared in

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:

  • 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:

List of final films

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 (

SELECT DISTINCT

c.CastActorID AS ActorId

FROM

ShortFilms as sf

INNER JOIN tblCast as c ON sf.FilmID = c.CastFilmID

),

-- films made by these actors

FilmsByTheseActors AS (

SELECT DISTINCT

c.CastFilmId as FilmId

FROM

ActorsForShortFilms as sfActors

INNER JOIN tblCast AS c ON sfActors.ActorId = c.CastActorID

)

-- finally, which actors appeared in these films

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:

Number of CTE rows

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 (

SELECT DISTINCT

c.CastActorID AS ActorId

FROM

ShortFilms as sf

INNER JOIN tblCast as c ON sf.FilmID = c.CastFilmID

),

-- films made by these actors

FilmsByTheseActors AS (

SELECT DISTINCT

c.CastFilmId as FilmId

FROM

ActorsForShortFilms as sfActors

INNER JOIN tblCast AS c ON sfActors.ActorId = c.CastActorID

),

-- which actors appeared in these films

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

)

-- now summarise the results

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.

This blog has 0 threads Add post