SQL Capabilities - Common Table Expression (CTE)

This is the first of many blog posts of I am writing based on the interesting features of various technologies I have discovered in the recent times and especially during my internship.

The technologies I have been working on during my internship are, eclipse with BIRT capabilities at the front end and sql server at the back end.

I have been working with SQL server since a couple of years now and I am always finding some new (and more efficient) way of writing a particular query.

Just this morning I was working on a few queries (parent query) which required a single query (child query) to be called multiple times at different points of the parent query. I was initially approaching this to be one of the usual copy – paste jobs bound together by a “union” clause. But then I felt that there would probably be a more efficient way of approaching this task. I did some research and wasn’t very surprised on finding a sql capability to handle this task in a very efficient manner.

This sql capability is called “Common Table Expression”.  It basically allows the user to define the query which is to be repeated, once in the beginning of the parent query. Once this is done, the query data can be utilized at any point within the parent query.

Common Table Expression Syntax
A Common Table Expression contains three core parts:

• The CTE name (this is what follows the WITH keyword)
• The column list (optional)
• The query (appears within parentheses after the AS keyword)

Example:

;with sales as (

SELECT TOP 10 CompanyID, CompanyName, SUM(GrossSales) [GrossSales]

FROM CompanySales

GROUP BY CompanyID, CompanyName

ORDER BY SUM(GrossSales) desc

)

select * from sales

union

SELECT -1, 'Other' [CompanyName], SUM(GrossSales) [GrossSales]

FROM CompanySales

WHERE CompanyID NOT IN(

                    select companyid from sales

)

So as it is seen above, the output was required to display the TOP 10 values from the CompanySales table and group the remaining values in the “Other” category. If this is approached with the conventional copy – paste method it would require the first query to be repeated multiple times at different points within the parent query. The CTE capability eliminated the unnecessary repetition and certainly enhanced the query performance.