CTE: what it is and it's purpose in SQL

Does your team use CTE in their daily programming process? This is a resource that helps you simplify SQL queries, especially at times when you have to join several requests in a single code.

Sometimes a query needs to join tables, perform aggregations, rename columns, or sort everything in a single, more robust query. This huge amount of information can create a challenging environment for professionals in this process. In fact, they might not even know where to start.

Stay in this article if you’ve already been through this or something similar. Here you can learn a resource that helps you divide the query into steps to improve the discussion and organization of ideas!

What is CTE?

Common Table Expression (CTE) is an alternative to subqueries, views, and user-defined functions. It is then a named and temporary set of results created by SELECT statements that can be used in subsequent SELECT statements.

This feature performs better on the database than Subselect. The basic CTE syntax starts with a With and is followed by the Expression Name:

WITH expression_name [( column_name [,…n] )]

TO THE

(CTE_query_definition)

The inclusion of the name is carried out through the name_expression, where the name of the columns of this temporary table is immediately declared. To view the CTE result you can use Select Query with CTE expression name.

Select [Column1,Column2,Column3 …..] from expression_name

Another option is:

Select * from expression_name

Once nominated, it is necessary to write AS and declare the query, as this information is essentially used to fill in the CTE.

What is CTE used for?

You may be asking yourself: “If its main characteristic is to be temporary, then what is the use of CTE in my code inside the database?”

Its main function is to simplify writing complex queries, helping to break logic into smaller pieces making them easier to understand.

As an example, let’s consider the query below that aims to calculate total sales by month and year:

SELECT MONTH(order_date) AS month, YEAR(order_date) AS year, SUM(total_price) AS total_sales

FROM orders

GROUP BY MONTH(order_date), YEAR(order_date)

ORDER BY year, month;

In this example, we can separate this query into two parts:

Aggregating and grouping the fields, i.e. extracting the month, year, and sum of prices.
Apply sorting by year and month to make the table easier to read.

To separate the construction logic of the query, it is possible to assign the first part to a CTE and the second to make a query of the CTE created in step 1. Thus, we have the possibility of coding as follows:

WITH sale_per_month (month, year, total_sales) AS (

     SELECT MONTH(order_date), YEAR(order_date), SUM(total_price)

     FROM orders

     GROUP BY MONTH(order_date), YEAR(order_date)

)

SELECT month, year, total_sales

FROM sale_per_month

ORDER BY month, year;

In this last example, the CTE was named sale_per_month and referenced in the following query, right after the SELECT. By writing this way, it was possible to break up the query logic, making the code more readable, organized, and easier to maintain.

Still thinking about the saying “divide and conquer”, in the example above we can think of two steps to build the query. Although it may seem simple, in some situations, database modeling can make the construction of the query more challenging. In such situations, we would even have to divide the logic into other parts. In this case, we can take advantage of nested CTEs.

CTEs are nested when they can be defined one after the other, which allows you to build complex multi-step queries. Based on the previous CTE, each nested CTE is also used as the basis for the next query step.

Given this, let’s now compare and explore the entire integration of the following commands, so that we can check in detail the information that is displayed therein.

WITH

departments (id, name) AS (

SELECT id, name

FROM department

),

collaborators (id, name, id_dept) AS (

SELECT id, name, dept_id

FROM employee

JOIN departments

ON collaborator.id_depto = departments.id

),

sales (collaborator_id, total_sale) AS (

SELECT employee_id, SUM(total_price)

FROM orders

JOIN collaborators

ON orders.id_collaborator = employees.id

GROUP BY collaborator_id

)

SELECT name, total_sale

FROM sales

JOIN collaborators

Vendas.id_colaborador = collaborators.id;

As you can see, the main objective of the code above is to show each person who collaborates with a company and the total sales made by them. An example output from this query would be:

name	total_sale
Mary	156
Luan	150
Fabio	153
Eduarda	170

In this construction, the query was carried out in sequential steps, organizing it into three different tables (departments, employees and sales) in which one refers to the other using its nickname (alias – AS).

In the last step, for example, the query selects the columns “name”, “sale_total” , so that the use of the JOIN refers to the table “employees”. Building on the previously nested steps, these intentional actions are performed to achieve our stipulated purpose.

So, in addition to being a great resource to help build and test complex queries, it also offers a temporary feature like a virtual table. That is, data is not stored permanently, only while the query is being executed.

Where to access CTE?

The Common Table Expressions, CTE, feature was added to SQL with the implementation of the WITH clause in SQL-1999 (SQL3). It became available in a variety of DBMS, Database Management Systems, such as SQL Server, PostgreSQL, Oracle, MySQL and others. However, CTE availability and support vary by DBMS version, and it is common for the most recent versions to support these features.

The more complex your query is, the better it fits the CTE. Although CTEs make the code easier to read, they are not suitable for every SQL query. Its main purpose is to improve the readability of the query and consequently future maintenance.

Get to know more abou my writing skills checking out my other samples!