Fixing PostgreSQL LATERAL JOIN That Returns No Rows When Subquery References Outer Column

You write a LATERAL JOIN, the query runs without errors, but the result set is completely empty. You run the subquery on its own with a hardcoded value and it returns rows just fine. Something about the outer column reference is breaking the join — and PostgreSQL gives you no hint about what went wrong.

This is one of the more confusing failure modes in PostgreSQL because the query is syntactically valid and logically plausible. The bug is subtle: it lives in how LATERAL passes rows, how INNER vs LEFT join semantics interact, and how easy it is to introduce a correlation condition that never matches.

What LATERAL JOIN Actually Does

A standard subquery in a FROM clause cannot see columns from other tables listed in the same FROM. LATERAL changes that: it evaluates the subquery once per row of the preceding table, passing each row's column values into the subquery as if they were parameters.

Think of it like a correlated subquery in the SELECT list, but one that can return multiple columns and multiple rows, and can be joined like any other table. That row-by-row evaluation is exactly what makes the outer-column reference work — and exactly what can silently produce no rows when something is off.

A Minimal Reproduction Case

Here is a concrete setup to anchor the rest of the article. Suppose you have two tables: users and orders.

CREATE TABLE users (
  id        SERIAL PRIMARY KEY,
  name      TEXT NOT NULL
);

CREATE TABLE orders (
  id         SERIAL PRIMARY KEY,
  user_id    INT NOT NULL,
  amount     NUMERIC(10,2),
  created_at TIMESTAMPTZ DEFAULT now()
);

INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Carol');

INSERT INTO orders (user_id, amount) VALUES
  (1, 49.99),
  (1, 120.00),
  (2, 75.50);
-- Carol (id=3) has no orders

Now you want the most recent order for each user:

SELECT
  u.id,
  u.name,
  latest.amount,
  latest.created_at
FROM users u
JOIN LATERAL (
  SELECT amount, created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) AS latest ON true;

This returns two rows (Alice and Bob). Carol is missing. If your whole result is empty, the problem is likely one of the causes described below.

Why the Subquery Returns No Rows

The LATERAL subquery runs for each outer row. When it finds no matching rows in orders, it returns an empty set. What happens next depends entirely on whether you used JOIN LATERAL (which is INNER JOIN LATERAL) or LEFT JOIN LATERAL.

With an inner join, a zero-row subquery result causes that outer row to be dropped from the final output — exactly like a regular inner join against an empty table. With a left join, the outer row is kept and the subquery columns are filled with NULL. This is the single most common reason a LATERAL JOIN appears to return no rows: every outer row produces an empty subquery, and the inner join semantics discard all of them.

The Most Common Cause: INNER JOIN LATERAL With No Matches

The keyword JOIN alone is syntactic sugar for INNER JOIN. When you write:

FROM users u
JOIN LATERAL (...) AS sub ON true

...PostgreSQL will drop any outer row for which the lateral subquery returns zero rows, regardless of the ON true condition. The ON true is not a fallback — it only controls whether matched rows pass through, not what happens when there are no rows to match.

A complete wipeout (zero rows total) usually means all outer rows produce empty subqueries. Common reasons:

• The correlation column names are wrong (typo or wrong table alias).
• The data types of the join condition don't match (e.g., TEXT vs INT), causing an implicit cast that never equals anything.
• The subquery has an additional WHERE filter that eliminates all rows before the LIMIT.
• The outer table itself is empty, which means the lateral never runs at all.

This is closely related to the silent-failure pattern covered in the article on fixing PostgreSQL UPDATE FROM that modifies the wrong rows on a join — a mismatch in join conditions that PostgreSQL happily accepts but that doesn't do what you intended.

Fix 1: Switch to LEFT JOIN LATERAL

This is the right fix when you want to preserve outer rows that have no matching subquery results (the same logic as a regular LEFT JOIN).

SELECT
  u.id,
  u.name,
  latest.amount,
  latest.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT amount, created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1
) AS latest ON true;

Now Carol appears in the output with NULL for amount and created_at. If your query was returning some rows but dropping others, this is almost certainly the fix you need. If it was returning zero rows, switching to LEFT JOIN LATERAL will at least return the outer rows — which tells you the subquery correlation is broken, not the join type.

Fix 2: Add a COALESCE or Default Row

Sometimes you don't want NULLs — you want a default value when the subquery has no match. You can handle this inside the subquery itself using UNION ALL with a sentinel row, then take the first result:

SELECT
  u.id,
  u.name,
  latest.amount,
  latest.created_at
FROM users u
LEFT JOIN LATERAL (
  SELECT amount, created_at
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY created_at DESC
  LIMIT 1

  UNION ALL

  SELECT 0.00, NULL  -- default when no orders exist
  LIMIT 1
) AS latest ON true;

The outer LIMIT 1 on the UNION ALL is implicit here because of how the first branch works, but you can also wrap the whole thing to be explicit. Alternatively, apply COALESCE in the outer SELECT:

SELECT
  u.id,
  u.name,
  COALESCE(latest.amount, 0.00) AS amount
FROM users u
LEFT JOIN LATERAL (...) AS latest ON true;

Use whichever form keeps the query easier to read for your team.

Fix 3: Check the Correlation Condition Itself

If switching to LEFT JOIN LATERAL gives you rows for every outer row but all the subquery columns are NULL, the correlation condition is the problem. The subquery is running but never matching anything.

Start by auditing the WHERE clause inside the lateral subquery:

• Alias collisions: If both the outer query and the subquery reference a table named o, PostgreSQL will use the innermost alias. Double-check that o.user_id and u.id really point to the tables you think they do.
• Type mismatches: Run \d users and \d orders in psql to confirm that u.id and o.user_id are the same type. An INT4 compared to a BIGINT or TEXT can silently produce zero matches in some edge cases.
• Schema prefix: If you're querying across schemas, make sure the subquery resolves the correct search_path table.

A quick sanity check is to replace the correlation with a hardcoded value that you know exists:

-- Replace u.id with a literal to test the subquery
JOIN LATERAL (
  SELECT amount, created_at
  FROM orders o
  WHERE o.user_id = 1   -- hardcoded for debugging
  ORDER BY created_at DESC
  LIMIT 1
) AS latest ON true;

If that returns rows but the correlated version does not, the outer column reference is broken. Work backwards from there.

Fix 4: Validate the Subquery in Isolation

PostgreSQL lets you run the lateral subquery as a standalone query by manually passing the outer column value. This is the fastest way to rule out a logic error inside the subquery itself:

-- Simulate what the lateral would receive for user id=1
SELECT amount, created_at
FROM orders o
WHERE o.user_id = 1
ORDER BY created_at DESC
LIMIT 1;

If this returns nothing, the problem is in your data or the subquery logic, not the LATERAL syntax. If this returns rows but the full query doesn't, the correlation isn't being passed correctly.

You can also use EXPLAIN ANALYZE on the full lateral query to see how many rows the subquery returns for each outer row. Look for lines like Rows Removed by Filter or a Limit node showing rows=0. For a detailed walkthrough of reading those plans, see the article on fixing PostgreSQL EXPLAIN ANALYZE that shows a seq scan despite an index.

One more thing to check: if your subquery contains a GROUP BY or aggregate, make sure the aggregate doesn't swallow all rows into a single NULL group when there's no data. An aggregate over zero rows returns one row with a NULL result — which does satisfy an inner join — so in that case you'll get rows, but they'll carry unexpected NULLs.

Common Pitfalls to Watch For

A few patterns repeatedly cause LATERAL JOIN headaches beyond the main fixes above.

Forgetting ON true

When you use JOIN LATERAL or LEFT JOIN LATERAL, you must supply a join condition. Since the filtering already happens inside the subquery, the standard idiom is ON true. Omitting it is a syntax error. Adding a real condition (like ON latest.amount > 0) can silently drop rows for reasons unrelated to the outer column reference.

LATERAL with CROSS JOIN

You can also write CROSS JOIN LATERAL, which implicitly uses inner-join semantics and drops outer rows with empty subquery results — same as JOIN LATERAL. There's no LEFT CROSS JOIN LATERAL; use LEFT JOIN LATERAL ... ON true if you need to preserve non-matching rows.

Ordering Without a Tiebreaker

If you're using ORDER BY created_at DESC LIMIT 1 to get the latest row, make sure the ordering column has no ties — or add a secondary sort (e.g., ORDER BY created_at DESC, id DESC) to get deterministic results. A non-deterministic LIMIT 1 won't cause missing rows, but it will cause subtly wrong ones.

CTE Inside LATERAL

Placing a CTE inside a lateral subquery is valid SQL but can confuse the planner. PostgreSQL may materialize the CTE before the outer row is passed in, which in older versions means the outer column reference is not visible. This is closely related to the CTE materialization problem described in the article on fixing a PostgreSQL CTE that materializes when you need it inline. If you need complex logic inside the lateral, prefer a subquery or a WITH clause outside the lateral.

NULL Values in the Outer Column

If u.id is somehow NULL for some rows (unusual for a primary key, but possible with outer queries or derived tables), the condition o.user_id = u.id will never match because NULL = NULL is NULL, not TRUE. Use IS NOT DISTINCT FROM if you need NULL-safe equality, or filter out nulls in the outer query first.

Performance: Index the Correlation Column

Because the lateral subquery runs once per outer row, the correlation column in the inner table should be indexed. Without an index on orders.user_id, a large users table triggers a seq scan on orders for every user. Add the index if you're seeing slow query times:

CREATE INDEX idx_orders_user_id ON orders (user_id);

Wrapping Up

A LATERAL JOIN that returns no rows is almost always one of two problems: inner-join semantics discarding outer rows that have no subquery matches, or a broken correlation condition that never finds a match in the first place. Here are the concrete steps to resolve it:

1. Switch to LEFT JOIN LATERAL ... ON true as your first move. If rows appear with NULL subquery columns, the correlation is broken. If all expected rows appear correctly, you're done.
2. Run the subquery in isolation with a hardcoded outer value to confirm it returns rows on its own.
3. Inspect column types and aliases inside the subquery — type mismatches and alias shadows are the most common correlation bugs.
4. Use EXPLAIN ANALYZE on the full query to see exactly how many rows the lateral subquery emits per outer row.
5. Add an index on the correlation column in the inner table once correctness is confirmed, especially if the outer table is large.

If your query involves complex joins elsewhere in the same statement, also check that the outer table itself isn't being filtered down to zero rows before the lateral is reached. A silent filter upstream can make the lateral look broken when the real issue is upstream — a pattern worth keeping in mind any time you debug unexpected empty results in PostgreSQL.