Skip to main content

Common Table Expressions (CTEs)

Premium

A Common Table Expression (CTE) is a named temporary result set that is defined within the context of a SQL statement. CTEs can be used to simplify complex queries by breaking them down into smaller, more manageable parts.

Basic syntax

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

SQL
WITH cte_name (column1, column2, ...) AS (
  SELECT column1, column2, ...
  FROM table_name
  WHERE some_condition
)
SELECT column1, column2, ...
FROM cte_name
WHERE some_other_condition;

In this example, the WITH clause is used to define the CTE, and the AS clause is used to specify the query that will be used to populate the CTE. The CTE is given a name (in this case cte_name), and the columns returned by the CTE are specified within parentheses after the name.

Once the CTE has been defined, it can be used in the main query just like any other table. In the example above, the CTE is used in the FROM clause of the main query, and additional conditions can be specified in the WHERE clause of the main query if needed.

CTEs are in some ways like "variables" in other programming languages because they are assigned and re-used throughout your query. (SQL also has support for real variables, but they're outside the scope of this course.)

Example CTE Usage

Here's an example of the previous query that used a subquery, rewritten using a CTE:

SQL
WITH customer_orders AS (
  SELECT customer_id, COUNT(*) as num_orders
  FROM orders
  WHERE order_date >= '2022-01-01'
  GROUP BY customer_id
)
SELECT customers.name
FROM customers
JOIN customer_orders
ON customers.customer_id = customer_orders.customer_id
WHERE customer_orders.num_orders > 10;

In this example, the subquery has been replaced with a CTE called customer_orders. The CTE is defined in the WITH clause, and it calculates the number of orders for each customer. The CTE is then used in the main query in a JOIN operation, just like in the previous example.

By using a CTE, this query provides a clear separation between the intermediate result and the main query, making the query easier to read and maintain. Additionally, the CTE can be reused in other parts of the query if needed, making it a more flexible and reusable solution than a subquery.

Comparing CTEs and Subqueries

The main difference between CTEs and subqueries is that CTEs are defined before the main query, while subqueries are embedded within the main query. This can make CTEs more readable and easier to maintain.

Another difference between CTEs and subqueries is that CTEs can be referenced multiple times within a single query, while subqueries can only be used once. This makes CTEs more flexible and reusable, as you can calculate intermediate results once and then reference them multiple times within the same query.

On the other hand, subqueries are often considered more efficient than CTEs, since they can be optimized more easily by the database engine in the context of your query. This means that subqueries may be faster and more performant than CTEs, especially in complex queries where multiple intermediate results are required.

In conclusion, both CTEs and subqueries have their advantages and disadvantages, and the choice between the two will depend on your specific requirements, so consider the trade-offs and choose the one that makes the most sense for your needs.

Try it yourself

Let's try an example. Using the customers and orders tables, write a query to return the list of customers who made a purchase in the past month. For extra practice, try writing it with and without the use of a CTE.

Next Lesson