Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
544 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers 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 ...
Written by Andrew Gould
In this tutorial
A derived table is an example of a subquery that is used in the FROM clause of a SELECT statement to retrieve a set of records. You can use derived tables to break a complex query into separate logical steps and they are often a neat alternative to using temporary tables.
You can learn more about derived tables - and their successors, CTEs or Common Table Expressions - on one of our SQL courses.
To create a derived table you need to start with a valid SELECT statement. Here's an example of a query which we will convert into a derived table:
SELECT
FilmName
,FilmRunTimeMinutes
FROM
tblFilm
To turn this into a derived table we need to nest it within the FROM clause of another query, like so:
SELECT
*
FROM
(
SELECT
FilmName
,FilmRunTimeMinutes
FROM
tblFilm
) AS MyDerivedTable
WHERE
FilmRunTimeMinutes < 100
Notice that the original query must be enclosed in a set of parentheses and that it must also be given an alias - here we have inventively called the derived table MyDerivedTable.
The example we've given above isn't particularly useful other than to demonstrate the basic syntax of a derived table. The next part of this series will cover a more complex example to show how derived tables can actually be useful!
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..
You can learn more about this topic on the following Wise Owl courses:
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 2024. All Rights Reserved.