Lateral Join vs Subquery in PostgreSQL: Key Differences and Usage
LATERAL JOIN allows a subquery to reference columns from preceding tables in the FROM clause, enabling row-wise operations. A subquery is a nested query that runs independently and cannot directly access outer query columns unless correlated. LATERAL joins are more flexible for complex row-by-row computations compared to standard subqueries.Quick Comparison
This table summarizes the main differences between LATERAL JOIN and subquery in PostgreSQL.
| Feature | LATERAL JOIN | Subquery |
|---|---|---|
| Access to outer query columns | Yes, can reference columns from preceding tables | Only if correlated; otherwise no access |
| Execution style | Row-by-row, for each row of the left table | Runs once or per row if correlated |
| Use case | Complex row-wise computations and joins | Filtering, aggregation, or simple nested queries |
| Syntax placement | Used in FROM clause with JOIN | Used in SELECT, WHERE, or FROM clauses |
| Performance | Often more efficient for dependent subqueries | Can be less efficient if not optimized |
Key Differences
LATERAL JOIN is a special kind of join that lets a subquery access columns from tables that appear earlier in the FROM clause. This means it can perform calculations or fetch related rows for each row of the left table individually, making it very powerful for row-wise operations.
In contrast, a subquery is a query nested inside another query. It can be correlated, meaning it refers to outer query columns, but it runs independently otherwise. Subqueries are often used for filtering or aggregation but lack the flexibility of lateral joins for complex row-by-row logic.
Because LATERAL executes the subquery for each row of the left table, it can be more efficient and expressive when you need to join with a set of rows that depend on each row of the main table. Subqueries, especially uncorrelated ones, run once and return a result used by the outer query.
Code Comparison
Example: For each customer, find their latest order using LATERAL JOIN.
SELECT c.customer_id, c.customer_name, o.order_id, o.order_date FROM customers c JOIN LATERAL ( SELECT order_id, order_date FROM orders WHERE orders.customer_id = c.customer_id ORDER BY order_date DESC LIMIT 1 ) o ON true;
Subquery Equivalent
Using a correlated subquery in the SELECT clause to get the latest order per customer.
SELECT c.customer_id, c.customer_name, (SELECT o.order_id FROM orders o WHERE o.customer_id = c.customer_id ORDER BY o.order_date DESC LIMIT 1) AS latest_order_id, (SELECT o.order_date FROM orders o WHERE o.customer_id = c.customer_id ORDER BY o.order_date DESC LIMIT 1) AS latest_order_date FROM customers c;
When to Use Which
Choose LATERAL JOIN when you need to join each row of a table with a set of rows or computations that depend on that row, especially for complex or multiple columns. It is ideal for row-wise operations and can improve readability and performance.
Use correlated subqueries when you want to fetch single values related to each row, especially in SELECT or WHERE clauses, and the logic is simple. For independent queries or simple filters, standard subqueries work well.