560 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 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.
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. |
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!).
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:
The start of the error message, which is self-explanatory
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.
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.
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.