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.

  1. Derived Tables in SQL Server
  2. 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:

Output of query

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!

  1. Derived Tables in SQL Server
  2. Shortening Complex Queries with Derived Tables (this blog)
This blog has 0 threads Add post