Common Table Expressions (CTE)

What is Common Table Expressions(CTE)

A CTE is similar to a derived table that is not stored as an object and lasts only for the duration of the query. It is basically useful for creating a recursive query where in SQL 2000 we need to write multiple lines of code for finding the Manager/Employee Hierarchy and the same can be achieved optimally from SQL 2005 using CTE.

Below mentioned is an coding example for the same.


/* Table which had Employee and Their Manager Names */
CREATE TABLE EMP
( EMP_ID varchar(10),
EMP_NAME varchar(50),
MGR_ID varchar(10)
)

/* Sample Data to be inserted */

/* Insert CEO Data who doesnt report to anyone and is the boss */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0001','PAUL - CEO',NULL

/* Insert President Data who reports to CEO */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0002','SAM - PRES','E0001'

/* Insert VP Data who reports to President */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0003','MIKE - VP','E0002'

/* Insert another VP Data who reporting is not finalised yet and is so null */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0004','ROB - VP',NULL

/* Insert Director data who is reporting to new VP who doesnt have reporting */
INSERT INTO EMP (EMP_ID,EMP_NAME,MGR_ID)
SELECT 'E0005','NATE - DIRECTOR','E0004'


/* Usage of Common Table Experessions(CTE)
This lists the Employee with their respective
managers and the levels and the Root Manager IDs */
WITH EMP_HIER(EMP_ID, EMP_NAME, MGR_ID, LEVELS, ROOT_MGR_ID)
AS
(
SELECT EMP_ID, EMP_NAME, MGR_ID, 0, EMP_ID
FROM EMP
WHERE MGR_ID IS NULL
UNION ALL
SELECT e.EMP_ID, e.EMP_NAME, e.MGR_ID, LEVELS + 1, eh.ROOT_MGR_ID
FROM EMP e JOIN EMP_HIER eh
ON eh.EMP_ID = e.MGR_ID
)
SELECT * FROM EMP_HIER


Output

EMP_ID EMP_NAME MGR_ID LEVELS ROOT_MGR_ID
E0001 PAUL - CEO NULL 0 E0001
E0004 ROB - VP NULL 0 E0004
E0005 NATE - DIRECTOR E0004 1 E0004
E0002 SAM - PRES E0001 1 E0001
E0003 MIKE - VP E0002 2 E0001