564 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 ...
Derived Tables in SQL Part two of a two-part series of blogs |
---|
A derived table is a technique for creating a temporary set of records which can be used within another query in SQL. You can use derived tables to shorten long queries, or even just to break a complex process into logical steps.
This blog is part of our complete SQL tutorial. You can learn more about derived tables and many other techniques on our Advanced SQL training course. |
To demonstrate how derived tables can help to shorten complex queries we're going to generate some statistics about films in our database. The output that we'd like to generate is shown in the diagram below:
We want to categorise our films based on their running time and then find out how many films are in each category.
It is possible to generate the results shown above without having to resort to a derived table, but the resulting query is rather long:
SELECT
CASE
WHEN FilmRunTimeMinutes < 100 THEN 'Short'
WHEN FilmRunTimeMinutes < 150 THEN 'Medium'
WHEN FilmRunTimeMinutes < 200 THEN 'Long'
ELSE 'Epic'
END AS FilmLength
,COUNT(*) AS NumberOfFilms
FROM
tblFilm
GROUP BY
CASE
WHEN FilmRunTimeMinutes < 100 THEN 'Short'
WHEN FilmRunTimeMinutes < 150 THEN 'Medium'
WHEN FilmRunTimeMinutes < 200 THEN 'Long'
ELSE 'Epic'
END
The above code certainly works but it's a little messy having to repeat the CASE statement.
With a derived table we can avoid the need to repeat the CASE statement:
SELECT
FilmLength
,COUNT(*) AS NumberOfFilms
FROM
(
SELECT
CASE
WHEN FilmRunTimeMinutes < 100 THEN 'Short'
WHEN FilmRunTimeMinutes < 150 THEN 'Medium'
WHEN FilmRunTimeMinutes < 200 THEN 'Long'
ELSE 'Epic'
END AS FilmLength
FROM
tblFilm
) AS FilmLengths
GROUP BY
FilmLength
Once again, we've nested one query inside the FROM clause of another. We've enclosed the inner query in parentheses and assigned it the alias of FilmLengths. The results of this inner query are then used in the outer query just as though it was a table which existed in the database.
Derived tables represent one technique for generating a temporary set of records which can be used within another query but there are several others. If you want to learn more about the other techniques you could read about common table expressions, temporary tables, table variables and table-valued functions!
You can learn about all of the above, and much more, on our two-day advanced SQL course, the online version of which you can attend from anywhere in the world..
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.