Lateral Join

Definitions

1. A SQL join where the right-hand subquery can reference columns from the left-hand table — like a correlated subquery but returning multiple rows/columns. Enables 'for each row, run this query' patterns. PostgreSQL uses LATERAL, MySQL 8+ uses LATERAL, and SQL Server uses CROSS APPLY / OUTER APPLY.

   In plain English: A SQL join where the subquery can use values from the outer table, enabling 'for each X, get the top N of Y' queries.

   Example: "SELECT u.*, recent.* FROM users u, LATERAL (SELECT * FROM orders WHERE user_id = u.id ORDER BY created DESC LIMIT 3) recent — top 3 orders per user."

Related Terms

• stored procedure
• trigger
• Clause
• Database
• Dialect
• LINQ
• CTE
• Window Function