Definition
- LATERAL – A PostgreSQL keyword allowing a subquery in the FROM clause to reference columns of tables listed before it in the same FROM.
- CROSS APPLY – SQL Server's equivalent of CROSS JOIN LATERAL: drops outer rows whose subquery returns no rows.
- OUTER APPLY – SQL Server's equivalent of LEFT JOIN LATERAL: keeps outer rows with NULLs when the subquery returns nothing.
- Per-row execution – The subquery runs once per outer row, enabling parameterized queries per row.
- Top-N per group – A clean pattern: ORDER BY ... LIMIT N inside the LATERAL subquery, one result set per group.
- Table-valued function – Calling a function per row that returns a set, e.g.,
LATERAL compute_fees(a.id). - Indexing requirement – The correlation column must be indexed or the subquery degrades to a scan per outer row.
Normally a subquery in FROM is sealed off and cannot see the other tables. LATERAL unlocks a little window so the inner query can peek at the current outer row. For each National Bank of India account, Rajesh wants "that account's three latest transactions." LATERAL lets him pass the current account's ID into an inner query that fetches exactly its three latest rows – one small lookup per account, not a full window-function scan.
Understanding the Concepts
What is LATERAL?
Normally, a subquery in the FROM clause is independent and cannot reference other tables in the same FROM. With the LATERAL keyword, the subquery can reference columns from tables listed before it. This means the subquery runs once per outer row, parameterized by that row's values.
-- Without LATERAL: subquery cannot see 'a'
SELECT * FROM accounts a, (SELECT * FROM transactions) sub;
-- 'sub' has no access to a.account_id
-- With LATERAL: subquery CAN see 'a'
SELECT a.account_id, a.holder_name, recent.*
FROM accounts a,
LATERAL (
SELECT t.txn_id, t.amount, t.txn_date
FROM transactions t
WHERE t.account_id = a.account_id -- references outer 'a'
ORDER BY t.txn_date DESC
LIMIT 3
) recent;
Expected output:
account_id | holder_name | txn_id | amount | txn_date
------------+--------------+---------+-----------+------------
SAV2001 | Rajesh Kumar | TXN-042 | 50000.00 | 2024-01-15
SAV2001 | Rajesh Kumar | TXN-038 | 25000.00 | 2024-01-10
SAV2001 | Rajesh Kumar | TXN-035 | 15000.00 | 2024-01-05
CUR3001 | Priya Sharma | TXN-041 | 75000.00 | 2024-01-14
CUR3001 | Priya Sharma | TXN-039 | 30000.00 | 2024-01-12
CUR3001 | Priya Sharma | TXN-036 | 20000.00 | 2024-01-08
How is LATERAL different from a regular subquery in FROM?
| Aspect | Regular subquery in FROM | LATERAL subquery |
|---|---|---|
| Can see outer tables | No | Yes |
| Execution | Once, independently | Once per outer row |
| Use case | Independent derived table | Per-row parameterized query |
| PostgreSQL syntax | FROM a, (SELECT ...) sub | FROM a, LATERAL (SELECT ... WHERE t.id = a.id) sub |
What is CROSS APPLY vs OUTER APPLY?
CROSS APPLY is SQL Server's LATERAL with inner-join semantics: outer rows whose subquery returns nothing are dropped. OUTER APPLY is left-join semantics: outer rows are kept with NULLs when the subquery returns nothing.
-- PostgreSQL: CROSS JOIN LATERAL (drops empty accounts)
SELECT a.account_id, recent.*
FROM accounts a
CROSS JOIN LATERAL (
SELECT t.txn_id, t.amount
FROM transactions t
WHERE t.account_id = a.account_id
ORDER BY t.txn_date DESC
LIMIT 3
) recent;
-- Accounts with no transactions are NOT in the result
-- PostgreSQL: LEFT JOIN LATERAL (keeps empty accounts)
SELECT a.account_id, a.holder_name, recent.txn_id, recent.amount
FROM accounts a
LEFT JOIN LATERAL (
SELECT t.txn_id, t.amount
FROM transactions t
WHERE t.account_id = a.account_id
ORDER BY t.txn_date DESC
LIMIT 3
) recent ON true;
-- Accounts with no transactions show NULL for txn columns
-- SQL Server equivalents
-- CROSS APPLY = CROSS JOIN LATERAL
SELECT a.account_id, recent.*
FROM accounts a
CROSS APPLY (
SELECT TOP 3 t.txn_id, t.amount
FROM transactions t
WHERE t.account_id = a.account_id
ORDER BY t.txn_date DESC
) recent;
-- OUTER APPLY = LEFT JOIN LATERAL
SELECT a.account_id, recent.*
FROM accounts a
OUTER APPLY (
SELECT TOP 3 t.txn_id, t.amount
FROM transactions t
WHERE t.account_id = a.account_id
ORDER BY t.txn_date DESC
) recent;
How does LATERAL solve top-N per group?
Top-N per group is clean with LATERAL: the inner query simply uses ORDER BY ... LIMIT N, and the outer table defines the groups.
-- Top 3 transactions per account (clean LATERAL solution)
SELECT a.account_id, a.holder_name,
recent.txn_id, recent.amount, recent.txn_date
FROM accounts a
LEFT JOIN LATERAL (
SELECT t.txn_id, t.amount, t.txn_date
FROM transactions t
WHERE t.account_id = a.account_id
ORDER BY t.txn_date DESC
LIMIT 3
) recent ON true
ORDER BY a.account_id, recent.txn_date DESC;
Compare with window function approach:
SELECT * FROM (
SELECT t.*, ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY txn_date DESC) rn
FROM transactions t
) sub WHERE rn <= 3;
-- Window function computes rank for ALL rows, then filters.
-- LATERAL fetches exactly 3 per account. More efficient with an index.
How do you call a table-valued function per row?
LATERAL is how you call a function that returns a set, parameterized by each outer row's values.
-- Call a fee-calculation function per account
SELECT a.account_id, a.holder_name, fees.*
FROM accounts a
LEFT JOIN LATERAL compute_monthly_fees(a.account_id, '2024-01') fees ON true;
What about indexing?
Because LATERAL runs per outer row, the correlation column must be indexed. Without the index, each outer row triggers a sequential scan of the inner table.
-- Critical: composite index for efficient per-account lookups
CREATE INDEX idx_txn_acct_date
ON transactions (account_id, txn_date DESC);
-- Now each LATERAL subquery does an index scan: fast.
How do you implement this in Java?
package com.bank.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class RecentTransactionService {
private static final String SQL = """
SELECT a.account_id, a.holder_name,
recent.txn_id, recent.amount, recent.txn_date
FROM accounts a
LEFT JOIN LATERAL (
SELECT t.txn_id, t.amount, t.txn_date
FROM transactions t
WHERE t.account_id = a.account_id
ORDER BY t.txn_date DESC
LIMIT 3
) recent ON true
WHERE a.customer_id = ?
ORDER BY a.account_id, recent.txn_date DESC
""";
public List<String> getRecentTransactions(Connection conn,
long customerId) throws SQLException {
List<String> results = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(SQL)) {
ps.setLong(1, customerId);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String txnId = rs.getString("txn_id");
if (txnId == null) {
results.add(rs.getString("account_id") + ": no transactions");
} else {
results.add(rs.getString("account_id") + ": "
+ txnId + " Rs." + rs.getBigDecimal("amount"));
}
}
}
}
return results;
}
}
What should a senior engineer know?
A senior engineer knows that LATERAL runs per outer row, that the correlation column must be indexed, that LEFT JOIN LATERAL ON true is the PostgreSQL equivalent of OUTER APPLY, and that LATERAL is often more efficient than window functions for top-N per group because it fetches exactly N rows per group from the index rather than ranking all rows first.
Visual Overview
┌─────────────────────────────────────────────────────┐ │ LATERAL Join: Per-Row Subquery Execution │ ├─────────────────────────────────────────────────────┤ │ │ │ accounts LATERAL subquery │ │ ┌──────────┐ ┌──────────────────────────┐ │ │ │ SAV2001 │──→ │ SELECT FROM txns │ │ │ │ │ │ WHERE acct = SAV2001 │ │ │ │ │ │ ORDER BY date DESC LIMIT 3 │ │ │ │ │ │ → TXN-042, TXN-038, TXN-035│ │ │ └──────────┘ └──────────────────────────┘ │ │ │ │ ┌──────────┐ ┌──────────────────────────┐ │ │ │ CUR3001 │──→ │ SELECT FROM txns │ │ │ │ │ │ WHERE acct = CUR3001 │ │ │ │ │ │ ORDER BY date DESC LIMIT 3 │ │ │ │ │ │ → TXN-041, TXN-039, TXN-036│ │ │ └──────────┘ └──────────────────────────┘ │ │ │ │ ┌──────────┐ ┌──────────────────────────┐ │ │ │ FD4001 │──→ │ SELECT FROM txns │ │ │ │ │ │ WHERE acct = FD4001 │ │ │ │ │ │ ORDER BY date DESC LIMIT 3 │ │ │ │ │ │ → (empty: no transactions) │ │ │ └──────────┘ └──────────────────────────┘ │ │ │ │ CROSS JOIN LATERAL: FD4001 dropped (no results) │ │ LEFT JOIN LATERAL ON true: FD4001 kept (NULLs) │ │ │ │ Index required: txns(account_id, txn_date DESC) │ │ │ └─────────────────────────────────────────────────────┘
Key Takeaway
A LATERAL join in PostgreSQL – called CROSS APPLY and OUTER APPLY in SQL Server – is a subquery placed in the FROM clause that can reference columns from the tables listed before it. Because of that reference, the database executes the subquery once per outer row, effectively running a parameterized query for each row. CROSS APPLY (CROSS JOIN LATERAL) drops outer rows whose subquery returns nothing, while OUTER APPLY (LEFT JOIN LATERAL ON true) keeps them with NULLs.
The two situations where Rajesh reaches for LATERAL at National Bank of India are top-N per group and calling a table-valued function per row. For the home screen showing each account's three most recent transactions, LATERAL lets him write a simple inner query that filters by the current account ID, orders by timestamp descending, and limits to three. The database fetches exactly fifteen rows for five accounts instead of ranking all transactions with a window function.
Because the subquery runs per outer row, the correlation column must be indexed. A composite index on (account_id, txn_date DESC) ensures each LATERAL subquery does an efficient index scan rather than a full table scan. Without the index, LATERAL degrades to a sequential scan per outer row, which can be worse than the window function approach.
Remember one sentence: LATERAL is a FROM-clause subquery that can see the row to its left, so it runs per row – perfect for top-N per group and per-row function calls.
---