The essentials

Quick reference

One focused task per row. Jump to the related section for complete, working examples.

UseSyntaxExamples
Join a correlated subquerySELECT c.id, x.id FROM app.customers c CROSS JOIN LATERAL ( SELECT id FROM app.orders WHERE customer_id = c.id ) x;View examples
Return top one per rowSELECT c.id, x.id FROM app.customers c CROSS JOIN LATERAL ( SELECT id FROM app.orders WHERE customer_id = c.id ORDER BY created_at DESC LIMIT 1 ) x;View examples
Make lateral explicitSELECT t.id, f.value FROM app.things t CROSS JOIN LATERAL app.expand(t.payload) AS f(value);View examples
Preserve unmatched outer rowsSELECT c.id, x.id FROM app.customers c LEFT JOIN LATERAL ( SELECT id FROM app.orders WHERE customer_id = c.id LIMIT 1 ) x ON true;View examples
Filter after optional expansionSELECT c.id, x.id FROM app.customers c LEFT JOIN LATERAL app.lookup(c.id) x ON true WHERE x.id IS NULL;View examples
Generate integersSELECT n FROM generate_series(1, 10, 1) AS g(n);View examples
Generate daily timestampsSELECT ts FROM generate_series(timestamp '2026-08-01', timestamp '2026-08-07', interval '1 day') AS g(ts);View examples
Expand an arraySELECT value FROM unnest(ARRAY[10,20,30]) AS u(value);View examples
Add ordinalitySELECT value, position FROM unnest(ARRAY[10,20]) WITH ORDINALITY AS u(value, position);View examples
Zip arrays in FROMSELECT * FROM unnest(ARRAY[1,2], ARRAY['a','b']) AS u(id, label);View examples
Expand a JSON arraySELECT value FROM jsonb_array_elements('[1,2,3]'::jsonb) AS j(value);View examples
Project JSON objectsSELECT * FROM jsonb_to_recordset('[{"id":1}]'::jsonb) AS x(id integer);View examples
Declare estimated SRF rowsALTER FUNCTION app.recent_orders(bigint, integer) ROWS 10;View examples
Call a table functionSELECT * FROM app.recent_orders(42, 5);View examples
Inspect without executionEXPLAIN SELECT * FROM app.customers c CROSS JOIN LATERAL app.recent_orders(c.customer_id, 3) r;View examples
Measure lateral loopsEXPLAIN (ANALYZE, BUFFERS) SELECT * FROM app.customers c CROSS JOIN LATERAL app.recent_orders(c.customer_id, 3) r;View examples

LATERAL lets a FROM item refer to columns from items on its left, enabling parameterized subqueries evaluated per input row. Set-returning functions expand one value into rows; used deliberately in FROM with explicit aliases, they make cardinality and outer-row preservation much clearer than target-list expansion.

Step by step

Detailed examples

01

Use LATERAL for a parameterized FROM subquery

A LATERAL subquery may reference preceding FROM items and is evaluated for each qualifying left-side row. This enables per-row LIMIT and index-backed lookup patterns that an ordinary uncorrelated subquery cannot express. Join order is constrained by the dependency, so verify plans on large outer inputs.

Fetch the latest two orders per customer
SELECT c.customer_id, o.order_id, o.created_at
FROM app.customers c
CROSS JOIN LATERAL (
  SELECT order_id, created_at
  FROM app.orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY created_at DESC, order_id DESC
  LIMIT 2
) o
ORDER BY c.customer_id, o.created_at DESC;

Note: An index on orders(customer_id, created_at DESC, order_id DESC) supports the parameterized lookup.

Back to quick reference ↑
02

Use LEFT JOIN LATERAL to retain unmatched rows

CROSS JOIN LATERAL removes an outer row when the right side returns no rows. LEFT JOIN LATERAL with ON true preserves it and null-extends right-side columns, which is ideal for optional latest-child queries. Put correlation filters inside the lateral subquery so LIMIT applies to the intended set.

Keep customers without orders
SELECT c.customer_id, latest.order_id, latest.created_at
FROM app.customers c
LEFT JOIN LATERAL (
  SELECT o.order_id, o.created_at
  FROM app.orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY o.created_at DESC, o.order_id DESC
  LIMIT 1
) latest ON true;
Back to quick reference ↑
03

Generate bounded rows with explicit types

generate_series supports integer, numeric, timestamp, and timezone-aware timestamptz forms. Inclusive endpoints and step sign affect results; zero step is an error and NULL input returns no rows. Bound series generation before joining because row counts multiply quickly.

Build a gap-filled daily report
SELECT d.day, coalesce(sum(o.total), 0) AS revenue
FROM generate_series(date '2026-08-01', date '2026-08-07', interval '1 day') AS d(day)
LEFT JOIN app.orders o ON o.created_at >= d.day AND o.created_at < d.day + interval '1 day'
GROUP BY d.day ORDER BY d.day;
Back to quick reference ↑
04

Expand arrays with position and aligned semantics

unnest expands array elements in storage order. WITH ORDINALITY adds a one-based row number independent of the array lower bound, useful for reconstructing presentation order. Multiple arrays in FROM-function syntax are padded with NULL to the longest input, so validate lengths when pairwise alignment is required.

Expand tags while retaining order
SELECT p.product_id, tag.value, tag.position
FROM app.products p
CROSS JOIN LATERAL unnest(p.tags) WITH ORDINALITY AS tag(value, position)
ORDER BY p.product_id, tag.position;
Back to quick reference ↑
05

Expand JSON with declared relational types

jsonb_array_elements returns raw JSON values, while jsonb_to_recordset projects object fields into explicitly declared SQL types. Missing fields become NULL and incompatible values raise errors. Treat untrusted payloads as validation input, cap document size, and use LEFT JOIN LATERAL when empty arrays should not discard the parent.

Project JSON line items into typed columns
SELECT o.order_id, item.sku, item.qty
FROM app.orders o
CROSS JOIN LATERAL jsonb_to_recordset(o.items) AS item(sku text, qty integer)
WHERE item.qty > 0;

Note: Validate object shape at ingestion if conversion errors must not abort reporting queries.

Back to quick reference ↑
06

Declare custom SRF cardinality and volatility honestly

Functions returning SETOF or TABLE are usable as FROM items. ROWS supplies the planner an expected row count and COST estimates per returned row for set-returning functions; inaccurate values distort join choices. Volatility, leakproofness, security-definer search paths, and parallel safety must reflect actual behavior.

Declare a bounded SQL table function
CREATE FUNCTION app.recent_orders(p_customer bigint, p_limit integer DEFAULT 10)
RETURNS TABLE(order_id bigint, created_at timestamptz)
LANGUAGE sql STABLE PARALLEL SAFE ROWS 10
AS $$
  SELECT o.order_id, o.created_at
  FROM app.orders o
  WHERE o.customer_id = p_customer
  ORDER BY o.created_at DESC
  LIMIT p_limit
$$;

Note: Clamp p_limit in trusted code if callers can request unbounded results; audit PARALLEL SAFE before using it.

Back to quick reference ↑
07

Control cardinality before per-row work becomes expensive

A lateral plan can execute its inner side once per outer row, although the planner may memoize or transform safe cases. Use EXPLAIN ANALYZE carefully to check loops and actual rows, and filter the outer relation early. Read queries take ordinary ACCESS SHARE locks; functions that write, lock rows, or call volatile code add their own transaction and replication consequences.

Inspect loops in a top-N lateral plan
EXPLAIN (ANALYZE, BUFFERS)
SELECT c.customer_id, x.order_id
FROM app.customers c
CROSS JOIN LATERAL (
  SELECT order_id FROM app.orders o
  WHERE o.customer_id = c.customer_id
  ORDER BY created_at DESC LIMIT 3
) x
WHERE c.active;

Note: The inner node loops often match qualifying customers; test against production-scale cardinalities.

Back to quick reference ↑

Keep exploring

More SQL cheat sheets

Sources and further reading

References

Authoritative documentation used to verify and expand this cheat sheet.

  1. PostgreSQL Global Development GroupTable Expressionspostgresql.org
  2. PostgreSQL Global Development GroupSELECTpostgresql.org
  3. PostgreSQL Global Development GroupSet Returning Functionspostgresql.org
  4. PostgreSQL Global Development GroupArray Functions and Operatorspostgresql.org
  5. PostgreSQL Global Development GroupJSON Functions and Operatorspostgresql.org
  6. PostgreSQL Global Development GroupCREATE FUNCTIONpostgresql.org
  7. PostgreSQL Global Development GroupEXPLAINpostgresql.org

Help us improve

Found a typo or missing example?

Tell us what would make this cheat sheet clearer, more complete, or more useful.

Share feedback