This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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!

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

Parts of this blog
Derived Tables in SQL Server Shortening Complex Queries with Derived Tables (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Shortening Complex Queries with Derived Tables

Getting the Results without a Derived Table

Shortening the Code with a Derived Table

Alternatives to Derived Tables

Head office

London

Manchester