Posted 3 months ago. Visible to the public. Repeats.

PostgreSQL: WITH Queries (Common Table Expressions)

PostgreSQL's Common Table Expressions 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):

Copy
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:

Copy
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;
Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Author of this card:

Avatar
Arne Hartherz
Last edit:
2 months ago
by Besprechungs-PC
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Arne Hartherz to makandropedia