SQL Common Table Expression (CTE)

What is a SQL Common Table Expression (CTE)?

A Common Table Expression (CTE) in SQL is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are defined using the WITH keyword and allow you to create a named, reusable subquery within your SQL statement. They provide a way to simplify complex queries and make them more readable.

When you would use it

You would use a CTE when you want to:

1. Simplify complex queries: Break down a complex SQL statement into smaller, more manageable parts to improve readability and maintainability.

2. Avoid duplicating subqueries: Reuse a result set across multiple parts of a query without rewriting the same subquery multiple times.

3. Create recursive queries: When you need to perform recursive operations, such as traversing hierarchical data structures.

4. Improve query organization: Organize your SQL statements by separating logical sections, making it easier to understand and debug.

Syntax

The syntax for defining a CTE using the WITH clause is as follows:

WITH cte_name (column1, column2, ...) AS (
    SELECT ...
    FROM ...
    WHERE ...
)

• cte_name: The name of the CTE, which is used to reference the result set.
• (column1, column2, ...): An optional list of column names for the CTE, used for naming the columns in the CTE.
• SELECT ... FROM ... WHERE ...: The query that defines the CTE, which can include SELECT, FROM, and WHERE clauses.

Parameter values

• cte_name: A user-defined name for the CTE, which should be unique within the SQL statement.
• (column1, column2, ...): Optional. A list of column names for the CTE to provide names for the columns within the CTE.
• SELECT ... FROM ... WHERE ...: The query that defines the CTE, including the SELECT, FROM, and WHERE clauses, which should return a result set.

Example query

Suppose you have a table "employees" and you want to find all employees and their immediate supervisors. You can use a CTE like this:

WITH Supervisors AS (
    SELECT e.employee_id, e.employee_name, s.supervisor_name
    FROM employees e
    LEFT JOIN employees s ON e.supervisor_id = s.employee_id
)
SELECT employee_id, employee_name, supervisor_name
FROM Supervisors;

Example table response

Assuming the "employees" table contains the following data:

| employee_id | employee_name | supervisor_id |
| ----------- | ------------- | ------------- |
| 1           | John Smith    | NULL          |
| 2           | Mary Johnson  | 1             |
| 3           | Sam Brown     | 1             |
| 4           | Alice White   | 2             |

The query mentioned earlier would return the following result:

| employee_id | employee_name | supervisor_name |
| ----------- | ------------- | --------------- |
| 1           | John Smith    | NULL            |
| 2           | Mary Johnson  | John Smith      |
| 3           | Sam Brown     | John Smith      |
| 4           | Alice White   | Mary Johnson    |

This result uses a CTE called "Supervisors" to simplify the query and find the immediate supervisors of employees.

Use cases

• Simplifying complex queries by breaking them down into smaller, reusable parts.
• Reusing subqueries within a single SQL statement to avoid duplication.
• Performing recursive operations, such as navigating hierarchical data structures.
• Organizing SQL statements for better readability and maintainability.

SQL languages this is available for

Common Table Expressions (CTEs) are available in most modern relational database management systems (RDBMS), including but not limited to:

• MySQL
• PostgreSQL
• Oracle Database
• SQL Server
• SQLite
• IBM Db2
• MariaDB

The syntax and behavior of CTEs are generally consistent across these database systems, but there may be minor variations or extensions in specific database implementations.