Db2 CTE

Summary: in this tutorial, you will learn how to use Db2 common table expression or CTE to query data from tables.

Introduction to Db2 common table expression or CTE

A common table expression a temporary view defined and used during the duration of a SQL statement.

The following illustrates the syntax of the Db2 CTE:

WITH cte_name[(column_list)] AS
(CTE_definition)
SQL_statement;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the CTE. Later, you can refer to the common table expression name in the SQL statement.
• Next, specify a list of comma-separated columns after the table expression name. The number of columns must be equal to the number of column return by the CTE definition.
• Then, use the AS keyword after the table expression name or column list if available.
• After, provide a SQL statement to feed data to the CTE.
• Finally, use a SQL statement such as SELECT, INSERT, UPDATE, or DELETEthat refers the CTE. Notice that you can refer to the same common table expression multiple times in a query.

When to use DB2 CTE

You can use a common table expression in the following scenarios:

• When you want to avoid creating views for reference in an SQL statement.
• When the same result set needs to be referenced multiple times in a query.
• When the results need to be derived using recursion.

DB2 CTE examples

Let’s take some examples of using the CTE to get a better understanding.

1) Using CTE to return the average of totals example

The following query finds the average number of books published between 2000 and 2001 from the books table:

WITH CTE AS (
    SELECT 
        YEAR(published_date) published_year,
        COUNT(*) published_book
    FROM 
        books
    WHERE 
        published_date IS NOT NULL      
    GROUP BY 
        YEAR(published_date)

)   
SELECT 
    AVG(published_book) average_books_pear_year
FROM 
    cte
WHERE 
    published_year BETWEEN 2000 and 2018;
Code language: SQL (Structured Query Language) (sql)

In this example:

• First, the cte returned the published years of books and the number of published books each year.
• Then, the outer query used the cte to calculate the average total of books published between the year 2000 and 2018.

Here is the output:

AVERAGE_BOOKS_PEAR_YEAR 
---------------------- 
46  
Code language: SQL (Structured Query Language) (sql)

2) Referring a CTE multiple times example

This query uses the CTE to return the publisher that has the highest average rating of its books.

WITH cte AS(
    SELECT 
        name, 
        AVG(rating) avg_rating
    FROM 
        books
    INNER JOIN publishers USING (publisher_id)
    GROUP BY 
        name
)
SELECT 
    name, 
    avg_rating
FROM 
    cte 
WHERE 
    avg_rating = (SELECT MAX(avg_rating) FROM cte);
Code language: SQL (Structured Query Language) (sql)

The following picture shows the output:

In this example, we referenced the common table expression twice once in the query and the other in the subquery.

• First, the cte returns the publisher names and the average rating of their books.
• Then, the SELECT statement returns the publisher that has the average rating equals to the maximum average rating.

In this tutorial, you have learned how to use the Db2 common table expression to query data from tables.