CTE stands for OMMON TABLE EXPRESSION. CTE defined a temporary named result set which we can use in the statement like SELECT, INSERT, UPDATE, DELETE and MERGE. CTE was defined by using WITH operator. CTE works on SQL SERVER 2005 onwards. we can define multiple CTE's in single WITH operator by separating each CTE by comma.

Types of CTE

CTE divided into two categories are explained as follows -

• Recursive CTE –
  Recursive CTE is that which itself references in that CTE, at some point CTE has an end condition until that it executes repeatedly. We can use Recursive CTE on hierarchical data it executes continuously until it returns whole hierarchical data.
• Non-Recursive CTE –
  Non-Recursive CTE as the name states that it won't use recursion. In simple words Non-Recursive CTE does not calls itself with in the same CTE.

WITH expression_name ( column1, column2,…)
AS
CTE definition
SQL statement; 

• expression_name – It represents the name of the CTE that we want to create.
• column1, column2,… - It represents the name of the columns that we want to create on CTE.

• CTE increases readability by creating multiple CTE'S we can use all CTE'S data in one SELECT statement.
• CTE acts as VIEW if we don't have permission to create VIEW, simply by using CTE we can get our required data.
• CTE does not need storage space for result.
• CTE provides RECURSIVE queries.
• CTE makes code maintenance easy.

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Creating CTE on employee_details table.

Requirement - Creating emp CTE on employee_details table. The query was as follows –

WITH emp(emp_id,emp_name) AS (SELECT emp_id,emp_name
FROM employee_details)
SELECT emp_id, emp_name FROM empcte; 

By executing above query we can get emp_id, emp_name details from emp as shown in below –

Scenario – Creating multiple CTE's on employee_details table in single WITH operator.

Requirement - Creating emp, sales CTE's on employee_details table in single WITH operator. The query was as follows –

WITH emp(emp_id,emp_name) AS (SELECT emp_id,emp_name
FROM employee_details), sales(emp_id,designation) AS (SELECT emp_id,
designation FROM employee_details)
SELECT emp_name, designation FROM emp INNER JOIN sales ON
emp.emp_id = sales.emp_id; 

By executing above query we can get emp_name, designation details from emp and sales as shown in below –

Scenario – Creating CTE without column names on table.

Requirement - Creating employees CTE without column names on employee_details table. The query was as follows –

WITH employees (SELECT dept_id, COUNT(*) AS totalemployees FROM 
employee_details); 

By executing above query, we can get total employees in all departments present in employee_details by using cte. The output was as follows –