How to Use Lateral Join in PostgreSQL: Syntax and Examples

In PostgreSQL, a LATERAL join allows a subquery in the FROM clause to reference columns from preceding tables in the same FROM clause. Use LATERAL before a subquery or function to enable this row-by-row correlation and join dynamic results.

Syntax

The LATERAL join syntax lets a subquery or function access columns from tables listed before it in the FROM clause.

Basic syntax:

• FROM table1: The first table in the query.
• LATERAL (subquery or function): The lateral subquery or function that can use columns from table1.
• JOIN or LEFT JOIN: Specifies how to join the lateral result with the first table.

sql

SELECT t1.id, t2.val
FROM table1 t1
JOIN LATERAL (
  SELECT val FROM table2 WHERE table2.ref_id = t1.id LIMIT 1
) t2 ON true;

Example

This example shows how to use LATERAL to get the first matching value from table2 for each row in table1.

sql

CREATE TEMP TABLE table1 (id INT);
CREATE TEMP TABLE table2 (ref_id INT, val TEXT);

INSERT INTO table1 VALUES (1), (2), (3);
INSERT INTO table2 VALUES (1, 'a'), (1, 'b'), (2, 'c');

SELECT t1.id, t2.val
FROM table1 t1
LEFT JOIN LATERAL (
  SELECT val FROM table2 WHERE table2.ref_id = t1.id LIMIT 1
) t2 ON true
ORDER BY t1.id;

Output

 id | val 
----+-----
  1 | a
  2 | c
  3 | 
(3 rows)

Common Pitfalls

Common mistakes when using LATERAL include:

• Forgetting to write LATERAL before the subquery or function, which causes errors or unexpected results.
• Using LATERAL without a proper join condition, which can lead to a cross join effect.
• Assuming LATERAL works like a normal join; it runs the subquery for each row, which can impact performance if not used carefully.

sql

/* Wrong: missing LATERAL keyword */
SELECT t1.id, t2.val
FROM table1 t1
JOIN (
  SELECT val FROM table2 WHERE table2.ref_id = t1.id LIMIT 1
) t2 ON true;

/* Right: with LATERAL keyword */
SELECT t1.id, t2.val
FROM table1 t1
JOIN LATERAL (
  SELECT val FROM table2 WHERE table2.ref_id = t1.id LIMIT 1
) t2 ON true;

Quick Reference

Concept	Description
LATERAL	Allows subquery to reference preceding tables in FROM clause
JOIN LATERAL	Joins each row of the first table with the subquery result per row
LEFT JOIN LATERAL	Includes all rows from first table, with NULLs if subquery returns no rows
Use case	When you need row-by-row subquery results depending on outer table columns
Performance	Can be slower if subquery is complex or large; use indexes and LIMIT

Key Takeaways

Use LATERAL to let subqueries access columns from tables earlier in the FROM clause.

Always write LATERAL before the subquery or function to enable row-wise correlation.

Use JOIN LATERAL or LEFT JOIN LATERAL depending on whether you want to keep all rows.

Be careful with performance as lateral subqueries run once per row of the outer table.

Common errors include forgetting LATERAL or missing join conditions.