CTE

CTE stands for Common Table Expression. It is a named temporary result set defined with the WITH clause at the start of a SELECT, INSERT, UPDATE, or DELETE statement.

What is a CTE

• It is written once with WITH name AS (subquery) and then used like a table in the main query.
• It makes long nested subqueries easier to read because each step gets a clear name.
• The same CTE can be referenced multiple times in one statement without repeating the subquery.
• It exists only for the duration of the statement and is not stored in the database.

Example: simple CTE

• Example that finds departments whose average salary is above 40000:-
  WITH DEPT_AVG (DEPTNO, AVG_SAL) AS (SELECT WORKDEPT, AVG(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT) SELECT DEPTNO, AVG_SAL FROM DEPT_AVG WHERE AVG_SAL > 40000 ORDER BY AVG_SAL DESC;

Recursive CTE

• A recursive CTE references itself and is used for hierarchies like manager-employee chains or bills of material.
• It has two parts joined by UNION ALL: the anchor (starting rows) and the recursive step (next level).
• Example that lists an employee and all levels below him:-
  WITH EMP_HIER (EMPNO, NAME, MGRNO, LVL) AS (SELECT EMPNO, FIRSTNME, MGRNO, 1 FROM EMPLOYEE WHERE EMPNO = '000010' UNION ALL SELECT E.EMPNO, E.FIRSTNME, E.MGRNO, H.LVL + 1 FROM EMPLOYEE E, EMP_HIER H WHERE E.MGRNO = H.EMPNO) SELECT EMPNO, NAME, LVL FROM EMP_HIER ORDER BY LVL;
• Always test recursive CTEs on small data first, because a bad join condition can loop until DB2 stops it.