WITH Queries - Common Table Expressions

 

WITH queries, or common table expressions, provide a succinct way to create temporary tables that are only kept in memory until the final query is completed. WITH queries are often much faster and more readable than creating multiple temporary tables from which to query as well. The below SQL uses a common table expression to select the average ages for dogs and people by their countries.

1WITH dog_ages AS (
2    SELECT AVG(age) AS avg_dog_age, country 
3    FROM animals
4    GROUP BY country 
5    WHERE species = 'dog'
6), person_ages AS (
7    SELECT AVG(age) as avg_person_age, country 
8    FROM person
9    GROUP BY country
10)
11SELECT da.avg_dog_age, pa.avg_person_page, da.country
12FROM dog_ages da
13LEFT JOIN person_ages pa ON pa.country = da.country;

sql

As you can see, common table expressions are a powerful and expressive means of querying your PostgreSQL database. Remember that anywhere you are using a temporary table, you can probably use a common table expression instead and get faster results