PostgreSQL's Common Table Expressions (CTEs) can be used to extract sub-queries from bulky SQL statements into a temporary table to be referenced instead.
This is most useful to avoid ugly joins or sub-selects. CTEs can be used for SELECT, INSERT, UPDATE or DELETE.
Example (from the PostgreSQL docs):
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;
Note how the SELECT statement at the end is rather short and much easier to read. The CTE SELECTs are also quite readable. Imagine how a JOINed statement would look like...
Using sub-queries, the above statement would look something like this:
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (
SELECT region FROM (
SELECT region FROM (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
)
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
)
GROUP BY region, product;
Posted by Arne Hartherz to makandra dev (2017-10-30 17:49)