Beginner’s 101: The Ultimate Guide to Common Table Expressions (CTEs)

Are you tired of convoluted SQL queries that take forever to run?

Than………..,

 Say goodbye to complexity and embrace the power of Common Table

Expressions (CTEs).

This ultimate beginner guide will demonstrate how CTEs can make your SQL code

more concise, elegant, and lightning-fast.

What are Common Table Expressions (CTEs)?

In SQL, CTE stands for Common Table Expression and it is used to define a temporary view during the execution of a statement. It simplifies complex queries and improves SQL code organization and readability. By using CTEs, a temporary table can be created within the same SQL statement.

 When/Where to use CTEs: 

• In a query, CTEs are useful for referencing the same result set multiple times.
• The use of these tools can simplify complex queries and improve query performance.
• VIEWs, SELECTs, INSERTs, UPDATES, DELETES, and MERGES can all use CTEs.
• These functions are especially useful when dealing with recursive queries.
• When it comes to reading and understanding CTEs, they may be a better alternative to subqueries.
• The use of CTEs can avoid creating views for reference in SQL statements.
• In recursive CTEs, the same name is given within another SQL query, which may be referred to multiple times within the main query.
• During the lifetime of the query, CTEs exist only temporarily.
  

CTE  – Syntax:

WITH cte_name[(column_list)] AS (CTE_definition) SQL_statement;

CTEs are defined by the WITH keyword.

CTE_NAME specifies the name of the CTE, which can later be referenced within SQL statements.

A column name list called COLUMN_LIST is an optional list of column names that can be used with a CTE.

The CTE_DEFINITION statement defines the result set for the CTE.

The SQL statement utilizing the CTE is SQL_STATEMENT.

Example  – CTE:

Consider three tables: ORDERS, CUSTOMERS, and PRODUCTS. The goal is to find all the orders placed by customers in the city of CHENNAI and include information about the products ordered. Using a CTE simplifies the query

 WITH CUSTOMER_ORDERS AS (

          SELECT O.ORDER_ID,
           O.CUSTOMER_ID,
            O.PRODUCT_ID
          FROM ORDERS O
           JOIN CUSTOMERS C
             ON O.CUSTOMER_ID = C.CUSTOMER_ID
          WHERE C.CITY = 'Chennai'
   )
   SELECT CO.ORDER_ID,
         CO.CUSTOMER_ID,
         P.PRODUCT_NAME
      FROM CUSTOMER_ORDERS CO
        JOIN PRODUCTS P
          ON CO.PRODUCT_ID = P.PRODUCT_ID;

Result:

Understanding Recursive CTEs

• In SQL, recursive CTEs are powerful constructs that allow you to manipulate and retrieve complex data.
• When working with hierarchical or tree-structured data, recursive CTEs are particularly useful.
• It is possible for recursive CTEs to reference themselves, which means they repeatedly execute subsets of the data until the complete results are obtained.
• It is composed of two members, namely a recursive member and an anchor member.
• While the anchor member refers to the base result set of the CTE, the recursive member refers to the CTE itself.
• The syntax for creating a recursive CTE in DB2 for i is similar to that of a non-recursive CTE but with the addition of the WITH RECURSIVE keywords.
• A CTE can be combined using the UNION ALL operator, which combines the anchor and recursive members.

The Syntax of Recursive CTEs

         
          WITH RECURSIVE cte_name (column1, column2…) AS (

 -- Anchor member
                         SELECT…
                         UNION ALL
 -- Recursive member
                         SELECT ...
                         FROM cte_name
                          WHERE ...
                       )
                         SELECT ...
                         FROM cte_name;

Note: The column name list must specify the table name of the Recursive CTE 

Example of Recursive CTEs

In the following example, recursive CTE is used to calculate the factorials of the numbers 1 through 10.

• In this case, the FACTORIAL CTE is defined using the WITH RECURSIVE
• There are two columns in the CTE: N and RESULT, which reflect the number and the factorial of the number, respectively.
• From the SSYSDUMMY1 table, an anchor member selects 1 and its factorial 1.
• Afterwards, the CTE recursively references itself and multiplies each subsequent number by its previous factorial.
• Upon reaching 10, the recursion ends.
• As a final step, the query selects all columns from the FACTORIAL CTE that contains the factorials of numbers 1 through 10.

        WITH RECURSIVE FACTORIAL (N, RESULT) AS (
             SELECT 1,
       	             1
              FROM SYSIBM.SYSDUMMY1

          UNION ALL
 
          SELECT N + 1,
       	        (N + 1) * RESULT
          FROM FACTORIAL
          WHERE N < 10
      )
      SELECT *
      FROM FACTORIAL;

 Result:

Conclusion

Common Table Expressions (CTEs) are a powerful and essential tool in a SQL developer’s toolkit. They enable the creation of temporary result sets, making complex queries more manageable and readable. With the ability to create recursive queries and facilitate code reusability, CTEs prove their worth when dealing with intricate data structures and operations. By mastering the art of CTEs, SQL developers can enhance their query optimization skills and extract valuable insights from vast amounts of data with ease. So, embrace the versatility of CTEs and elevate your SQL prowess to new heights!