Sql And Me

Another use of CTE can be to simply a query which joins a lot of tables. we can divide he number of joins between the CTE and the query. Hence, making it more readable and easier to understand.

For this example, I have create five different tables for Product Name, Category, Description, Model and Price. each table contains a primary key Product ID.

Sample data is used as below:

Now, to return all columns in a single Result set we can write a query:

SELECT      P.ProductID, P.ProductName, PC.Category,
            PD.Description, PM.Model, PP.Price
FROM        dbo.Product P
INNER JOIN  dbo.ProductCategory PC
            ON PC.ProductID = P.ProductID
INNER JOIN  dbo.ProductDescription PD
            ON PD.ProductID = P.ProductID
INNER JOIN  dbo.ProductModel PM
            ON PM.ProductID = P.ProductID
INNER JOIN  dbo.ProductPrice PP
            ON PP.ProductID = P.ProductID

This works perfectly, the only problem here is number of JOINS required. And as the number of tables increase it would get more messier!

A CTE can be put to use to steal some joins from the query. for our example, we can perform two JOINs inside a CTE, and two JOINs in the outer query. Thus, making it more readable.

It can be converted to:

;WITH ProductDetails(ProductID, Category, Description, Model)
AS
( 
      — JOINs used in CTE = 2
      SELECT PC.ProductID, PC.Category, PD.Description, PM.Model
      FROM dbo.ProductCategory PC
      INNER JOIN dbo.ProductDescription PD ON PD.ProductID = PC.ProductID
      INNER JOIN dbo.ProductModel PM ON PM.ProductID = PC.ProductID
)
— JOINS used in outer query = 2
      SELECT PD.ProductID, P.ProductName, PD.Category, PD.Description, 
      PD.Model, PP.Price
      FROM ProductDetails PD
      INNER JOIN dbo.ProductPrice PP ON PP.ProductID = PD.ProductID
      INNER JOIN dbo.Product P ON P.ProductID = PD.ProductID

looks good, doesn’t it?

You can download the scripts used in this example here.

Hope This Helps!

Vishal