Understanding SQL CTE (Common Table Expression)

Dashrath Hapani
18h
131
0
2

Article

A Common Table Expression (CTE) in SQL is a temporary result set that simplifies complex queries, improves readability, and enhances performance.

Why Use CTE?

Makes queries more readable.
Helps in breaking down complex queries into manageable parts.
Improves recursive query handling.
Enhances performance in some cases.

Basic Syntax of CTE

WITH cte_name (column1, column2, ...) AS
(
       SELECT column1,
              column2,
              ...
       FROM   tablename )
SELECT *
FROM   cte_name;

Example of a Simple CTE

WITH employeecte
     AS (SELECT employeeid,
                NAME,
                salary
         FROM   employees
         WHERE  salary > 50000)
SELECT *
FROM   employeecte;

Recursive CTE Example

WITH recursivecte (id, NAME, managerid, level)
     AS (SELECT id,
                NAME,
                managerid,
                1
         FROM   employees
         WHERE  managerid IS NULL
         UNION ALL
         SELECT e.id,
                e.NAME,
                e.managerid,
                r.level + 1
         FROM   employees e
                INNER JOIN recursivecte r
                        ON e.managerid = r.id)
SELECT *
FROM   recursivecte;

Case. Using CTE for Aggregation

WITH salessummary
     AS (SELECT employeeid,
                Sum(salesamount) AS TotalSales
         FROM   sales
         GROUP  BY employeeid)
SELECT *
FROM   salessummary
WHERE  totalsales > 10000;

Case. Using Multiple CTEs

WITH salescte
     AS (SELECT employeeid,
                Sum(salesamount) AS TotalSales
         FROM   sales
         GROUP  BY employeeid),
     highperformers
     AS (SELECT employeeid
         FROM   salescte
         WHERE  totalsales > 20000)
SELECT employees.NAME,
       salescte.totalsales
FROM   employees
       JOIN salescte
         ON employees.employeeid = salescte.employeeid
       JOIN highperformers
         ON employees.employeeid = highperformers.employeeid;