Multiple CTEs in a Single Query – Sql Server

This is the third article in the series of articles on Common Table Expression in Sql Server. Below are the other articles in this series:

Introduction to Common Table Expression (a.k.a CTE)
Recursive CTE
Nested Common Table Expressions

Multiple CTEs in a Single Query

Many a times we come across a scenario, where we need to use multiple CTEs in a single query. For instance assume first CTE gives employee details and second CTE gives total salary paid to the employee year to date after performing all the aggregation of the salary paid from the beginning of the year to till date. So, here combining the first CTE with the Second CTE in a single query gives a meaning ful details of employee with his year to date salary.

Below is very basic example of using multiple CTEs in a single query:

Multiple CTES in a Single query Ver2

WITH FirstCTE AS(SELECT 1 EmployeeId,'Shreeganesh Biradar' Name)
	, SecondCTE AS (SELECT 1 EmployeeId, '$ 100000' YTDSalary)
SELECT FC.EmployeeId, FC.Name, SC.YTDSalary
		ON FC.EmployeeId = SC.EmployeeId

Multiple CTES in a Single query Result Ver2

6 thoughts on “Multiple CTEs in a Single Query – Sql Server

  1. Dear Sir,

    Could you please let me know about OPTION clause?

    – what will happen without OPTION clause
    – what is the use of OPTION clause

    Thanks in advance

    Pradeep SB.

Leave a Reply to Pradeep SB Cancel reply

Your email address will not be published. Required fields are marked *