PostgreSQL LATERAL Joins: Top-N Per Group, unnest Expansion, and Correlated Subqueries

LATERAL joins let a subquery reference columns from tables earlier in the FROM clause — making it possible to run a correlated subquery for each row of the outer table. The most useful application is top-N per group: the top 3 most recent orders per customer, the top 5 trending posts per category, the latest event per user. Without LATERAL, these require window functions or complex subqueries; with LATERAL, they're clean and fast.

Top-N Per Group: The Classic Use Case

-- Scenario: get the 3 most recent orders for each customer
-- This is the prototypical "top-N per group" problem

-- ❌ Approach 1: Subquery with IN — doesn't limit to N per group
SELECT *
FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE plan = 'growth');

-- ❌ Approach 2: Window function — works but filters AFTER the join
SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) ranked
WHERE rn <= 3;
-- Problem: scans all orders, then filters (no index-per-customer benefit)

-- ✅ Approach 3: LATERAL — index scan per customer, stops after 3 rows
SELECT
  c.id          AS customer_id,
  c.name,
  c.email,
  o.id          AS order_id,
  o.amount_cents,
  o.created_at
FROM customers c
JOIN LATERAL (
  SELECT id, amount_cents, created_at
  FROM orders
  WHERE customer_id = c.id          -- References outer table column
  ORDER BY created_at DESC
  LIMIT 3                           -- Stop after 3 rows per customer
) o ON TRUE                         -- Always join (inner join equivalent)
WHERE c.workspace_id = $1
ORDER BY c.id, o.created_at DESC;

-- The planner can use the index on orders(customer_id, created_at DESC)
-- and do a fast index scan for each customer — much more efficient than
-- scanning all orders and then filtering

LEFT JOIN LATERAL: When Rows May Have No Results

-- LEFT JOIN LATERAL: include customers even if they have no orders
SELECT
  c.id,
  c.name,
  o.id          AS latest_order_id,
  o.amount_cents AS latest_order_amount,
  o.created_at  AS latest_order_at
FROM customers c
LEFT JOIN LATERAL (
  SELECT id, amount_cents, created_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY created_at DESC
  LIMIT 1
) o ON TRUE          -- ON TRUE required (the correlation is in WHERE, not ON)
WHERE c.workspace_id = $1;

-- Without LEFT JOIN LATERAL, customers with no orders would be excluded
-- With LEFT JOIN LATERAL, they appear with NULL order columns

LATERAL for Top Events Per Workspace

-- Dashboard: most frequent events for each workspace in last 7 days
SELECT
  w.id AS workspace_id,
  w.name,
  top_events.name AS event_name,
  top_events.count
FROM workspaces w
JOIN LATERAL (
  SELECT name, COUNT(*) AS count
  FROM events
  WHERE workspace_id = w.id
    AND occurred_at >= NOW() - INTERVAL '7 days'
  GROUP BY name
  ORDER BY count DESC
  LIMIT 5
) top_events ON TRUE
WHERE w.plan IN ('growth', 'enterprise')
ORDER BY w.id, top_events.count DESC;

unnest with LATERAL: Expand Arrays Into Rows

-- Workspace has a tags TEXT[] column
-- Find all workspaces that use each tag, exploding the array

CREATE TABLE workspaces (
  id   UUID PRIMARY KEY,
  name TEXT NOT NULL,
  tags TEXT[] NOT NULL DEFAULT '{}'
);

-- Without LATERAL: unnest works but loses row context without explicit reference
SELECT id, unnest(tags) AS tag FROM workspaces;  -- Works but limited

-- With LATERAL: explicit and joins cleanly with other tables
SELECT
  w.id,
  w.name,
  t.tag
FROM workspaces w,
  LATERAL unnest(w.tags) AS t(tag)  -- Generates one row per tag element
WHERE 'postgresql' = ANY(w.tags)    -- Filter first (index on GIN tags)
ORDER BY w.id, t.tag;

-- Equivalent using JOIN LATERAL (same result):
SELECT
  w.id,
  w.name,
  t.tag
FROM workspaces w
JOIN LATERAL unnest(w.tags) AS t(tag) ON TRUE;

-- Count tag usage across all workspaces:
SELECT
  t.tag,
  COUNT(DISTINCT w.id) AS workspace_count
FROM workspaces w,
  LATERAL unnest(w.tags) AS t(tag)
GROUP BY t.tag
ORDER BY workspace_count DESC
LIMIT 20;

LATERAL with Set-Returning Functions

-- generate_series with LATERAL: create date ranges per row
SELECT
  p.id    AS project_id,
  p.name,
  days.d  AS day
FROM projects p,
  LATERAL generate_series(
    p.start_date,
    COALESCE(p.end_date, CURRENT_DATE),
    '1 day'::interval
  ) AS days(d)
WHERE p.workspace_id = $1
  AND p.status = 'active'
ORDER BY p.id, days.d;

-- This generates one row per day for each project's duration
-- Useful for building calendar heatmaps or Gantt chart data

LATERAL for Pagination With Context

-- Cursor pagination with additional context per page item
SELECT
  t.id,
  t.title,
  t.status,
  t.created_at,
  -- LATERAL subquery for related data without separate query
  assignee.name AS assignee_name,
  comment_count.n AS comments
FROM tasks t
JOIN LATERAL (
  SELECT name FROM users WHERE id = t.assignee_id
) assignee ON TRUE
LEFT JOIN LATERAL (
  SELECT COUNT(*) AS n FROM task_comments WHERE task_id = t.id
) comment_count ON TRUE
WHERE t.project_id = $1
  AND t.created_at < $2  -- Cursor
ORDER BY t.created_at DESC
LIMIT 25;

Performance: When LATERAL is Faster

-- Use EXPLAIN to compare approaches

-- Setup: index exists on orders(customer_id, created_at DESC)

-- Approach A: Window function
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders WHERE workspace_id = $1
) r WHERE rn <= 3;
-- Likely: Seq Scan or large index scan → sort → window filter

-- Approach B: LATERAL
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.id, o.*
FROM customers c
JOIN LATERAL (
  SELECT * FROM orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 3
) o ON TRUE
WHERE c.workspace_id = $1;
-- Likely: Index Scan on customers → Nested Loop → Index Scan on orders per customer
-- Much faster when customers are few and orders index is selective

TypeScript with Prisma: LATERAL via $queryRaw

// lib/queries/top-orders.ts
import { prisma } from "@/lib/prisma";

interface CustomerWithTopOrders {
  customerId:    string;
  customerName:  string;
  orderId:       string;
  amountCents:   number;
  createdAt:     Date;
}

export async function getTopOrdersPerCustomer(
  workspaceId: string,
  ordersPerCustomer = 3
): Promise<CustomerWithTopOrders[]> {
  return prisma.$queryRaw<CustomerWithTopOrders[]>`
    SELECT
      c.id          AS "customerId",
      c.name        AS "customerName",
      o.id          AS "orderId",
      o.amount_cents AS "amountCents",
      o.created_at  AS "createdAt"
    FROM customers c
    JOIN LATERAL (
      SELECT id, amount_cents, created_at
      FROM orders
      WHERE customer_id = c.id
      ORDER BY created_at DESC
      LIMIT ${ordersPerCustomer}
    ) o ON TRUE
    WHERE c.workspace_id = ${workspaceId}::uuid
    ORDER BY c.id, o.created_at DESC
  `;
}

// unnest expansion via $queryRaw
export async function getTagUsageStats(workspaceId: string) {
  return prisma.$queryRaw<{ tag: string; count: bigint }[]>`
    SELECT t.tag, COUNT(DISTINCT w.id) AS count
    FROM workspaces w,
      LATERAL unnest(w.tags) AS t(tag)
    WHERE w.id IN (
      SELECT id FROM workspaces WHERE parent_id = ${workspaceId}::uuid
    )
    GROUP BY t.tag
    ORDER BY count DESC
    LIMIT 20
  `;
}

When to Use LATERAL vs Alternatives

Cost and Timeline Estimates

See Also

• PostgreSQL Window Functions
• PostgreSQL CTE and Recursive Queries
• PostgreSQL Performance Tuning
• PostgreSQL Statistics and ANALYZE
• PostgreSQL Indexing Advanced Patterns

---

Working With Viprasol

LATERAL joins unlock query patterns that are either impossible or prohibitively slow with standard joins — particularly top-N per group, where every other approach either scans the whole table or generates N+1 queries. Our team uses LATERAL as the default for recent-record-per-row queries (latest order per customer, top event per workspace) when the index exists on the correlation column.

What we deliver:

• Top-N LATERAL: JOIN LATERAL (SELECT ... WHERE customer_id = c.id ORDER BY ... LIMIT N) o ON TRUE
• LEFT JOIN LATERAL for optional correlated data with NULL when no match
• unnest(tags) AS t(tag) expansion with LATERAL for array-to-rows with GIN index filter
• generate_series with LATERAL for date range expansion per project
• EXPLAIN comparison: window function vs LATERAL to demonstrate index scan benefit
• $queryRaw TypeScript helpers: getTopOrdersPerCustomer and getTagUsageStats

Talk to our team about your PostgreSQL query optimization →

Or explore our cloud and data engineering services.