Posted 12 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.

Owner of this card:

Avatar
Arne Hartherz
Last edit:
11 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 makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more