Common Table Expression in SQL

Last Updated: 26th September, 2023

Overview

This lesson will teach about Common Table Expression(CTE), also named a temporary result set. We will also see the WITH keyword used to define CTE in detail.

What is Common Table Expression?

A Common Table Expression (CTE) is a named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is similar to a derived table or subquery but can be referenced multiple times within a single query.

CTEs are defined using the WITH keyword, followed by a comma-separated list of named queries. Each named query consists of a name, followed by the SELECT statement defining the result set.

Why is Common Table Expression Used?

Some common scenarios where you might want to use a CTE:

1. Recursive queries: CTEs are often used to write recursive queries that traverse hierarchical data, such as an organization chart or a family tree.
2. Subquery replacement: Instead of using a subquery in a larger query, you can create a CTE that contains the subquery and reference it in the larger query. This can make the query easier to read and maintain.
3. Code reusability: If a complex query is used in multiple places in your code, you can create a CTE to encapsulate that query and make it easier to reuse.

Overall, CTEs are a useful tool in SQL for simplifying queries, improving performance, and making code more maintainable.

Syntax of CTE

The syntax of a Common Table Expression (CTE) in SQL is as follows:

WITH cte_name (column1, column2, column3, ...) AS (
   SELECT column1, column2, column3, ...
   FROM table_name
   WHERE condition
)
SELECT ...
FROM cte_name
WHERE condition;

Here, cte_name is the CTE's name, followed by a list of column names (optional). The SELECT statement inside the parentheses defines the CTE's result set. It can use any valid SQL statement, including joins and subqueries. Finally, the CTE is referenced in the main query using its name.

It's important to note that the CTE can only be referenced in the query that immediately follows its definition. Additionally, the CTE is only visible within the scope of the SELECT, INSERT, UPDATE, or DELETE statement in which it is defined. Once the statement is executed, the CTE is discarded.

CTEs can be used to simplify complex queries and improve query performance by reducing the need for subqueries and temporary tables.

Example:

We can take the example of an employee's table that includes the employee ID, first name, last name, and salary of all employees.

For example, the following CTE defines a result set that includes the employee ID, first name, last name, and salary of all employees who earn more than $50,000 per year:

WITH high_earners AS (
  SELECT employee_id, first_name, last_name, salary
  FROM employees
  WHERE salary > 50000
)
SELECT *
FROM high_earners;

In this example, the CTE is named "high_earners" and is defined using a SELECT statement that filters the "employees" table based on the salary column. The CTE is then referenced in the main query using its name, which allows the result set to be used in further operations.

CTEs are particularly useful when a query requires multiple steps or when the same subquery needs to be referenced multiple times within a single query. They can also make complex queries easier to read and understand by breaking them down into smaller, more manageable parts.

It is important to note that CTEs are only visible within the query that defines them and are discarded as soon as the query is executed. They are not stored in the database or persisted in any way.

Types of CTE:

1. Recursive CTE: A recursive CTE is used to query hierarchical data such as an organization chart or a bill of materials. The following example shows how to use a recursive CTE to query a self-referencing table:

WITH RECURSIVE cte_employee AS (
 SELECT employee_id, name, manager_id, 1 AS level
 FROM employees
 WHERE manager_id IS NULL
 UNION ALL
 SELECT e.employee_id, e.name, e.manager_id, c.level + 1
 FROM employees e
 JOIN cte_employee c ON c.employee_id = e.manager_id
)
SELECT employee_id, name, manager_id, level
FROM cte_employee;

2. Inline CTE: An inline CTE is used to simplify complex queries by breaking them down into smaller, more manageable parts. The following example shows how to use an inline CTE to calculate the average salary of employees in each department:

SELECT department_id, AVG(salary) AS avg_salary
FROM (
 SELECT employee_id, salary, department_id
 FROM employees
) AS subquery
GROUP BY department_id;

3. Pivot CTE: A pivot CTE transforms rows into columns to make data easier to analyze. The following example shows how to use a pivot CTE to transform sales data into a format suitable for analysis:

WITH pivot_cte AS (
 SELECT product, year, quarter, SUM(amount) AS total
 FROM sales
 GROUP BY product, year, quarter
)
SELECT product,
 MAX(CASE WHEN year = 2020 AND quarter = 'Q1' THEN total END) AS q1_2020,
 MAX(CASE WHEN year = 2020 AND quarter = 'Q2' THEN total END) AS q2_2020,
 MAX(CASE WHEN year = 2020 AND quarter = 'Q3' THEN total END) AS q3_2020,
 MAX(CASE WHEN year = 2020 AND quarter = 'Q4' THEN total END) AS q4_2020,
 MAX(CASE WHEN year = 2021 AND quarter = 'Q1' THEN total END) AS q1_2021,
 MAX(CASE WHEN year = 2021 AND quarter = 'Q2' THEN total END) AS q2_2021
FROM pivot_cte
GROUP BY product;

Best Practices:

1. Use Descriptive Names: Always use descriptive and meaningful names for your CTEs. Use names that clearly explain what the CTE is doing or what it represents.
2. Proper Formatting: Proper formatting of your CTE can make it easier to read and understand. Use indentation and line breaks to separate the different parts of your CTE clearly.
3. Avoid Repeating Code: Avoid repeating the same code in your CTE. If you need to use the same code in multiple CTEs, consider creating a view or function to encapsulate the code.
4. Use Appropriate Clauses: Use appropriate clauses like WHERE, GROUP BY, ORDER BY, and HAVING to optimize your CTEs. These clauses help reduce the number of rows that need to be processed and improve the performance of your queries.
5. Be Mindful of Performance: Always be mindful of the performance impact of your CTEs. CTEs can sometimes be slower than other query constructs like subqueries, so it's important to test the performance of your CTEs and optimize them as necessary.
6. Use Indexes: Use indexes on columns used in your CTEs to improve performance. This will offer assistance in reducing the number of rows that ought to be checked during query execution.
7. Consider Utilizing Temporary Tables: In a few cases, it may be more proficient to utilize temporary tables rather than CTEs. Temporary tables can be indexed and can be utilized to store intermediate results that can be reused in numerous queries.
8. Avoid Nesting CTEs: Avoid nesting CTEs too deeply, making your query difficult to read and maintain. Instead, break down your query into smaller, more manageable parts.
9. Use Appropriate Joins: Use appropriate join types to optimize your CTEs. Choosing the right join type can result in faster query performance.
10. Test Your Queries: Always test your CTEs thoroughly before using them in production. This can help you catch any performance issues or errors before they cause problems in your application.

Conclusion

A Common Table Expression (CTE) is a named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using the WITH keyword and is useful for recursive queries, subquery replacement, and code reusability.

Key Takeaways

• This lesson covers Common Table [removed]CTE), which may be a named temporary result set that can be referenced inside a SELECT, Insert, Update, or Erase statement. It is comparable to a derived table or subquery, but it can be referenced at different times inside a single query.
• CTEs are defined utilizing the WITH keyword, taken after by a comma-separated list of named queries.
• A CTE can be utilized to streamline complex queries and improve query execution by decreasing the requirement for subqueries and temporary tables.
• Recursive CTE is utilized to query hierarchical data such as an organization chart or a charge of materials. In contrast, inline CTE is utilized to streamline complex queries by breaking them into smaller, more manageable parts.
• CTEs are only visible inside the query that defines them and are disposed of as soon as the query is executed. They are not stored within the database or persisted in any way.

Quiz

1. What is a Common Table Expression (CTE)?
   1. A permanent table in a database 
   2. A named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement  
   3. A type of subquery that can be referenced multiple times within a single query  
   4. A derived table that is only visible within the query that defines it

Answer:b.  A named temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

2. What is the purpose of using a Common Table Expression (CTE)?
   1. To create a permanent table in a database 
   2. To simplify queries, improve performance, and make code more maintainable 
   3. To replace subqueries with inline code 
   4. To define complex joins between tables

Answer: b. To simplify queries, improve performance, and make code more maintainable.

3. Which keyword is used to define a CTE in SQL?
   1. CREATE  
   2. INSERT  
   3. SELECT 
   4. WITH

Answer: d. WITH.

4. Can a CTE be referenced in multiple queries?
   1. Yes  
   2. No

Answer: b. No. A CTE can only be referenced within the query that immediately follows its definition.

5. What are some common scenarios where a CTE might be used?
   1. Recursive queries, subquery replacement, and code reusability 
   2. Complex joins, inline code, and temporary tables  
   3. Indexing, partitioning, and clustering 
   4. Data warehousing, replication, and backup

Answer:a. Recursive queries, subquery replacement, and code reusability.

6. Is a CTE stored in the database or persisted in any way?
   1. Yes  
   2. No

Answer: b. No. A CTE is only visible within the query that defines it, and it is discarded as soon as the query is executed.