Home > Common Table Expressions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > TSQL – Common Table Expressions (CTE) – Part 2 – Simplifying a Query

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:

image image image image image

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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: