This page has 0 threads | Add post

Derived Tables in SQL Server

Written by Andrew Gould

In this tutorial

The Basic Syntax of a Derived Table
What's Next?
Getting the Results without a Derived Table
Shortening the Code with a Derived Table
Alternatives to Derived Tables

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.

The Basic Syntax of a Derived Table

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.

What's Next?

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.

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!

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..

Previous
tutorial

Subqueries

Previous
part

Recursive CTEs

Top of page

Next
part

Next
tutorial

Programming in SQL

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

CTEs and derived tables

Derived Tables in SQL Server

The Basic Syntax of a Derived Table

What's Next?

Getting the Results without a Derived Table

Shortening the Code with a Derived Table

Alternatives to Derived Tables

Head office

London

Manchester