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.
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;
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