LATERAL Subqueries

A LATERAL subquery in Oracle SQL allows a subquery in the FROM clause to reference columns from the outer query. It is a powerful feature that enables you to use a subquery in a way that is not possible with regular subqueries. Essentially, it lets you treat the subquery as if it were a table that can access the outer query's context.

The LATERAL keyword allows the subquery to be evaluated once for each row of the outer query, and in doing so, it enables row-by-row processing that would not otherwise be possible in a standard inline view or subquery.

Key Features of LATERAL Subqueries:

1.     Access to Outer Query Columns:

o   In a regular subquery, the subquery cannot refer to columns from the outer query. But in a LATERAL subquery, the subquery can reference columns from the outer query.

o   This makes LATERAL especially useful when you need to perform operations where the subquery needs to interact with data from the outer query row-by-row.

2.     Row-by-Row Evaluation:

o   A LATERAL subquery is evaluated once for each row of the outer query. Unlike other subqueries, which are executed independently, a LATERAL subquery is treated as a correlated subquery.

3.     Analogy to CROSS JOIN:

o   The LATERAL subquery behaves like a CROSS JOIN between the outer query and the subquery, but with the important difference that the subquery can refer to columns of the outer query.

4.     Use with Table-Valued Functions:

o   LATERAL is useful when you need to join a table with the result of a table-valued function that requires access to the outer query's columns.

5.     CROSS APPLY and OUTER APPLY:

o   In SQL Server, the CROSS APPLY and OUTER APPLY operators are used for similar functionality as LATERAL in Oracle. While CROSS APPLY is used when the subquery always returns rows, OUTER APPLY allows for the subquery to return no rows for some outer query rows, much like a left join.

 

Syntax of a LATERAL Subquery:

The general syntax for a LATERAL subquery looks like this:

SELECT outer_query_columns, lateral_subquery_columns

FROM outer_table,

     LATERAL (subquery) lateral_subquery_alias;

·        LATERAL: This keyword is placed before the subquery in the FROM clause.

·        The subquery is treated as a derived table and can reference columns from the outer query.

·        The subquery can be aliased (in this case, lateral_subquery_alias) like any other derived table.

 

Examples of LATERAL Subqueries

1. Basic Example of LATERAL with a Simple Subquery:

Let’s look at an example where we use a LATERAL subquery to return the first n rows for each employee from a table of projects.

SELECT e.employee_name, p.project_name

FROM employees e,

     LATERAL (

         SELECT p.project_name

         FROM projects p

         WHERE p.employee_id = e.employee_id

         FETCH FIRST 1 ROWS ONLY

     ) p;

Explanation:

·        For each employee (e), the LATERAL subquery fetches the first project they are assigned to (using FETCH FIRST 1 ROWS ONLY).

·        The LATERAL keyword allows the subquery to refer to e.employee_id and select only the projects related to that particular employee.

 

2. Using LATERAL for Row-by-Row Operations:

In this example, let's find the most recent order for each customer:

SELECT c.customer_name, o.order_id, o.order_date

FROM customers c,

     LATERAL (

         SELECT o.order_id, o.order_date

         FROM orders o

         WHERE o.customer_id = c.customer_id

         ORDER BY o.order_date DESC

         FETCH FIRST 1 ROWS ONLY

     ) o;

Explanation:

·        The LATERAL subquery selects the most recent order (FETCH FIRST 1 ROWS ONLY orders by order_date DESC) for each customer.

·        The WHERE clause inside the subquery uses c.customer_id from the outer query (customers c), which is allowed because of the LATERAL keyword.

 

3. Example with a Table-Valued Function:

Suppose we have a table-valued function called get_employee_tasks that returns tasks assigned to an employee. You can use LATERAL to join the function's results with the outer query.

SELECT e.employee_name, t.task_name

FROM employees e,

     LATERAL get_employee_tasks(e.employee_id) t;

Explanation:

·        The function get_employee_tasks(e.employee_id) returns a result set of tasks assigned to each employee.

·        LATERAL allows the subquery to call the function for each employee, passing the employee_id from the outer query (e.employee_id).

·        Each employee will get matched with their respective tasks returned by the table-valued function.

 

Important Use Cases for LATERAL:

1.     Dynamic Table Joins:

o   You can use LATERAL when you need to join a table with the result of a subquery that varies based on the row being processed. This is useful for row-by-row evaluations.

2.     Working with Table-Valued Functions:

o   Table-valued functions are commonly used in LATERAL subqueries. This allows for efficient querying when using complex functions that return multiple rows, and you need to pass values from the outer query to the function.

3.     Handling Hierarchical Data:

o   LATERAL can be useful in cases involving hierarchical data, where you want to generate results that depend on the parent-child relationship between rows. For example, fetching all child nodes for a given parent in a recursive hierarchy.

4.     Flattening Nested Results:

o   If you have nested or array-like data, you can use LATERAL to unnest or flatten the results. This can be useful when dealing with arrays or lists in databases that support such data types.

 

Performance Considerations:

1.     Evaluation for Each Row:

o   LATERAL subqueries are evaluated once for each row of the outer query. If the outer query returns many rows, the subquery will be executed many times, which can increase the cost of the query.

2.     Indexing:

o   Proper indexing of the columns involved in the subquery’s WHERE clause (such as employee_id, order_id, etc.) will help improve the performance of LATERAL subqueries.

3.     Use of FETCH or ROWNUM:

o   Since LATERAL subqueries are often used to limit the result set to a fixed number of rows (using FETCH FIRST or ROWNUM), make sure that such limitations are used appropriately to reduce the data processed by the subquery.

4.     Avoiding Full Scans:

o   While LATERAL is powerful, if used improperly (e.g., with inefficient subqueries), it can lead to full table scans or other performance issues. Always monitor execution plans to ensure efficiency.

 

Comparison with CROSS APPLY and OUTER APPLY:

·        CROSS APPLY (in SQL Server) is similar to LATERAL in Oracle. It returns rows from the outer query and the subquery when the subquery returns rows. If the subquery returns no rows, that outer query row is excluded.

·        OUTER APPLY (also in SQL Server) works similarly to CROSS APPLY but returns all rows from the outer query, even if the subquery returns no rows (i.e., like a left join).

Oracle's LATERAL subquery is essentially an equivalent to CROSS APPLY in SQL Server. It does not have an exact counterpart for OUTER APPLY, but you can simulate that behavior with a LEFT JOIN or a LEFT OUTER JOIN in Oracle.

 

Summary of Key Points:

·        LATERAL allows a subquery in the FROM clause to reference columns from the outer query.

·        It enables row-by-row processing and is useful for performing operations that involve table-valued functions or need dynamic, row-dependent data.

·        Common use cases include dynamic joins, working with nested results, and querying hierarchical data.

·        Performance considerations are crucial, as LATERAL subqueries are evaluated once per outer query row.