BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- CTEs in SQL - Common Table Expressions (this blog)
- Common Problems with CTEs
- 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.
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:
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!
- CTEs in SQL - Common Table Expressions (this blog)
- Common Problems with CTEs
- Recursive CTEs