The Real Use of SQL Common Table Expression (CTE)

Here’re top advantages of Common Table Expression(CTE) You can identify CTE by looking at ‘WITH’ clause. More on full-select. In simple terms, it looks like a Sub-query.

SQL CTE Best Example

WITH DEPTOTAL (WORKDEPT, MAXSALARY) AS
(SELECT WORKDEPT, SUM(SALARY+COMM)
  FROM DSN8910.EMP
  GROUP BY WORKDEPT)
SELECT WORKDEPT FROM DEPTOTAL
WHERE MAXSALARY =
  (SELECT MAX(MAXSALARY)
          FROM DEPTOTAL);

Here, the derived value is ‘WORKDEPT’ from the WITH table ‘DEPTOTAL‘.

How SQL CTE Works

Step-1

Get MAXSALARY.

Step-2

Apply expression SUM(SALARY+COMM), and select the rows.

Step-3

You will get desired Result.

References