bugfree Icon
interview-course
interview-course
interview-course
interview-course
interview-course
interview-course
interview-course
interview-course

Data Interview Question

Common Table Expressions in SQL

bugfree Icon

Hello, I am bugfree Assistant. Feel free to ask me for any question related to this problem

Answer

What is a Common Table Expression (CTE)?

A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a SELECT, INSERT, UPDATE, DELETE, or MERGE statement. Defined using the WITH keyword, a CTE is similar to a view but is only valid for the duration of the query in which it is defined. CTEs improve the readability and maintainability of complex queries by breaking them into simpler, more understandable parts.

Benefits of Using CTEs

  • Improved Readability: CTEs help simplify complex queries by breaking them into more manageable pieces, making the SQL code easier to read and understand.
  • Reusability: Once defined, a CTE can be referenced multiple times within the same query, eliminating the need to repeat the same subquery logic.
  • Temporary Nature: CTEs do not require storage space as they exist only for the duration of the query execution, unlike temporary tables.
  • Recursive Queries: CTEs support recursive queries, allowing you to perform operations like hierarchical data retrieval.

Example Scenario

Suppose you have a database table named Employees with columns EmployeeID, EmployeeName, DepartmentID, and Salary. You want to retrieve a list of employees whose salaries are above the average salary for their department.

Here is how you can achieve this using a CTE:

WITH DepartmentAverages AS (
    SELECT DepartmentID, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentID
)
SELECT e.EmployeeName, e.DepartmentID, e.Salary
FROM Employees e
JOIN DepartmentAverages da ON e.DepartmentID = da.DepartmentID
WHERE e.Salary > da.AvgSalary;

Explanation:

  • CTE Definition: The CTE DepartmentAverages calculates the average salary for each department.
  • Main Query: The main query selects employees whose salary exceeds the average salary for their department by joining the Employees table with the DepartmentAverages CTE on DepartmentID.

Conclusion

CTEs are powerful tools for simplifying complex SQL queries. By using CTEs, you can make your queries more readable and maintainable, especially when dealing with complex logic or when you need to reuse subquery logic multiple times within the same query. In scenarios where you need temporary result sets without the overhead of creating and managing physical tables, CTEs are an ideal solution.