Common Table Expression
(CTE) is a SQL syntax that uses in MS SQL, Teradata and other databases. This
post will include why will we use CTE? and SQL syntax of CTE.
By using
CTE; complex query can be written as simple readable and maintainable way. It’s something like temporary result set
but not exactly temporary/volatile table. You can avoid having volatile table
if you use CTE. CTE is like dynamic view, however views meta data is stored in
the database but CTE doesn't require that.
A recursive
CTE can simplify the code required to run a recursive query within a SELECT,
INSERT, UPDATE, DELETE, or CREATE VIEW statement. For
example, in an organization chart you need display employee’s positions in the
organization or e.g. Showing product hierarchies, you need to find out
product segment, and then Product template, eventually the product. Above both
cases, CTE can be helpful to find out the hierarchical chart.
A simple CTE query will look like below:
with CTE_PRODUCT_X_CUSTOMER (PD_ID,PROD_NAME,CUSTOMER_ID,CUSTOMER_NAME,SALES_DATE)
AS
(
Select P.PD_ID as PD_ID, P.PROD_NAME as PROD_NAME,
C.Customer_ID as CUSTOMER_ID, C.CUSTOMER_NAME as CUSTOMER_NAME, S.Sales_date as
SALES_DATE
From Sales as S
JOIN
From Product as P on
S.PD_ID=P.PD_ID
JOIN Customer as C
ON S.CST_ID=P.CST_ID
)
select * from CTE_PRODUCT_X_CUSTOMER
Above example deliver sales information of particular
customer. of course, this can be done without having CTE, a simple join or view
can achieve same output. However, the above example can introduce you CTE syntax.
Now, Lets look at an example where CTE can avoid
volatile/temporary table. e.g. you need to update Manufacture_id from your
PRODUCT_SALES table. And to update the column Manufacture_id, you are depend on
Product_ID matches from other sub queries. The example can go like this:
create volatile table TMP
as
(
Select K.PD_ID as PD_ID, P.PROD_NAME as PROD_NAME,
Ck.Customer_ID as customer_id, P.EFF_DT as EFF_DT
From Product_STG as P
join Product_KEY as k
on p.PRD_CODE=k.PRD_CODE
and p.EFF_DT> '2016-01-29'
JOIN Customer_key as CK
on CK.SRC_ID=P.Source_Id
and p.eff_dt>'2016-01-29'
) with data
on commit preserve rows;
update PRODUCT_SALES
set Manf_id=(select customer_id from TMP
where
PRODUCT_SALES.PD_ID=TMP.PD_ID
and
PRODUCT_SALES.EFF_DT=TMP.EFF_DT)
Above example required to create volatile table. Now we can
see how we can avoid using volatile table; instead, lets use CTE syntax.
with CTE_TEMP (PD_ID,PROD_NAME,customer_id,EFF_DT)
AS
(
Select K.PD_ID as PD_ID, P.PROD_NAME as PROD_NAME,
Ck.Customer_ID as customer_id, P.EFF_DT as EFF_DT
From Product_STG as P
join Product_KEY as k
on p.PRD_CODE=k.PRD_CODE
and p.EFF_DT> '2016-01-29'
JOIN Customer_key as CK
on CK.SRC_ID=P.Source_Id
and p.eff_dt>'2016-01-29'
)
update PRODUCT_SALES
set Manf_id=(select customer_id from CTE_TEMP
where
PRODUCT_SALES.PD_ID=CTE_TEMP.PD_ID
and
PRODUCT_SALES.EFF_DT=CTE_TEMP.EFF_DT)
As
you see, you can avoid creating temporary table by using CTE. Please remember,
you can’t create view on top of CTE.
