SQL Interview Questions

A Common Table Expression (CTE) in SQL is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs make complex queries easier to read, write, and maintain by breaking them into simpler, more manageable parts. They are defined using the WITH clause and can be recursive or non-recursive.

Syntax

WITH cte_name (column1, column2, ...)
AS (
    -- CTE query
    SELECT ...
)
SELECT column1, column2, ...
FROM cte_name;

• cte_name: The name of the CTE.
• column1, column2, ...: Optional column names for the CTE result set.
• CTE query: The query that defines the CTE.

Types of CTEs

1. Non-Recursive CTE: A CTE that does not reference itself.
2. Recursive CTE: A CTE that references itself, allowing it to iterate and build a result set iteratively.

Examples

Example 1: Non-Recursive CTE

Scenario: Simplify a complex query that retrieves department names and the total number of employees in each department.

WITH DepartmentEmployeeCount AS (
    SELECT department_id, COUNT(*) AS employee_count
    FROM employees
    GROUP BY department_id
)
SELECT d.department_name, c.employee_count
FROM departments d
JOIN DepartmentEmployeeCount c ON d.department_id = c.department_id;

Explanation: The CTE DepartmentEmployeeCount calculates the number of employees in each department. The main query then joins this CTE with the departments table to get the department names along with the employee counts.

Example 2: Recursive CTE

Scenario: Retrieve an organizational hierarchy (e.g., employees and their managers).

WITH RECURSIVE EmployeeHierarchy AS (
    SELECT employee_id, manager_id, employee_name, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id, e.employee_name, h.level + 1
    FROM employees e
    INNER JOIN EmployeeHierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM EmployeeHierarchy;

Explanation: The recursive CTE EmployeeHierarchy starts with employees who have no managers (top-level employees). The recursive part of the CTE then iteratively finds employees reporting to the top-level employees, building the hierarchy level by level.

Advantages of Using CTEs

1. Improved Readability: Breaking down complex queries into simpler, logical blocks makes them easier to read and understand.
2. Reusable Result Sets: You can reference the CTE multiple times within the main query, avoiding redundant code.
3. Modularity: CTEs help modularize query logic, making maintenance and updates easier.
4. Recursive Queries: Simplifies the syntax for recursive queries compared to traditional recursive SQL methods.

CTE vs. Subqueries

CTE:

• Defined at the beginning of the query using the WITH clause.
• Can be referenced multiple times within the main query.
• Improves readability and modularity.
• Supports recursion.

Subquery:

• Embedded within the main query, usually in the SELECT, FROM, or WHERE clause.
• Typically used only once within the main query.
• Can make complex queries harder to read.
• Does not support recursion directly.

Example Comparison

CTE:

WITH AverageSalaries AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT e.employee_id, e.employee_name, a.avg_salary
FROM employees e
JOIN AverageSalaries a ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;

Subquery:

SELECT e.employee_id, e.employee_name, a.avg_salary
FROM employees e
JOIN (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
) a ON e.department_id = a.department_id
WHERE e.salary > a.avg_salary;

Conclusion

Common Table Expressions (CTEs) are a powerful SQL feature that enhances query readability, maintainability, and modularity. They allow for easier management of complex queries by breaking them into simpler parts and support recursive queries, which are useful for hierarchical data retrieval. Understanding and using CTEs effectively can significantly improve the quality and efficiency of your SQL queries.