This page has 0 threads | Add post

CTEs in SQL - Common Table Expressions

Written by Andy Brown

In this tutorial

Example of a Common Table Expression
What CTEs Replace - Derived Tables

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.

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

Previous
tutorial

Subqueries

Previous
part

Top of page

Next
part

Common Problems with CTEs

Next
tutorial

Programming in SQL

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

CTEs and derived tables

CTEs in SQL - Common Table Expressions

Example of a Common Table Expression

What CTEs Replace - Derived Tables

Head office

London

Manchester