Summary -

In this topic, we described about the below sections -

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.

Syntax -

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.

Advantages -

  • 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.

Example –

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

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

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 –

emp_id emp_name
001 Employee1
002 Employee2
003 Employee3
004 Employee4
005 Employee5
006 Employee6
007 Employee7
008 Employee8
009 Employee9

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 –

emp_id emp_name designation
001 Employee1 Director
002 Employee2 Director
003 Employee3 Manager
004 Employee4 Manager
005 Employee5 Analyst
006 Employee6 Analyst
007 Employee7 Clerk
008 Employee8 Salesman
009 Employee9 Salesman

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 –

dept_id Totalemployees
1000 5
2000 4