Definition
- LATERAL – a SQL keyword that allows a subquery in the FROM clause to reference columns from the row to its left.
- Top-N per group – a pattern that retrieves the N highest (or lowest) rows per group without window functions.
- CROSS JOIN LATERAL – for each row in the left table, runs the LATERAL subquery; omits left rows with no results.
- LEFT JOIN LATERAL ... ON true – keeps left rows even when the LATERAL subquery returns no rows.
- Index optimization – a composite index on
(group_key, ranking_column DESC)turns each inner query into a short index range scan with LIMIT stop.
At National Bank of India, for each branch Pankaj wants the three highest-balance accounts. LATERAL walks branch by branch and, for each one, grabs that branch's three largest accounts. It is like visiting each branch manager and asking for their top three customers: you visit one branch, get three names, then move to the next branch.
Understanding the Concepts
What is LATERAL in SQL?
LATERAL allows a subquery in the FROM clause to reference columns from the row to its left. Without LATERAL, a subquery in FROM is independent and cannot see outer columns.
-- Without LATERAL: subquery cannot reference d.branch_id
-- SELECT * FROM branches d,
-- (SELECT * FROM accounts WHERE branch_id = d.branch_id) a;
-- ERROR: column d.branch_id does not exist in subquery
-- With LATERAL: subquery can reference d.branch_id
SELECT * FROM branches d
CROSS JOIN LATERAL (
SELECT * FROM accounts a
WHERE a.branch_id = d.branch_id
ORDER BY a.balance DESC
LIMIT 3
) top3;
How does LATERAL solve top-N per group?
For each branch row, the LATERAL subquery runs with that branch's ID, orders accounts by balance descending, and limits to 3. The result is the union across all branches.
-- Top-3 highest-balance accounts per branch at National Bank of India
SELECT
d.branch_name,
top3.account_id,
top3.holder_name,
top3.balance
FROM branches d
CROSS JOIN LATERAL (
SELECT a.account_id, a.holder_name, a.balance
FROM accounts a
WHERE a.branch_id = d.branch_id
AND a.status = 'ACTIVE'
ORDER BY a.balance DESC
LIMIT 3
) top3
ORDER BY d.branch_name, top3.balance DESC;
Result:
branch_name | account_id | holder_name | balance
DELHI | SAV2001 | Rajesh Kumar | 250000.00
DELHI | SAV2003 | Pankaj Singh | 180000.00
DELHI | SAV2002 | Priya Sharma | 150000.00
MUMBAI | CUR3001 | Amit Verma | 320000.00
MUMBAI | SAV3002 | Neha Gupta | 200000.00
MUMBAI | SAV3003 | Vikram Rao | 175000.00
What is the difference between CROSS JOIN LATERAL and LEFT JOIN LATERAL?
CROSS JOIN LATERAL omits branches that have no matching accounts. LEFT JOIN LATERAL ... ON true keeps those branches with NULL values for the account columns.
-- CROSS JOIN LATERAL: skip empty branches
SELECT d.branch_name, top3.account_id, top3.balance
FROM branches d
CROSS JOIN LATERAL (
SELECT a.account_id, a.balance
FROM accounts a
WHERE a.branch_id = d.branch_id
ORDER BY a.balance DESC
LIMIT 3
) top3;
-- Branches with no accounts are omitted
-- LEFT JOIN LATERAL: keep empty branches
SELECT d.branch_name, top3.account_id, top3.balance
FROM branches d
LEFT JOIN LATERAL (
SELECT a.account_id, a.balance
FROM accounts a
WHERE a.branch_id = d.branch_id
ORDER BY a.balance DESC
LIMIT 3
) top3 ON true;
-- Branches with no accounts appear with NULL account_id and balance
How does the ROW_NUMBER alternative work?
The window function approach partitions by group, ranks by balance, and filters to rank <= N.
-- Alternative: ROW_NUMBER window function
SELECT branch_name, account_id, holder_name, balance
FROM (
SELECT
b.branch_name,
a.account_id,
a.holder_name,
a.balance,
ROW_NUMBER() OVER (
PARTITION BY a.branch_id
ORDER BY a.balance DESC
) AS rn
FROM accounts a
JOIN branches b ON a.branch_id = b.branch_id
WHERE a.status = 'ACTIVE'
) ranked
WHERE rn <= 3
ORDER BY branch_name, balance DESC;
When does LATERAL outperform ROW_NUMBER?
LATERAL often wins when N is tiny and a composite index on (branch_id, balance DESC) exists. Each inner query becomes a short index range scan that stops after three rows. The ROW_NUMBER version must sort or scan the entire partition.
-- Create the composite index for LATERAL performance
CREATE INDEX idx_accounts_branch_balance
ON accounts (branch_id, balance DESC);
-- LATERAL: each inner query does:
-- Index Scan on idx_accounts_branch_balance
-- Starting at branch_id = 'DELHI', scan 3 rows, stop.
-- Very fast: 3 index lookups per branch.
-- ROW_NUMBER: must process entire partition
-- Sort all DELHI accounts by balance (could be thousands)
-- Then filter to rank <= 3
-- Slower when partitions are large and N is small.
Performance Comparison:
Scenario: 100 branches, 1M accounts, top-3 per branch
LATERAL + composite index:
100 branches × 3 index lookups = 300 index reads
Very fast
ROW_NUMBER:
1M rows sorted into 100 partitions
Each partition sorted by balance
Then filter to rank <= 3
Much more work
How does LATERAL work in MySQL and SQL Server?
MySQL 8+ supports LATERAL. SQL Server uses CROSS APPLY (equivalent to CROSS JOIN LATERAL) and OUTER APPLY (equivalent to LEFT JOIN LATERAL).
-- MySQL 8+: LATERAL
SELECT d.branch_name, top3.account_id, top3.balance
FROM branches d,
LATERAL (
SELECT a.account_id, a.balance
FROM accounts a
WHERE a.branch_id = d.branch_id
ORDER BY a.balance DESC
LIMIT 3
) top3;
-- SQL Server: CROSS APPLY
SELECT d.branch_name, top3.account_id, top3.balance
FROM branches d
CROSS APPLY (
SELECT TOP 3 a.account_id, a.balance
FROM accounts a
WHERE a.branch_id = d.branch_id
ORDER BY a.balance DESC
) top3;
-- SQL Server: OUTER APPLY (keeps empty branches)
SELECT d.branch_name, top3.account_id, top3.balance
FROM branches d
OUTER APPLY (
SELECT TOP 3 a.account_id, a.balance
FROM accounts a
WHERE a.branch_id = d.branch_id
ORDER BY a.balance DESC
) top3;
How do you use LATERAL from Java?
Rajesh calls the LATERAL query from Java to build a branch performance report.
package com.bank.repository;
import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
public class BranchTopAccountsRepository {
private static final Logger LOG = Logger.getLogger(
BranchTopAccountsRepository.class.getName());
private final Connection connection;
public BranchTopAccountsRepository(Connection connection) {
this.connection = connection;
}
public List<String> getTopNPerBranch(int topN) throws SQLException {
String sql = "SELECT d.branch_name, t.account_id, t.holder_name, t.balance "
+ "FROM branches d "
+ "CROSS JOIN LATERAL ("
+ " SELECT a.account_id, a.holder_name, a.balance "
+ " FROM accounts a "
+ " WHERE a.branch_id = d.branch_id "
+ " AND a.status = 'ACTIVE' "
+ " ORDER BY a.balance DESC "
+ " LIMIT ?"
+ ") t "
+ "ORDER BY d.branch_name, t.balance DESC";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = connection.prepareStatement(sql)) {
ps.setInt(1, topN);
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
String line = rs.getString("branch_name")
+ " | " + rs.getString("account_id")
+ " | " + rs.getString("holder_name")
+ " | " + rs.getBigDecimal("balance");
results.add(line);
}
}
}
LOG.info("Retrieved top-" + topN + " accounts per branch");
return results;
}
}
Output:
DELHI | SAV2001 | Rajesh Kumar | 250000.00
DELHI | SAV2003 | Pankaj Singh | 180000.00
DELHI | SAV2002 | Priya Sharma | 150000.00
MUMBAI | CUR3001 | Amit Verma | 320000.00
MUMBAI | SAV3002 | Neha Gupta | 200000.00
MUMBAI | SAV3003 | Vikram Rao | 175000.00
What should a senior engineer know?
A senior should know that the composite index (branch_id, balance DESC) is critical for LATERAL performance. Without it, each inner query does a sort. They should also know that LATERAL is more general than top-N: it can call functions, generate series, or perform any correlated computation per row.
-- Senior: verify the index is used with EXPLAIN
EXPLAIN (ANALYZE, BUFFERS)
SELECT d.branch_name, t.account_id, t.balance
FROM branches d
CROSS JOIN LATERAL (
SELECT a.account_id, a.balance
FROM accounts a
WHERE a.branch_id = d.branch_id
ORDER BY a.balance DESC
LIMIT 3
) t;
-- Look for: Index Scan using idx_accounts_branch_balance
Visual Overview
┌──────────────────────────────────────────────────────┐ │ LATERAL Top-N Per Group │ ├──────────────────────────────────────────────────────┤ │ │ │ branches LATERAL subquery │ │ ┌──────────┐ ┌──────────────────────────┐ │ │ │ DELHI │──────►│ SELECT * FROM accounts │ │ │ └──────────┘ │ WHERE branch_id = 'DELHI'│ │ │ │ ORDER BY balance DESC │ │ │ │ LIMIT 3 │ │ │ └──────────────────────────┘ │ │ Result: SAV2001, SAV2003, SAV2002│ │ │ │ ┌──────────┐ ┌──────────────────────────┐ │ │ │ MUMBAI │──────►│ SELECT * FROM accounts │ │ │ └──────────┘ │ WHERE branch_id = 'MUMBAI│ │ │ │ ORDER BY balance DESC │ │ │ │ LIMIT 3 │ │ │ └──────────────────────────┘ │ │ Result: CUR3001, SAV3002, SAV3003│ │ │ │ With index (branch_id, balance DESC): │ │ Each inner query = 3 index reads + stop │ │ Total: branches × 3 index reads │ │ │ │ CROSS JOIN LATERAL: skips empty groups │ │ LEFT JOIN LATERAL ON true: keeps empty groups │ └──────────────────────────────────────────────────────┘
Key Takeaway
To get the top N rows per group with LATERAL, you cross join the group table with a LATERAL subquery that filters to the current group's key, orders by the ranking column descending, and limits to N. Because the subquery references the group row to its left, the database runs it once per group and returns up to N rows for each, giving a clean top-N-per-group result without any window function.
At the National Bank of India, Pankaj used LATERAL to list each branch's three largest-balance accounts for a relationship-management report. With a composite index on (branch_id, balance DESC), each inner query became a short index range scan that stopped after three rows instead of sorting the entire partition. For the DELHI branch it returned SAV2001, SAV2003, and SAV2002 ranked by balance.
The ROW_NUMBER window function is the alternative approach: partition by group, rank by the column, and filter to rank <= N. LATERAL often wins when N is small and the composite index is available, because each inner query stops after N index reads. ROW_NUMBER must process the entire partition.
LATERAL is supported in PostgreSQL, MySQL 8+, and SQL Server (as CROSS APPLY and OUTER APPLY). Use CROSS JOIN LATERAL to skip empty groups and LEFT JOIN LATERAL ... ON true to keep groups with no matches.
Remember one sentence: loop the groups, and for each run ORDER BY the ranking column descending with LIMIT N.
---