oracle lateral

Oracle Lateral is a feature introduced in Oracle 12c that allows you to reference columns from a table or subquery that appear later in the SQL statement. This allows you to write more efficient queries that can avoid the need for subqueries or self-joins.

The syntax for using Lateral in Oracle is as follows:

SELECT *
FROM table1
LATERAL (SELECT column2 FROM table2 WHERE table2.column1 = table1.column1)

In this example, the Lateral clause is used to reference a column from table2 that is related to a column in table1. This eliminates the need for a separate subquery or join.

Lateral can also be used with subqueries, allowing you to reference columns from a subquery that appears later in the SQL statement. This can be useful for complex queries that involve multiple subqueries or joins.