Derived Tables
Some of the examples from the previous section of this chapter can be solved using subqueries in the FROM clause. Subqueries work as tables in the query, and they are called derived tables. SQL Server considers these subqueries in the same way as ad hoc views, because the definition of the derived tables is merged with the outer query to produce a single query execution plan.
To use a derived table in a query, you must specify in the FROM clause a standard subquery with a table alias for the derived table (see Listing 11.15).
Caution
Correlated queries cannot be used as derived tables.
Code Listing 11.15. You Can Use Derived Tables to Solve Common Queries Without Defining Views or Temporary Tables
|
-- Using a Derived Table to compare UnitPrice
-- with the actual minimum and maximum prices
SELECT P.ProductID, P.UnitPrice
, (MinPrice + MaxPrice) / 2 AS MediumPrice
FROM Products P
JOIN (
SELECT ProductID,
MIN(UnitPrice) as MinPrice,
MAX(UnitPrice) as Maxprice
FROM [Order Details]
GROUP BY ProductID
) AS OD
ON OD.ProductID = P.ProductID
-- Using a Derived Table to produce a discounted price list
SELECT P.ProductID,
Discount * 100 AS Discount,
P.UnitPrice * (1 - Discount) as Price
FROM Products P
CROSS JOIN (
SELECT 0.0 AS Discount
UNION ALL
SELECT 0.10
UNION ALL
SELECT 0.20
UNION ALL
SELECT 0.30
) AS OD
-- Partial result
ProductID UnitPrice MediumPrice
----------- --------------------- ---------------------
23 9.0000 8.1000
46 12.0000 10.8000
69 36.0000 32.4000
77 13.0000 11.7000
31 12.5000 11.2500
15 15.5000 13.9500
62 49.3000 44.3500
38 263.5000 237.1500
-- Partial result
ProductID Discount Price
----------- -------- --------------------------
1 .00 18.000000
1 10.00 16.200000
1 20.00 14.400000
1 30.00 12.600000
2 .00 19.000000
2 10.00 17.100000
2 20.00 15.200000
2 30.00 13.300000
Tip
Derived tables are good candidates to be defined as views. After converted into views, some of them can be indexed, providing extra performance to your reporting queries. You can apply permissions to views but not to derived tables.