What do you mean by Common Table Expression?

Common Table Expressions(CTE) are a new feature in Microsoft SQL Server 2005 that offer a more readable form of the derived table that can be declared once and referenced multiple times in a query.

WITH ProductAndCategoryNamesOverTenDollars (ProductName, CategoryName, UnitPrice)

(
   SELECT    p.ProductName,
                     c.CategoryName,
                     p.UnitPrice
  FROM Products p INNER JOIN Categories c ON c.CategoryID = p.CategoryID
WHERE p.UnitPrice > 10.0
)

SELECT *
FROM ProductAndCategoryNamesOverTenDollars
ORDER BY CategoryName ASC, UnitPrice ASC, ProductName ASC

This query creates a CTE named ProductAndCategoryNamesOverTenRuppes that returns the name, category name, and price of those products whose unit price exceeds 10.00.

In short, a Common Table Expression allows us to define a temporary, view-like construct. We start by (optionally) specifying the columns it returns, then define the query.

What do you mean by Common Table Expression?

1 Answer