Joins, subqueries, CTEs and LATERAL

Every join type in practice, correlated subqueries, common table expressions, recursive CTEs, LATERAL joins and RETURNING.

Joins and subqueries

-- inner: only matching rows
select b.title, a.name
from book b join author a on a.id = b.author_id;

-- left: keep books even with no reviews
select b.title, count(r.id) as reviews
from book b
left join review r on r.book_id = b.id
group by b.id, b.title;

-- anti-join: authors with no books, expressed two ways
select a.* from author a left join book b on b.author_id = a.id where b.id is null;
select a.* from author a where not exists (select 1 from book b where b.author_id = a.id);

-- semi-join
select a.* from author a where exists (select 1 from book b where b.author_id = a.id);

-- full outer join: reconcile two sources
select coalesce(x.isbn, y.isbn) as isbn, x.title as local, y.title as remote
from local_book x full join remote_book y using (isbn)
where x.isbn is null or y.isbn is null;

CTEs and recursion

with recent as (
  select id, customer_id, total_cents
  from invoice
  where issued_at >= now() - interval '30 days'
),
totals as (
  select customer_id, sum(total_cents) as revenue, count(*) as invoices
  from recent
  group by customer_id
)
select c.name, t.revenue, t.invoices
from totals t join customer c on c.id = t.customer_id
order by t.revenue desc
limit 20;

-- recursive: an org chart
with recursive org as (
  select id, name, manager_id, 1 as depth
  from employee where manager_id is null
  union all
  select e.id, e.name, e.manager_id, o.depth + 1
  from employee e join org o on e.manager_id = o.id
  where o.depth < 10
)
select repeat('  ', depth - 1) || name as tree, depth from org order by depth, name;

• Since PostgreSQL 12 a CTE referenced once is inlined by default, so it no longer acts as an optimisation fence. materialized forces the old behaviour and not materialized forces inlining.
• A recursive CTE must have a termination condition. A cycle without one runs until it exhausts memory or the statement timeout.
• Use union rather than union all in the recursive term if the set can produce duplicate rows and you want them eliminated - at the cost of a sort per iteration.
• Add cycle detection in PostgreSQL 14+ with cycle id set is_cycle using path instead of tracking an array by hand.

-- data-modifying CTEs: several writes in one statement, all-or-nothing
with moved as (
  delete from staging.book where imported_at is not null
  returning *
)
insert into book (title, author_id)
select title, author_id from moved
returning id, title;

LATERAL and RETURNING

-- top 3 books per author: the classic lateral use
select a.name, b.title, b.published_on
from author a
cross join lateral (
  select title, published_on
  from book
  where author_id = a.id
  order by published_on desc nulls last
  limit 3
) b;

-- left join lateral keeps authors with no books
select a.name, b.title
from author a
left join lateral (
  select title from book where author_id = a.id order by id limit 1
) b on true;

-- expand a jsonb array into rows
select i.id, e.value->>'sku' as sku
from invoice i
cross join lateral jsonb_array_elements(i.metadata->'items') as e;

insert into book (isbn, title, author_id)
values ('9780441013593', 'The Dispossessed', 1)
on conflict (isbn) do update
  set title = excluded.title,
      updated_at = now()
returning id, title, (xmax = 0) as inserted;

FAQ

Related

Window functions, full-text search and regex Query planning and performance tuning

Last refreshed 2026-09-18.