Home > Common Table Expressions, SQLServer, SQLServer 2005, SQLServer 2008, SQLServer 2008 R2 > TSQL – Common Table Expressions (CTE) – Part 0 – Introduction

TSQL – Common Table Expressions (CTE) – Part 0 – Introduction

What is it? A CTE Creates a temporary named result set. It is available within the execution of a single DML statement. A Common Table Expression can be used to:

1. Create Recursive Query
2. Create a temporary view, to simplify another query
3. To avoid “Msg 164, Level 15, State 1” – group by a derived column / non deterministic
   
function
4. And, Use the result set returned multiple time in the same statement.

Let’s see how to create one,
I have created two tables for this example – FirstNames, LastNames as below:

Data in FirstNames and LastNames :
image image

To combine the firstname and lastname in output, we can use INNER JOIN:

SELECT FN.EmpID, FN.FirstName, LN.LastName
FROM dbo.FirstNames FN
INNER JOIN dbo.LastNames LN ON LN.EmpID = FN.EmpID

To write the same using CTE, let’s look at the syntax first:

WITH TempResultSetName(Column1, Column2, …)
AS
( 
      SELECT Query
)
SELECT Column1, Column2, FROM TempResultSetName

The “WITH TempResultSetName” defines a name for the result set, and the Column List defines the column names for the result set – this is optional, if column names are not specified it will used the names from the “SELECT Query”.

The “SELECT Query” is the actual data for the results. i.e. which returns actual rows.

The Final part of CTE is the “SELECT Column1, ….. FROM TempResultSetName” – this simply outputs the result set to user. you are not limited to only SELECT here, any valid DML can be used.

However, you can only use the TempResultSet in FROM clause of a SELECT statement, i.e. you can not INSERT INTO or UPDATE or DELETE to/from TempResultSet.

Now, let’s write the actual CTE to replace INNER JOIN:

WITH TempResultSetName(EmpID, FirstName, LastName)
AS
( 
      SELECT FN.EmpID, FN.FirstName, LN.LastName
      FROM dbo.FirstNames FN
      INNER JOIN dbo.LastNames LN ON LN.EmpID = FN.EmpID
)     SELECT EmpID, FirstName, LastName
      FROM TempResultSetName

Line 1 – Defines the Result Set and Columns in Result Set
Line 4 to 6 – Populates the Result Set
Line 8 & 9 – Outputs the Result Set – The output can also include other tables, I have added examples in the sample script.

Download the scripts used in post 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: