How to Use LATERAL Inline Views with CROSS APPLY in Oracle

Questions such as "the two longest routes from each airport" or "the latest order of each customer" ask for a small result computed per row of another table. An ordinary inline view in FROM cannot do it, because it cannot see the tables listed before it. A lateral inline view can: it is evaluated once for each row to its left and may refer to that row's columns. Oracle writes it as LATERAL, CROSS APPLY, or OUTER APPLY.

Code for This Guide

The main examples are in the examples/joins folder of the Oracle Database 26ai code repository on GitHub, each with its output. They query NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

Syntax:

table1 cross apply (subquery referring to table1) alias
table1 outer apply (subquery referring to table1) alias
table1, lateral (subquery referring to table1) alias

Why an Ordinary Inline View Fails

An inline view is evaluated on its own, before it is joined, so it cannot refer to another table in the same FROM clause:

Example:

select a.airport_code, x.routes
from   airports a,
       (select count(*) as routes from routes r where r.origin = a.airport_code) x
where  a.airport_code in ('DXB', 'LHR');

Output:

Error starting at line : 1
In command -
select a.airport_code, x.routes
from   airports a,
       (select count(*) as routes from routes r where r.origin = a.airport_code) x
where  a.airport_code in ('DXB', 'LHR')
Error at Command Line : 3 Column : 66
Error report -
SQL Error: ORA-00904: "A"."AIRPORT_CODE": invalid identifier

Adding LATERAL in front of the same subquery makes it legal, as the third query below shows.

Top N per Row with CROSS APPLY

The first query returns the two longest routes from each of three airports. The subquery refers to a.airport_code, sorts that airport's routes by distance, and keeps two. The second uses OUTER APPLY, which keeps Kathmandu even though it has no routes. The third counts the routes of two airports with LATERAL.

Example:

-- the two longest routes from each of three airports
select a.airport_code, x.destination, x.distance_km
from   airports a
cross  apply (select r.destination, r.distance_km from routes r
              where r.origin = a.airport_code
              order by r.distance_km desc fetch first 2 rows only) x
where  a.airport_code in ('SIN', 'SYD', 'KTM');

select a.airport_code, x.destination
from   airports a
outer  apply (select r.destination from routes r where r.origin = a.airport_code
              order by r.distance_km desc fetch first 1 row only) x
where  a.airport_code in ('SIN', 'KTM');

select a.airport_code, x.routes
from   airports a,
       lateral (select count(*) as routes from routes r where r.origin = a.airport_code) x
where  a.airport_code in ('DXB', 'LHR');

Output:

AIRPORT_CODE    DESTINATION       DISTANCE_KM
_______________ ______________ ______________
SIN             SYD                      6294
SIN             DXB                      5845
SYD             DXB                     12043
SYD             SIN                      6294

AIRPORT_CODE    DESTINATION
_______________ ______________
KTM
SIN             SYD

AIRPORT_CODE       ROUTES
_______________ _________
DXB                    20
LHR                     2

In the first result, Kathmandu (KTM) is missing: it has no routes, so CROSS APPLY drops it. In the second, OUTER APPLY keeps it with an empty destination.

When to Use It

• Top N per row: the subquery can use ORDER BY with FETCH FIRST, which a correlated scalar subquery could do for only one value.
• Several columns per row: a scalar subquery in the select list returns one value, while an applied subquery returns as many columns as you need.
• Row-generating functions: CROSS APPLY with a table function or JSON_TABLE that takes the current row's column as input.

Top N per group can also be written with an analytic function such as ROW_NUMBER and a filter. Which is faster depends on the data: APPLY works well when each outer row has an index into the inner table and N is small.

Related Guides

• How to Write Correlated Subqueries in Oracle SQL
• How to Filter Analytic Results with QUALIFY in Oracle
• Oracle SQL Query to Use LEFT OUTER JOIN to Include All Records from Left Table

Conclusion

A lateral inline view is evaluated for every row of the tables before it and can refer to their columns. Use CROSS APPLY or LATERAL to drop rows for which it returns nothing, and OUTER APPLY to keep them. They answer top-N-per-row questions directly and return several columns per row, which neither an ordinary inline view nor a scalar subquery can do.