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
CTE database Expressions sql query table