Guide to Common Table Expressions CTEs

Definition:

A common table expression or CTE is the temporary named result set created from a simple SELECT statement that can be used in subsequent SELECT statements.

With the help of CTE, You can Divide large query into small queries. Readability and maintenance will be better after using CTE.

CTE which is a Common Table Expression is the temporary name result set.

So, it’s basically a temporary result set that you can use in SELECT, INSERT, UPDATE, and DELETE.

Add WITH clause before SELECT, INSERT, UPDATE, DELETE, MERGE statement.

Purpose : The purpose of using CTE is to Divide and Conquer.

When to use CTE :

Whenever It comes to query optimization CTE plays very important role in it. Suppose you write a query and its very long & complex. You used this query in view n that too is complex. So in that case CTE can help you.

You can create multiple temporary result set, whenever you need you can join both of them to create new set and you can refer that temporary result set as per need.

Basic Syntax : 

 WITH CTE_NAME (column_name) AS (query)

SELECT * FROM CTE_NAME;

Whenever a query gets complicated, you can use CTE. It will be more easier to execute the query.

CTE must be followed by a single  SELECT, INSERT, UPDATE, DELETE.

Significant points :

• Readability :

you can divide complex queries into small queries.

create some of CTE’s than writing one large query or multiple subqueries, which can be combined later if needed.

• Ranking :

When you want to use ranking function i.e. Row_number(), Rank(), ntile(),etc.

• Substitute for View :

You can use CTE instead of View function. If view function query is complex.

• Recursion : The recursive query call itself till the query satisfied the condition.

• Limitations :

To overcome constraints of SELECT statement like referencing itself.

Types of CTE :

1.   Non-Recursive CTE’s :

Don’t reference themselves. Don’t use recursion. You can create single CTE or multiple CTE. So temporary real set can be created on which you can apply update, delete, select

2.   Recursive CTE :

The recursive query call itself till the query satisfied the condition. The CTEs which repeated again and again with reference to itself based on conditions. It repeats till its not satisfied with condition.

Syntax :

 WITH
   CTE_Name1 (column_name) AS (query),
   CTE_Name2 (column_name) AS (query)
SELECT * FROM CTE_Name1
UNION ALL
SELECT * FROM CTE_Name2;
 

When you make any results in SQL, You write a query and store it in temporary table which is actually not existed anywhere, its just temporary results. So that temporary result table gets stored in CTE. The WITH clause can include one or multiple CTEs by commas.

Example :

Let's take one example by creating some Employees table.

Let's create a table named as Employees database using CREATE TABLE statement as shown below −

Syntax for creating a table :  

 CREATE TABLE Employees (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   EXPERIENCE INT NOT NULL,
  CITY CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

Now, Insert some records into the above created table Like Name of the employee, how many years experience of employees , salary, etc.

INSERT INTO Employees VALUES
(1, 'Robert', 3, 'Albany', 5000.00 ),
(2, 'Kenneth', 5, 'New Rochelle', 6000.00 ),
(3, 'Paul', 3, 'Rochester', 6000.00 ),
(4, 'Chris', 10, 'Secaucus', 16000.00 ),
(5, 'Harry', 7, 'Harrison', 8500.00 ),
(6, 'George', 6, 'Niagara Falls', 8000.00 ),
(7, 'Murphy', 8, 'Syracuse', 10000.00 );

The table created is as shown below − 

Here, we are creating a Common Table Expression (CTE) named  Employee_Exp that selects all  Employees with an experience of 3.

We are then retrieving the ID, NAME, and AGE of these customers from the CTE.

Syntax :    

  WITH Employee_Exp1 AS(
SELECT * FROM  Employees WHERE EXPERIENCE = 3),
SELECT ID, NAME, EXPERIENCE FROM Employee_Exp1; 

Output :

Here, we are creating one more Common Table Expression (CTE) named  Employee_Experience that selects all  Employees with an experience of 10.

We are then retrieving the ID, NAME, and AGE of these customers from the CTE.

Syntax :

  with Employee_Exp2 AS (
SELECT * FROM  Employees WHERE EXPERIENCE = 10)
SELECT ID, NAME, EXPERIENCE FROM Employee_Exp2

Ouput :

Here, we are explaining two CTEs namely   ‘Employee_Exp1’ and  ‘Employee_Exp1’ to separate out Employees based on their Experiences. Then, using the UNION ALL operator to combine the both results of CTEs into a single result set, retrieving information from both.

Syntax :

WITH
          Employee_Exp1 AS (
SELECT * FROM  Employees WHERE EXPERIENCE = 3),
         Employee_Exp2 AS (
SELECT * FROM  Employees WHERE EXPERIENCE = 10)
SELECT * FROM Employee_Exp1
UNION ALL
SELECT * FROM Employee_Exp2;

Output :

Recursive :

Now, we are using a Recursive CTE named recursive_cust to retrieve data from the 'Employees' table created above. Firstly, we are going to select Employees with salaries above 10000 and then recursively joining Employees having experience more than 5 to the result set using the UNION ALL operator.

Syntax :

WITH recursive_cust (ID, NAME, Experience, city,salary) AS
(
   SELECT ID, NAME, Experience, city, salary
   FROM Employees
   WHERE SALARY > 10000
 UNION ALL
   SELECT ID, NAME, Experience, city, salary
   FROM Employees
   WHERE Experience > 5
)
SELECT * FROM recursive_cust;

Output :

When the above query is executed, all data from the employees table whose Experience is greater than 5 or salary is greater than 10000 will be displayed recursively as shown in above output table.

Conclusion :

CTEs are defined using WITH keyword and It can be referenced several times in a query. In real-time scenarios, More than one tables can join to form a single data model. Achieved results can be referred to several times. Using CTEs is very comfortable as it can make queries easier to write and more readable. You can organize long query in a better way.

References :

https://www.tutorialspoint.com/sql

https://learnsql.com/blog