What are CTEs (Common Table Expressions) in SQL? How do you use WITH clause?

Q: What are CTEs (Common Table Expressions) in SQL? How do you use WITH clause?

CTEs — Common Table Expressions A CTE creates a named, temporary result set defined before the main query using the WITH keyword. It exists only for the duration of that query. Basic Syntax sql WITH ctename AS ( -- CTE definition SELECT ... FROM ... WHERE ... ) -- Main query using the CTE SELECT FROM ctename; Simple CTE — Replace Nested Subquery sql -- Without CTE (hard to read): SELECT name, salary FROM employees WHERE salary (SELECT AVG(salary) FROM employees); -- With CTE (much cleaner): WITH avgsalary AS ( SELECT AVG(salary) AS avgsal FROM employees ) SELECT e.name, e.salary FROM employees

Answer

CTEs — Common Table Expressions

A CTE creates a named, temporary result set defined before the main query using the WITH keyword. It exists only for the duration of that query.

Basic Syntax

SQL

WITH cte_name AS (
    -- CTE definition
    SELECT ...
    FROM ...
    WHERE ...
)
-- Main query using the CTE
SELECT * FROM cte_name;

Simple CTE — Replace Nested Subquery

SQL

-- Without CTE (hard to read):
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- With CTE (much cleaner):
WITH avg_salary AS (
    SELECT AVG(salary) AS avg_sal FROM employees
)
SELECT e.name, e.salary
FROM employees e, avg_salary a
WHERE e.salary > a.avg_sal;

Multiple CTEs — Chain Them

SQL

WITH
-- Step 1: get avg salary per department
dept_avg AS (
    SELECT department, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
),
-- Step 2: find departments above company average
high_paying_depts AS (
    SELECT department, avg_sal
    FROM dept_avg
    WHERE avg_sal > (SELECT AVG(salary) FROM employees)
)
-- Step 3: get employees in those departments
SELECT e.name, e.department, e.salary, h.avg_sal AS dept_avg
FROM employees e
JOIN high_paying_depts h ON e.department = h.department
ORDER BY e.salary DESC;

CTE vs Subquery vs Temp Table

SQL

-- Subquery (can''t reuse, hard to read)
SELECT * FROM (SELECT id, name FROM employees WHERE active = 1) AS active_emps;

-- CTE (readable, reusable within query)
WITH active_emps AS (
    SELECT id, name FROM employees WHERE active = 1
)
SELECT * FROM active_emps;   -- can reference multiple times

-- Temp Table (persists for session — different use case)
CREATE TEMP TABLE active_emps AS
SELECT id, name FROM employees WHERE active = 1;

Recursive CTE — Hierarchical Data (Org Chart, Tree)

SQL

-- employees table with manager_id (self-referencing)
-- | id | name    | manager_id |
-- |  1 | CEO     | NULL       |
-- |  2 | VP_Eng  |    1       |
-- |  3 | SDET1   |    2       |
-- |  4 | SDET2   |    2       |

WITH RECURSIVE org_chart AS (
    -- Anchor: start with the CEO (no manager)
    SELECT id, name, manager_id, 0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive: join each employee to their manager
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT('  ', level) || name AS org_tree, level
FROM org_chart
ORDER BY level, name;

-- Result:
-- CEO          (level 0)
--   VP_Eng     (level 1)
--     SDET1    (level 2)
--     SDET2    (level 2)

CTE for Test Data Preparation

SQL

-- Generate test IDs using CTE
WITH test_users AS (
    SELECT 'user_' || generate_series AS username,
           'user_' || generate_series || '@test.com' AS email,
           'Test@123' AS password
    FROM generate_series(1, 100)  -- PostgreSQL
)
INSERT INTO users (username, email, password)
SELECT username, email, password FROM test_users;