A derived table is essentially a correlated subquery within your FROM clause. If used correctly, a derived table can drastically improve the efficiency of your query.

Imagine that you’re going to be querying a large table with over a million rows in it and 20 columns, but you know your query is only ever going to be using 50,000 of those rows and 3 of the columns. That’s 950,000 rows and 17 columns that have to be scanned and rejected by your query, in order to get the data you need. By using a derived table, you can limit the table reference to those 50,000 rows.

Syntax and Explanation

SELECT P.* 
 
FROM (
      SELECT ProductId,
             ProductName,
             ProductDescription
 
      FROM Products
 
      WHERE ProductCategoryId = 4
     ) AS P 
     JOIN Orders AS O ON O.ProductId = P.ProductId
 
WHERE O.OrderDate >= DATEADD(Y, -1, GETDATE()

The above code will return all products with a ProductCategoryId of 4 from the products table, which have been ordered within the last year.

This will help query performance by cutting down the rows read out from the Products table to begin with. The FROM clause within a SQL statement is actually the first to be executed, then the WHERE. So in the above query, only the 50,000 records have to be joined to the Orders table, rather than the full million rows – only to be rejected later.