Common Table Expressions (CTEs) - SQL Training
Part two 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
  2. Common Problems with CTEs (this blog)
  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

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.

Common Problems with CTEs

Time and again when using Common Table Expressions I make the same 3 mistakes, so I thought I'd list them here so others might share the pain (and avoid making the same mistakes!).

Problem 1 - End Preceding Line with a Semi-Colon

In SQL, you can choose to end every command with a semi-colon (;), but it's usually optional.  However, for a CTE it's essential:

-- the semi-colon is essential!

USE MOVIES;

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

Without the semi-colon above, you'd get the following error:

Error from CTE

The start of the error message, which is self-explanatory

Problem 2 - Missing Brackets

The brackets aren't optional!

-- get a CTE containing those films

-- which last longer than 3 hours

WITH LongFilms AS

SELECT

FilmName,

The query above is doomed to fail, since you should enclose the SELECT statement in parentheses.

Problem 3 - CTEs are like Mayflies

CTEs don't live long - in fact, just for a single command.  This means that as soon as you've created a CTE you must use it.  The following query would fail:

-- get a CTE containing those films

-- which last longer than 3 hours

WITH LongFilms AS (

SELECT

FilmName,

FilmReleaseDate

FROM

tblFilm

WHERE

FilmRunTimeMinutes > 180

)

-- do something else

PRINT 'This ruins everything!'

-- using this CTE, sort the rows (but it's too late now ...)

SELECT

FilmName,

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

FROM

LongFilms

ORDER BY

FilmReleaseDate

The moral?  Don't be tempted to include any other statements after creating a CTE.

Note that there's a bit more to CTEs than is shown in this blog; you can see extra CTE tips and topics at this recent blog.

  1. CTEs in SQL - Common Table Expressions
  2. Common Problems with CTEs (this blog)
  3. Recursive CTEs
This blog has 0 threads Add post