562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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.
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.
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:
To turn this into a derived table we need to nest it within the FROM clause of another query, like so:
) AS MyDerivedTable
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.
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|
25 Aytoun Street