Yogesh Chauhan's Blog

Common Table Expressions (CTE) in PostgreSQL

in Postgres on January 11, 2021

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


Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to get recent posts in WordPress?WordPress12 URLSearchParams methods in JavaScriptJavaScript3 Ways we can create URLSearchParams Objects in JavaScriptJavaScriptHow does Binding work in JavaScript?JavaScriptHow to add a Pie Chart in Angular App?AngularHow to scroll contents of HTML body horizontally and vertically using JavaScript?JavaScript