557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
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 (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!
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:
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.
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 |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.