This page has 0 threads | Add post

Blogs

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

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. Derived Tables in SQL Server (this blog) 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.

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.

Derived Tables in SQL Server (this blog)
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

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.

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!

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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Head office

Kingsmoor House

Railway Street

GLOSSOP

SK13 2AA

London

Landmark Offices

99 Bishopsgate

LONDON

EC2M 3XD

Manchester

Holiday Inn

25 Aytoun Street

MANCHESTER

M1 3AE

Site map

Terms & Conditions

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

Derived Tables in SQL Server

The Basic Syntax of a Derived Table

What's Next?

Head office

London

Manchester