Common Table Expression

A ‘Common Table Expression’ or a CTE is a temporary view that is created and used for executing SQL statement and destroyed at the end of execution.  CTE can be used everywhere where an entire SQL statement can be written. CTEs improve the readability of the code and reduces repeated usage of same query within an SQL query.

CTE Syntax:

WITH cte_Name (Column_List) As
(CTE_Definition)
SQL_Statement;

Cte_Name – This would be the CTE name which will be used to refer the same in the desired SQL Statement.
Column_List – This would be the column list from the CTE_Definition. The number of columns defined here should match to what is defined within the CTE_Definition. The column names can be renamed here if required. This is optional.
CTE_Definition – This contains the SQL statement that needs to be defined for the CTE being created.

Let us use the following tables to understand CTEs better.
A student master file/table named STUDMAST.

A subject master file/table which holds minimum marks to pass against each subject, named SUBMAST.

A file/table to hold marks and results that each student has attained against each subject, named STUDMARKS.

A simple CTE over STUDMARKS table to find out the list of students that have failed can be written as follows:

With FAILEDSTUDS As (
        Select Stud_Id, Sub_Id, Marks
        From  DEMO.StudMarks
        Where Results = 'FAIL')
Select * From  FAILEDSTUDS

QUERY RESULT:-

Here, FAILEDSTUDS is the CTE that is defined prior to writing the actual query itself. And the use of CTE makes this SQL look like one of the simplest select queries.

Another sample query to find out the subjects in which students are able to score at least an average mark of 50 can be written as follows:

With MarksAvg As (
        Select Sub_ID, Avg(Marks) As Avg
        From   DEMO.STUDMARKS
        Group By Sub_Id)
Select S.*, A.Avg
From  DEMO.SUBMAST S, MarksAvg A Where S.Sub_Id = A.Sub_Id And A.Avg >= 50

QUERY RESULT:-

Simply, once the CTE is defined, the CTE name itself can be used in the query just like any other database table name and this gives us the freedom to use CTEs in any possible ways like joins, subqueries, etc.

CTEs can also be used with Insert statements

If we have a table with all the minute information about a subject in each record, and user requires a custom information to be extracted out of that data and move into another table, it can also be achieved in one single query.

We will use the same example of the subjects in which students are able to score at least an average mark of 50 to be inserted into another table of same structure as output columns as follows:

Insert into library/File_name

With MarksAvg As (
     Select Sub_ID, Avg(Marks) As Avg
     From DEMO.STUDMARKS
     Group By Sub_Id)
Select S.*, A.Avg
From DEMO.SUBMAST S, MarksAvg A Where S.Sub_Id = A.Sub_Id And A.Avg >= 50