Common Table Expressions in SQL

Common Table Expressions (CTE) are very useful when we need to use a temporary table for further analysis in a subsequent query.

The common table expression (CTE) is a powerful feature in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and deleted after query execution.

We start defining the SQL CTE using the WITH clause.

CTE Syntax

WITH cte_name (column_name) AS
   (
    SELECT ...
    
    FROM ...
   )
__End CTE
__Main query

SELECT ...

FROM CTE;

In this syntax:

• We have first specified the CTE name that will be referred to later in a query.

• The next step is to create a list of comma-separated columns. It ensures that the number of columns in the CTE definition arguments and the number of columns in the query must be the same. If we have not defined the CTE arguments' columns, it will use the query columns that define the CTE.

• After that, we'll use the AS keyword after the expression name and then define a SELECT statement whose result set populates the CTE.

• Finally, we will use the CTE name in a query such as SELECT, INSERT, UPDATE, DELETE, and MERGE statement.

Using Common Table Expressions

In the below query, we declared the CTE named group_cte and put it in a WITH statement before the main query. Then, the SELECT statement is applied to the group CTE as a table in the main query in the FROM clause. This CTE will create a temporary table names group_cte that will have all the required information like columns company id (comp_id), title and job_count that are taken from our main table job_listings. The CTE is like a subset of our main table.After defining the CTE group_cte, we have referenced it in the SELECT statement to get the desired result.

WITH group_cte AS

    (SELECT comp_id, title, count(job_id) AS job_count

     FROM job_listings

     GROUP BY company_id, title, description)

SELECT count(comp_id) FROM group_cte WHERE job_count > 1;

Multiple CTE

Syntax

WITH   
 cte_name1 (column_names) AS (query),  
 cte_name2 (column_names) AS (query)  
SELECT * FROM cte_name  
UNION ALL  
SELECT * FROM cte_name;    

(The multiple CTE definition can be defined using UNION, UNION ALL, JOIN, INTERSECT, or EXCEPT)

We can also write more than one CTE in a single query. Add a comma to declare another CTE. The "," comma operator must be preceded by the CTE name to distinguish multiple CTE.Multiple CTEs help us in simplifying complex queries that are eventually joined together. Each complex piece had its own CTE, which could then be referenced and joined outside the WITH clause.

In the below example, we have created two CTE sales and profit. In the example below, we've created two CTEs for sales and profit, and we've used them in the main query. As we can see from the SELECT statement the CTEs are joined as if they were tables. When our queries get more complicated, CTEs can be a great way to separate operations, thereby simplifying the final query.

WITH sales AS
   (SELECT country,SUM(sales) AS total_sales
    FROM sales
    GROUP BY country),
profit AS
    (SELECT country,SUM(profit) AS total_profit
    FROM profit
    GROUP BY country)
SELECT country, total_sales,total_profit FROM sales INNER JOIN profit ON
sales.country = profit.country

Advantages of Using CTE

Common Table Expressions are resualbe are are easy to understand. CTE can be reused multiple times in a query. Instead of rewriting the same query every time we need to use it, we can write in the CTE once and can reuse that CTE.They help separate out query logic. If we are joining two complex queries, you can use non recursive CTEs to separate out the complexity of the queries from the actual join. By doing this, not only can we test the query definition independently, but we can also easily identify the parts of the join that need to be worked on. As CTE is declared at the top of the query which is easier for users to understand by following the query’s logic from top to bottom as compared to the subqueries nested fashion. By breaking the query into smaller pieces using meaningful names (e.g. sales and profit), it is also easier to understand the entire query.

When our queries become more complex, then we use views to make it simpler. Views are useful for encapsulating query and join logic. Queries using the view are simpler and easier to read. The creation of a view may not be appropriate in all cases. As an example, if we do not have permission to create database objects, such as if we are using a third party database, Common table expressions can be used in these situations.

Conclusion

An important feature of the SQL language is its ability to use common table expressions. By using them, we can create more readable queries and manage data format differences between table data and report data. Like database views and derived tables, CTEs can make it easier to write and manage complex queries by making them more readable and simple. This blog gives a basic understanding about CTE. Thanks for reading.