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;
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
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
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;
In this example:
- First, the
ctereturned the published years of books and the number of published books each year.
- Then, the outer query used the
cteto calculate the average total of books published between the year 2000 and 2018.
Here is the output:
AVERAGE_BOOKS_PEAR_YEAR ---------------------- 46
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);
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
ctereturns the publisher names and the average rating of their books.
- Then, the
SELECTstatement 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.