Derived Tables in SQL
Part one 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 (this blog)
  2. Shortening Complex Queries with Derived Tables

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.

Derived Tables in SQL Server

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.

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!

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