Common Table Expressions (CTEs) - SQL Training
Part one of a three-part series of blogs

This blog gives training on writing CTEs in SQL, or Common Table Expressions to give them their full title. The blog also includes a section on creating recursive CTEs.

  1. CTEs in SQL - Common Table Expressions (this blog)
  2. Common Problems with CTEs in SQL
  3. Recursive CTEs

You can find many more articles like this on our SQL tutorial page.  To get a rounded view of how to program in SQL, have a look at our introductory and advanced SQL training.

Posted by Andy Brown on 20 October 2011 | no comments

CTEs in SQL - Common Table Expressions

Common Table Expressions (also known as CTEs) provided a way to create an ad hoc set of records, and then immediately do something with it.

CTEs were introduced in SQL Server 2005, so if you're still using SQL Server 2000, you can stop reading now!

 Example of a Common Table Expression

To illustrate the use of CTEs, consider the following simple SQL statement:

-- get a list of all films

-- which last longer than 3 hours

SELECT

FilmName,

FilmReleaseDate

FROM

tblFilm

WHERE

FilmRunTimeMinutes > 180

This SQL query might produce the following output:

Results of SQL - a list of films

The results of running the query above - a list of the films in this table lasting more than 3 hours.

 

Suppose now that we wanted to do something additional to this list, such as sort it.  To do this, we could refer to the set of records returned from the query as a common table expression, and sort the records returned from this:

-- get a CTE containing those films

-- which last longer than 3 hours

WITH LongFilms AS (

SELECT

FilmName,

FilmReleaseDate

FROM

tblFilm

WHERE

FilmRunTimeMinutes > 180

)

-- using this CTE, sort the rows

SELECT

FilmName,

CONVERT(char(10),FilmReleaseDate,103) AS 'Released'

FROM

LongFilms

ORDER BY

FilmReleaseDate

The obvious question is - why bother to divide this into two parts, when you could have done the whole thing with a single query?

SELECT

FilmName,

CONVERT(char(10),FilmReleaseDate,103) AS 'Released'

FROM

tblFilm

WHERE

FilmRunTimeMinutes > 180

ORDER BY

FilmReleaseDate

The answer in this case is that there was no point in using the CTE.  In general, however:

  • you can nearly always use a single complex SQL query to avoid using a CTE; but
  • using a CTE will nearly always make a query easier to understand.

Common Table Expressions allow you to divide a query into parts, and hence simplify problems.  While they're rarely essential, I like them because they enable me to get my head round complicated queries.

What CTEs Replace - Derived Tables

It's probably worth briefly mentioning that CTEs are a substitute for derived tables (they work in the same way, but are easier to use).  You could have written the CTE above as follows:

-- show the film name and release date ...

SELECT

FilmName,

CONVERT(char(10),FilmReleaseDate,103) AS 'Released'

FROM (

-- ... of all films lasting more than 3 hours

SELECT

FilmName,

FilmReleaseDate

FROM

tblFilm

WHERE

FilmRunTimeMinutes > 180

) AS LongFilmsDerivedTable

ORDER BY

FilmReleaseDate

I think CTEs are much easier to understand! 

However, if you are going to use CTEs, you need to be aware of three common traps that you can fall into - so it's worth reading the next part of this blog!