BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- Derived Tables in SQL Server
- Shortening Complex Queries with Derived Tables (this blog)
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.
Posted by Andrew Gould on 08 April 2013
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Shortening Complex Queries with Derived Tables
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.
Getting the Results without a Derived Table
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.
Shortening the Code with a Derived Table
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.
Alternatives to Derived Tables
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!
- Derived Tables in SQL Server
- Shortening Complex Queries with Derived Tables (this blog)