This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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!

Parts of this blog
CTEs in SQL - Common Table Expressions (this blog) Common Problems with CTEs Recursive CTEs

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

CTEs in SQL - Common Table Expressions

Example of a Common Table Expression

What CTEs Replace - Derived Tables

Head office

London

Manchester