LATERAL JOIN

In Oracle SQL, a LATERAL JOIN is a type of join that allows a subquery in the FROM clause to reference columns from the outer query. It provides a way to correlate a subquery with the outer query, enabling the subquery to be evaluated once for each row in the outer query. This is particularly useful when you want to perform a complex calculation or use a set of rows that depends on the outer query's data.

A LATERAL join enables you to use a subquery that refers to the columns of the outer query, which would not normally be possible in a standard subquery.

Key Points of LATERAL JOIN:

1.     **Purpose of LATERAL:

• The LATERAL join allows a correlated subquery to be part of the FROM clause.
• It can reference columns from the outer query, which is not possible with regular subqueries in the SELECT clause.

2.     Basic Usage:

• LATERAL enables the subquery to act like a table that is re-evaluated for each row of the outer query.
• It is particularly useful when you need to use a function or a derived table that requires knowledge of the outer query's row context.

3.     Syntax:

The syntax for using a LATERAL JOIN is:

SELECT column_list

FROM table1 t1

CROSS JOIN LATERAL (subquery) subquery_alias;

• table1: The outer table.
• subquery: A subquery that is evaluated once per row of the outer table.
• subquery_alias: An alias for the subquery that is referenced in the query.

You can also use LATERAL with joins other than CROSS JOIN, such as INNER JOIN or LEFT JOIN.

SELECT column_list

FROM table1 t1

LEFT JOIN LATERAL (subquery) subquery_alias;

4.     Use with CROSS JOIN: The CROSS JOIN LATERAL is the most common way to use LATERAL, where the subquery is evaluated for each row of the outer table.

5.  SELECT a.column1, b.column2

6.  FROM table1 a

7.  CROSS JOIN LATERAL (SELECT column2 FROM table2 b WHERE b.column1 = a.column1);

In this example, for each row in table1, the subquery evaluates rows from table2 where the column1 values match. The result is a Cartesian product, but the subquery is correlated to the outer query.

8.     Use with INNER JOIN or LEFT JOIN: You can also use LATERAL with joins like INNER JOIN or LEFT JOIN to bring in data from the subquery.

Example with LEFT JOIN LATERAL:

SELECT a.column1, b.column2

FROM table1 a

LEFT JOIN LATERAL (SELECT column2 FROM table2 b WHERE b.column1 = a.column1) b ON 1=1;

In this case, if no match is found in table2, a NULL value is returned for column2.

9.     **When to Use LATERAL: You might use LATERAL in the following situations:

• When you need to perform a calculation that depends on the data from the outer query, such as aggregating or filtering based on each row's values.
• When you need to work with a table-valued function or an inline view that uses data from the outer query.
• To enable row-by-row processing in a query where the subquery needs access to the outer query’s columns.

10.  Important Considerations:

• LATERAL makes subqueries more flexible and allows for row-by-row processing, but it should be used judiciously because it can sometimes lead to performance overhead.
• LATERAL is often used in conjunction with set-returning functions, such as TABLE() or XMLTABLE(), that return multiple rows for each row of the outer query.
• It is useful for situations where you want to apply a set of operations or a function (e.g., ROWNUM, ROW_NUMBER(), or XMLTABLE) in a correlated manner.

Example 1: Basic LATERAL Join (with CROSS JOIN)

Suppose you have a departments table and an employees table, and you want to list the first three employees (based on hire date) for each department.

departments Table:

dept_id	dept_name
1	HR
2	IT
3	Marketing

employees Table:

emp_id	emp_name	hire_date	dept_id
1	Alice	2020-01-01	1
2	Bob	2021-05-15	1
3	Charlie	2019-06-12	2
4	David	2022-07-19	2
5	Eve	2020-03-22	3

To get the first three employees for each department, you can use LATERAL with a subquery that ranks employees based on their hire date:

SELECT d.dept_name, e.emp_name, e.hire_date

FROM departments d

CROSS JOIN LATERAL (

    SELECT emp_name, hire_date

    FROM employees e

    WHERE e.dept_id = d.dept_id

    ORDER BY e.hire_date

    FETCH FIRST 3 ROWS ONLY

) e;

Result:

dept_name	emp_name	hire_date
HR	Alice	2020-01-01
HR	Bob	2021-05-15
IT	Charlie	2019-06-12
IT	David	2022-07-19
Marketing	Eve	2020-03-22

Explanation:

• The subquery in the LATERAL clause is evaluated for each department (d.dept_id), and it retrieves the first three employees (ordered by hire_date) from the employees table.
• CROSS JOIN LATERAL ensures that for each department, the subquery executes and returns the results as part of the final output.

Example 2: Using LATERAL for a Set-Returning Function

A more advanced use case involves using LATERAL with a set-returning function, such as TABLE() or XMLTABLE(), which returns multiple rows for each row of the outer query.

Suppose you have a table of departments and a table of department_budget, where each department has a budget broken down by month in an XML format. You want to extract and list the department’s monthly budget details.

departments Table:

dept_id	dept_name
1	HR
2	IT
3	Marketing

department_budget Table (XML format):

dept_id	budget_data
1	January5000February6000
2	January7000February8000

To extract each department’s monthly budget using LATERAL, you could use the XMLTABLE function:

SELECT d.dept_name, b.month, b.amount

FROM departments d

CROSS JOIN LATERAL (

    SELECT x.month, x.amount

    FROM XMLTABLE(

        '/budgets/row'

        PASSING d.budget_data

        COLUMNS month VARCHAR2(50) PATH 'month',

                amount NUMBER PATH 'amount'

    ) x

) b;

Result:

dept_name	month	amount
HR	January	5000
HR	February	6000
IT	January	7000
IT	February	8000

Explanation:

• The CROSS JOIN LATERAL with the XMLTABLE function extracts each month and amount from the XML budget_data for each department.
• For each row in the departments table, the XMLTABLE function is executed, and the budget data is parsed and returned in the result set.

Performance Considerations:

• LATERAL can be computationally expensive when used with complex subqueries, especially if those subqueries return large sets of rows. It is important to optimize queries using LATERAL and ensure they are efficient in terms of execution time and resources.
• LATERAL is particularly useful when set-returning functions or correlated subqueries are needed, as it allows for better flexibility in those situations.
• You should be cautious when using LATERAL in large datasets, as it may introduce performance overhead due to the re-evaluation of subqueries for each row.

Conclusion:

• The LATERAL JOIN is a powerful feature in Oracle SQL that allows subqueries in the FROM clause to reference columns from the outer query.
• It is particularly useful for performing correlated subqueries, extracting data using set-returning functions, or performing row-by-row operations that would otherwise be difficult to achieve.
• LATERAL should be used judiciously, as it may impact performance, especially with complex or large datasets.