postgres-syntax-lateral-joins

To get the top N posts per user without losing users who have no posts, use a LEFT JOIN LATERAL with a subquery that orders by a timestamp and limits results. This ensures outer rows are preserved when correlated subqueries return empty.

A LATERAL join in PostgreSQL enables dynamic subquery execution by allowing a subquery to reference columns from preceding tables. Use it for correlated data scenarios like computing top-N-per-group results efficiently while maintaining outer row visibility.

PostgreSQL 15, 16, and 17 support LATERAL joins for dynamic subqueries. These versions allow correlated subqueries to reference outer query columns, enabling per-row dynamic data retrieval and optimized join scenarios.

Your LATERAL join drops rows because an INNER LATERAL join excludes outer rows when the subquery returns no results. Use LEFT JOIN LATERAL instead to preserve outer rows even when correlated subqueries return empty result sets.

The best way to optimize correlated subqueries in PostgreSQL is using LATERAL joins, which allow per-row dynamic subquery execution. This approach avoids unnecessary complexity and handles top-N-per-group queries efficiently while preventing common pitfalls like losing outer rows.

You can use a LATERAL join to compute per-user aggregate data by running dynamic subqueries for each outer row. This is effective for scenarios like retrieving the 3 most recent posts per user, maintaining all users even with empty correlated results.