Views & Common Table Expressions (WITH / CTEs)

Virtual views, readability with CTEs, and recursive hierarchy queries

« ACID Transactions & Concurrency Control« Previous SQL Summary & Certification Exam »Next »

CTEs (WITH queries) and Views simplify complex database queries, enhancing readability, modularity, and enabling recursive tree traversal.

CTEs vs Views

Common Table Expressions (CTEs): Created using the WITH cte_name AS (...) clause. Exists temporarily for the duration of a single query, eliminating nested subquery spaghetti.

Recursive CTEs: Reference themselves to traverse hierarchical data structures (such as organizational management charts or nested product categories).

Views: Named queries saved permanently in the database catalog (CREATE VIEW view_name AS ...) that can be queried exactly like physical tables while hiding underlying joins and sensitive columns.

Recursive CTE Navigating an Organization Chart

Try It Yourself »

WITH RECURSIVE OrgChart AS (
    -- Anchor member: CEO (ManagerID is NULL)
    SELECT EmployeeID, Name, ManagerID, 1 AS HierarchyLevel
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive member: Find direct reports
    SELECT e.EmployeeID, e.Name, e.ManagerID, o.HierarchyLevel + 1
    FROM Employees e
    INNER JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgChart ORDER BY HierarchyLevel, Name;

Google AdSense Slot (inContentBanner)

Set your Publisher ID in src/config/ads.ts to display live ads.

Test Yourself With Exercise

+15 XP

Which SQL clause introduces a Common Table Expression (CTE)?

« Previous: ACID Transactions & Concurrency Control Next: SQL Summary & Certification Exam »