Description

Common Table Expression (CTE) is a temporary result set defined in a single SQL query, and its scope is limited to the query statement that defines it. The main advantage of CTE is that it can improve the readability and reusability of SQL statements.

Syntax

WITH
    <cte_name1> AS (SELECT ...),
    [<cte_name2> AS (SELECT ...)],
    [<cte_nameN> AS (SELECT ...)]
SELECT ...

Parameter Description:

• <cte_name>: Specifies the name of the common table expression, used for reference in the query.

Example

Example 1: Simple CTE Query

Suppose we have a table named oredres that contains two fields, album_name and album_year. We want to query all album names released in 1976 and sort them by name. Using a CTE can simplify the query statement as follows:

WITH 
my_cte AS (SELECT * FROM oredres WHERE album_year = 1976)，
my_cte2 AS (SELECT album_name,count(*) FROM my_cte group by album_name)
SELECT album_name FROM my_cte2 ORDER BY album_name;

Example 2: CTE Query with Filter Conditions

If we want to query the album names released in 1976, but only for a specific artist's works, we can add filter conditions in the CTE:

WITH my_cte AS (SELECT * FROM oredres WHERE album_year = 1976 AND artist = 'ArtistName')
SELECT album_name FROM my_cte ORDER BY album_name;