TSQL – Common Table Expressions (CTE) – Part 2 – Simplifying a Query
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
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
