What is a common table expression (CTE) and how is it used?

A common table expression (CTE) is a temporary named result set that can be used in a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are often used to simplify complex queries by breaking them down into smaller, more manageable pieces.

CTEs are defined using the WITH clause in SQL. The syntax for the WITH clause is as follows:

SQL
WITH cte_name (column_name1, column_name2, ...) AS (
SELECT column_name1, column_name2, ...
FROM table_name
WHERE condition
)
SELECT column_name1, column_name2, ...
FROM cte_name
WHERE condition;
Use code with caution. Learn more
The cte_name is the name of the CTE. The column_name1, column_name2, and so on are the names of the columns in the CTE. The SELECT statement defines the result set of the CTE. The WHERE clause is optional and can be used to filter the results of the CTE.

Once a CTE has been defined, it can be referenced in the SELECT statement of the query. CTEs can also be nested, meaning that a CTE can be referenced within another CTE.

Here is an example of a CTE:

SQL
WITH active_customers AS (
SELECT name, email
FROM customers
WHERE last_order_date >= CURRENT_DATE - INTERVAL 30 DAY
)
SELECT name
FROM active_customers;
Use code with caution. Learn more
This query defines a CTE called active_customers that contains the names and emails of all customers who have placed an order in the past 30 days. The main query then selects the names of all active customers from the CTE.

CTEs can be used to perform a variety of tasks, such as:

Filtering data
Aggregating data
Joining multiple tables
Performing complex calculations
CTEs are a powerful tool for simplifying and optimizing complex SQL queries. By understanding how to use CTEs, you can write more efficient and maintainable code.