1. Derived tables
Derived tables are another name for a query acting as a table and are commonly used to do aggregations in T-SQL. Let's take a look at how to create them.
2. What are Derived tables?
When is a query not a query? When it is a derived table. You can use derived tables when you want to break down a complex query into smaller steps. Derived tables are listed in the FROM section of the query like tables and are a great solution if you want to create intermediate calculations that need to be used in a larger query. Let's build a derived table in T-SQL.
3. Derived tables in T-SQL
This query will return all the rows in the Kidney table for people who are the average age. The derived table is after the join and can include any tables in the database. Here the query will compute a single value, meaning the derived table will only contain one value, the average age in the data. The derived table is joined to the Kidney table, which means the only records returned will be for records of people who are the average age.
Notice how both the tables are aliased not just the derived table? It is common practice to do this so you don't have to type the table name every time you need to reference values in that table.
4. Let's practice!
Now let's try some examples using Derived Queries.