CTE Query in SQL Server

Introduction

Common Table Expressions (CTE) query is very useful to reduce query length as well complexity.

Generally we are using Joins for retrieving records from multiple tables, It is difficult and complex little bit than CTE queries.

A common table expression (CTE) is a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. CTE query returns us a single query from multiple joins query so we can easily deal with it and get more expected result in less effort.

Using the code

It is very easy to write CTE query.

A Common Table Expression defines with three components.

1. CTE name after WITH phrase
2. Column list (It is not mandatory)
3. Final query (appears within parentheses after the AS keyword)

Let see Format of CTE query

WITH CTEname(Columnlist optional) AS(..sql query..)
SELECT * FROM CTE name

it returns all columns of sql query which has been written in parenthesis..

e.g.

WITH SampleData AS(SELECT e1.column2 AS EmpName,e2.column2 AS Department FROM table1 e1 INNER JOIN table2 e2 ON e1.Column3=e2.column1) 
select * from SampleData  

it returns two columns having name EmpName and Department and it act like single and simple query.

We can use multiple CTE query as well

with samp as
(select 'Nirav' as Text1 ,'Prabtani' as Text2)
    ,samp1 as(
            select * from samp
            union
            select 'Code' as Text1 ,'project' as Text2
         )
select * from samp1  

we can define column name as well like this.

WITH CTEname (columnlist) as (....) 
 with samp as

(select 'Nirav' as Text1 ,'Prabtani' as Text2)

    ,samp1(MyColumn1,MyColumn2) as(
            select * from samp
            union
            select 'Code' as Text1 ,'project' as Text2
         )
select * from samp1  

This query returns output of both queries.. 

Points of Interest

I have retrieved dates between date interval from first date to second date like this..

 WITH CTEQuery AS (
			  SELECT CAST('23 Mar 2014' AS DATETIME) AS dt
			  UNION ALL
			  SELECT DATEADD(dd, 1, dt)
			   FROM CTEQuery s
			   WHERE DATEADD(dd, 1, dt) <= CAST('26 Jun 2014' AS DATETIME)
			   )
select * from CTEQuery 

It returns all the date row wise between 23 Mar 2014 to 26 Jun 2014.... :)

History

• 20 Jan 2014, initial level..