Common Table Expressions (CTE) in PostgreSQL

Common Table Expression

A common table expression, which is also known as CTE, is a temporary result set that you can reference within another SQL query statements like SELECT, UPDATE, DELETE, etc.

CTE only exists when you run the query and you can not use it afterwards. They are like temporary table just to use it in the query itself.

Syntax


WITH name_of_CTE (column_1, column_2, column_3,...) AS (
    add a definition like SELECT column_1, column_2 from table where condition 
)
another_query_after_CTE_ends;

Example

I will be using this database for all examples which is available on my Github public repo


WITH order_size_table AS (
    SELECT 
        order_id, 
        order_date,
        (CASE 
            WHEN freight < 50 THEN 'Small'
            WHEN freight < 100 AND freight > 50 THEN 'Medium'
            ELSE 'Large'
        END) order_size    
    FROM
        orders
)
SELECT
	order_id, 
	order_date,
    order_size
FROM 
    order_size_table
WHERE
    order_size = 'Large'
ORDER BY 
    order_date;

// Output

order_id  order_date.       order_size
10255     "1996-07-12"    "Large"
10258     "1996-07-17"    "Large"
10263     "1996-07-23"    "Large"
...
...
187 rows

In the first part of CTE, we are creating a CTE with name order_size_table (it’s like a temp table) where we have different list of orders with different freight size. In that condition we have specified the small, medium and large size conditions.

After that we are just using the CTE we just created with another SQL query statement SELECT.

We are only selecting the rows with large freight size.

We can easily use the same CTE with another SELECT query and JOIN them together.

CTE with INNER JOIN

We can keep the same CTE in this one.

All we are doing is joining it with order_details table using order_id. We can easily check now what’s the size of the order and the quality of the products in it. We can even include price to add more details to it.


WITH order_size_table AS (
    SELECT 
        order_id, 
        order_date,
        (CASE 
            WHEN freight < 50 THEN 'Small'
            WHEN freight < 100 AND freight > 50 THEN 'Medium'
            ELSE 'Large'
        END) order_size    
    FROM
        orders
)
SELECT od.order_id, order_size, quantity
	FROM order_details od
	INNER JOIN order_size_table 
	USING(order_id);

//Output

order_id   order_size    quantity
10248      "Small"         12
10248      "Small"         10
10248      "Small"         5
10249      "Small"         9
...
...
2155 rows
10249	"Small"	40

Another JOIN Example


WITH temp_customers_table AS (
    SELECT customer_id, ship_country,
        COUNT(order_id) total_orders
    FROM   orders
    GROUP  BY ship_country, customer_id, order_date
)
SELECT c.customer_id,
	ship_country,
    total_orders
FROM customers c
    INNER JOIN temp_customers_table USING (customer_id);

//Output

customer_id,    ship_country,    total_orders
"WANDK"         "Germany"          1
"WARTH"         "Finland"          1
"HUNGO"         "Ireland"          1
...
...
823 rows

Over here we are creating a CTE for customers from different countries and the total number of orders placed by them. For an admin view these kind of details would play an important part.

Credit: PostgreSQL Docs