SQL Tips by Namwar Rizvi

May 3, 2007

Simplify your Hierarichal Query…Use Common Table Expressions CTE

Filed under: Uncategorized — namwar @ 9:06 PM

Displaying Manager with Employee in a single row will 90% of the time results in query with correlated sub-query.
A typical query which display Employee Title,Manager Title and Title of Manager of Manager is like this

Select e.EmployeeId,e.ManagerId,m.ManagerId as ManagerOfManagerId, e.Title ,m.Title as ManagerTitle,mm.Title as ManagerOfManageTitle
from
HumanResources.Employee e
Inner Join
(Select EmployeeId,ManagerId,Title from HumanResources.Employee) m On e.ManagerId=m.EmployeeId
Inner Join
(Select EmployeeId,ManagerId,Title from HumanResources.Employee) mm On m.ManagerId=mm.EmployeeId

Most of us feel difficult to understand this query in first sight because of correlated sub-queries int and maintenance of these type of queries is also an issue.

But don’t worry, Microsoft has come up with an excellent new construct called Common Table Expression or CTE. This construct lets you define queries separately like sort of virtual variables. Once defined, you can use these table name anywhere in your query in place of sub-query which will make your query more understandable and easier to maintain.
Above mentioned query can be re-written as follows

—Define Virtual Table
With ManagerTable (EmployeeId,ManagerId,Title)
As
(
Select EmployeeId,ManagerId,Title from HumanResources.Employee
)
—Use the above virtual table, instead of sub query
Select e.EmployeeId,e.ManagerId,m.ManagerId as ManagerOfManagerId,e.Title ,m.Title as ManagerTitle,mm.Title as ManagerOfManageTitle
from
HumanResources.Employee e
Inner Join
ManagerTable m On e.ManagerId=m.EmployeeId
Inner Join
ManagerTable mm On m.ManagerId=mm.EmployeeId

Although it is a very simple example but you can see yourself how much readability is increased and now even a junior SQL programmer can easily understand and maintain this.
For detailed understanding of Common Table Expressions (CTE) you can visit
http://www.4guysfromrolla.com/webtech/071906-1.shtml

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

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

Create a free website or blog at WordPress.com.

%d bloggers like this: