Common Table Expressions (CTEs): CTE Example

Read Time:3 Minute, 6 Second

A Common Table Expression (CTE) is the result set of a query which exists temporarily and use within the context of a larger query. Much like a derived table, the result of a CTE is not stored and exists only for the duration of the query. This article will focus on non-recurrsive CTEs.

Notice that when we define the CTE we give the result a name as well its columns. In this way a CTE acts like a VIEW. The result and columns naming is different. This allows you to encapsulate complicated query logic with the common table expression.

Now going back to the CTE, notice that the WITH statement. There you’ll see the name and columns defined. These columns correspond to the columns returned from the inner query.

Syntax:

A CTE allows you to define a temporary named result set that available temporarily in the execution scope
of a statement such as SELECT, INSERT, UPDATE, DELETE, or MERGE sql.

WITH CTE_NAME(column_1, column_2, column_3)
AS
(--normal SQl query
SELECT *
FROM table)

In the code above, the Common Table Expression must start with a WITH. This tells your query that it is a CTE statement.
Next, CTE_NAME is the name you want to call it. You can name it anything, but since this is generally
used for a reference later, it should make sense. The column_1, column_2, & column_3 are the aliasing column names you want in your query. Finally, the AS, is the start of the SQL statement, which I labeled as normal SQL query as a note reference.

Comes into the relevance, if you plan on calling the same query over and over again.

WITH Table1 (col1)
AS
(select col1 from tbl)
 )
,Table2(col2)
AS
(select col2 from tbl)
,Table3 (Col3)
AS
(select col3 from tabl) a
)
Select m.Conference_id,me.NumberMealEaters, me.AttendeeType, e.expense_date, 
e.RecordIdentifier,amount
from Table1 m
join Table2 me on m. col1 = me. Col2join Table3 e on e. col3 = m.col1

INSERT into siebel.EIM_GROUP
(ROW_ID,IF_ROW_BATCH_NUM,IF_ROW_STAT,PARTY_TYPE_CD,PARTY_UID,GRP
_BU,ORG_NAME,ORGASGNUSREXCLDFLG,PSTN_POSTN_BU,PSTN_ROW_STATUS,PSTN_PO
STN_DIVN)
with date_ranges as
(select max(lr1.last_start_run) max_last_start_run, max(lr1.start_run) max_start_run,
max(lr1.last_run) max_last_run, max(lr1.this_run) max_this_run 
 from siebel.last_run lr1 where lr1.run_type = 'EIM')
select 
cols
from 
siebel.table, 
date_ranges dr, 
siebel.eim_control_numbers ecn_row_id, 
siebel.eim_control_numbers ecn_if_batch_num 
where 
ecn_row_id.EIM_TABLE_NAME = 'EIM_GROUP' 
ecn_if_batch_num.EIM_UPDATE_TYPE = (
case when table.expiration > dr.max_last_run then 'EXPIRE' 
when table.effective > dr.max_last_run then 'ADD'
when table.last_changed > dr.max_last_run then 'UPDATE'
end)

I want to point out that you can define more than one CTE within a WITH statement. This can help you simplify some very complicated queries which are ultimately joined together. Each complicated piece can include in their own CTE which is then referred to and joined outside the WITH clause.

Here is an example using of TWO CTE’s, it’s a simple example, but it shows how two CTE’s are defined, and then used in an INNER JOIN

WITH   PersonCTE (BusinessEntityID, FirstName, LastName)
AS     (SELECT Person.BusinessEntityID,
               FirstName,
               LastName
        FROM   Person.Person
        WHERE  LastName LIKE 'C%'),
PhoneCTE (BusinessEntityID, PhoneNumber)
AS     (SELECT BusinessEntityID,
               PhoneNumber
        FROM   Person.PersonPhone)
SELECT FirstName,
       LastName,
       PhoneNumber
FROM   PersonCTE
INNER JOIN
PhoneCTE
ON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;