Today, I am going to be starting a series of articles on temporary tables. I will be focusing on derived tables. A derived table is a sub-query that is used to as input to the outer query. An table alias should be used outside the parenthesis to reference the inner query. I think of derived tables as a cross between temporary tables and dynamic views that go away after the query executes.
The first example of a derived table creates the car ownership table for person a. Please see the tiny AUTOS database I created in earlier article. The cool thing about this example is that the data is from in-line VALUES and not from a table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<span style="color: #008000;">-- Database selection does not matter USE model; GO -- Create a derived table SELECT * FROM ( VALUES (1, 'Continental'), (2, 'Edsel'), (3, 'Lincoln'), (4, 'Mercury'), (5, 'Ram') ) AS A (MyId, MyValue); GO </span> |
I will be using the Adventure Works Sample database supplied by Microsoft during the rest of this talk.
A common query pattern is to get the most recent id by date. Joining these results to the original table will bring back the most recent record by date.
The SELECT query below uses the last sales order by customer id as the derived table (inner query). The outer query returns customer name, sales id, sub-total and order date. This might be a query for the SALES team to cold call customers that have not ordered in the past N months.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
<span style="color: #008000;">-- Use the correct database USE AdventureWorks2008R2 GO -- Get customer name, sales id, sub-total and order date SELECT P.FirstName, P.LastName, SH.SalesOrderID, SH.SubTotal, SH.OrderDate FROM Sales.SalesOrderHeader AS SH -- Derived table - last order by customer in 2008 INNER JOIN ( SELECT CustomerID AS MyCustomer, MAX(OrderDate) AS MyDate FROM Sales.SalesOrderHeader WHERE YEAR(OrderDate) = 2008 GROUP BY CustomerID ) AS MC ON SH.CustomerID = MC.MyCustomer AND SH.OrderDate = MC.MyDate INNER JOIN Sales.Customer C ON MC.MyCustomer = C.CustomerID INNER JOIN Person.Person P ON C.PersonID = P.BusinessEntityID ORDER BY SH.OrderDate ASC; GO </span> |
I am going to create a duplicate of the department table from the Adventure Works database. This table will be used in the examples below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
<span style="color: #008000;">-- Remove department staging table IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[STAGE].[Department]') AND type in (N'U') ) DROP TABLE [STAGE].[Department] GO -- Recreate table from AW entries SELECT * INTO [STAGE].[Department] FROM [HumanResources].[Department] GO -- Add a default date ALTER TABLE [STAGE].[Department] ADD CONSTRAINT df_Modified_Date DEFAULT GETDATE() FOR [ModifiedDate]; GO </span> |
Derived tables can be used as the target of an UPDATE statement. The query below updates the two Quality Assurance deparment names with a location ‘US’ as a prefix. We are doing this to get ready for making a set of ASIA departments with the same job functions.
|
1 2 3 4 5 6 7 8 9 10 |
<span style="color: #008000;">-- Update group to US UPDATE T1 SET GroupName = 'US Quality Assurance' FROM ( SELECT * FROM [STAGE].[Department] WHERE GroupName = 'Quality Assurance' ) AS T1; GO </span> |
Derived tables can not be inserted into directly since they are temporary tables. However, they can be used as the source of an INSERT opertion. The query below inserts two new ASIA departments like their US counterparts.
|
1 2 3 4 5 6 7 8 9 10 |
<span style="color: #008000;">-- Insert group for ASIA INSERT INTO [STAGE].[Department] (Name, GroupName) SELECT T1.Name, 'ASIA ' + SUBSTRING(T1.GroupName, 4, LEN(T1.GroupName) - 3) AS GroupName FROM ( SELECT * FROM [STAGE].[Department] WHERE GroupName = 'US Quality Assurance' ) AS T1; GO </span> |
Last but not least, derived tables can be used as the target of a DELETE statement.
|
1 2 3 4 5 6 7 8 9 10 |
<span style="color: #008000;">-- Delete group for US DELETE FROM T1 FROM ( SELECT DepartmentID FROM [STAGE].[Department] WHERE GroupName = 'US Quality Assurance' ) AS T1; GO </span> |
Derived tables are useful when you want to combine the results of several inner SELECT sub-queries into one large result set. I like the syntax for UPDATING and DELETING data using derived tables. One can check the results of the inner query before executing the outer one. Like any good carpenter (crafty dba), check twice and cut once.
I do not like the syntax for a derived table INSERT which increases the amount of typing without adding value. Next time, I will talk about local and global temporary tables.
Like the fresh appearance off the site. I enjoyed this article. Bless you for a excellent blog post.
Simply a smiling visitant here to share the love (:, btw great design .