LATERAL Joins in Oracle SQL

Introduction

LATERAL is a join type that allows a subquery to use columns from tables that appear earlier in the FROM clause of the query. It lets us build a mental model of exploring data from the inside out.

This feature lets us work with subqueries in a unique way, allowing them to reference columns from preceding tables in the query. Normally, an inline view in the FROM clause cannot reference columns from tables that appear before it in that same FROM clause. LATERAL removes this restriction, allowing the inline view to reference columns from preceding tables. This enables us to run complex queries in a more intuitive way than the standard JOIN syntax.

To see how LATERAL join really works in Oracle, let us examine 2 examples

Examples

Finding latest customer orders

The code below creates the tables needed for the first examples:

DROP TABLE IF EXISTS scott.customers ;
DROP TABLE IF EXISTS scott.orders ;

CREATE TABLE customers (
    customer_id   NUMBER PRIMARY KEY,
    customer_name VARCHAR2(100) NOT NULL,
    city          VARCHAR2(100)
);

CREATE TABLE orders (
    order_id      NUMBER PRIMARY KEY,
    customer_id   NUMBER NOT NULL,
    order_date    DATE NOT NULL,
    order_total   NUMBER(10,2) NOT NULL,
    
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

We then insert some data into the tables using the Values constructor capability new in 26ai:

INSERT INTO customers (customer_id, customer_name, city)
VALUES  (1, 'Alice Johnson', 'Toronto')
       ,(2, 'Brian Smith', 'Winnipeg')
       ,(3, 'Carla Davis', 'Vancouver')
       ,(4, 'Daniel Brown', 'Calgary')
       ,(5, 'Emma Wilson', 'Ottawa');

INSERT INTO orders (order_id, customer_id, order_date, order_total)
VALUES  (101, 1, DATE '2026-01-10', 125.50)
       ,(102, 1, DATE '2026-03-15', 89.99)
       ,(103, 1, DATE '2026-07-22', 240.00)
       ,(104, 2, DATE '2026-02-05', 55.75)
       ,(105, 2, DATE '2026-08-18', 310.20)
       ,(106, 3, DATE '2026-04-12', 75.00)
       ,(107, 5, DATE '2026-01-25', 180.40)
       ,(108, 5, DATE '2026-05-30', 95.25);

COMMIT;

Now that we have our tables and data, imagine a requirement came in from the business as such: “For every customer, return only their most recent order. If the customer has never placed an order, still return the customer with NULL order information.”

Non-LATERAL Solution

Without using LATERAL, this can be resolved using a window function and the QUALIFY clause (New in 26ai), as shown below:

SELECT
    c.customer_name,
    c.city,
    o.order_id,
    to_char(o.order_date, 'DD-MON-RR') order_date,
    o.order_total
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
QUALIFY row_number () OVER (partition by c.customer_id order by o.order_date DESC ) = 1;

CUSTOMER_NAME   CITY        ORDER_ID ORDER_DATE  ORDER_TOTAL 
--------------- ----------- -------- ----------- ----------- 
Alice Johnson   Toronto     103      22-JUL-26   240         
Brian Smith     Winnipeg    105      18-AUG-26   310.2       
Carla Davis     Vancouver   106      12-APR-26   75          
Daniel Brown    Calgary                                      
Emma Wilson     Ottawa      108      30-MAY-26   95.25          

As shown, the query above returns the results we want. I do this only to show that QUALIFY may not be the only method to get around the problem. Also, If you’re not on 26ai yet, the QUALIFY functionality is not available to you and you would need a subquery to return the correct results. Other methods are possible as well.

Now, this query can be solved using LATERAL, but not only that; it’s more intuitive in the way developers(humans) tend to think about a problem, i.e., building from the inside out. Let’s see how it works.

LATERAL Solution

We start by querying all the latest order dates for all customers:

SELECT 
      customer_id,
      to_char(max(order_date),'DD-MON-RR') max_order_date
FROM
    orders 
GROUP BY customer_id;

CUSTOMER_ID MAX_ORDER_DATE 
----------- -------------- 
1           22-JUL-26      
2           18-AUG-26      
3           12-APR-26      
5           30-MAY-26      

We can then use these results to connect to an outer query to get the rest of the information (customer_name, city, order_id, order_total) using LEFT JOIN LATERAL as shown below:

SELECT 
    c.customer_name, 
    c.city,
    o.order_id,
    TO_CHAR(mcod.max_order_date, 'DD-MON-RR') max_order_date,
    o.order_total
FROM customers c
LEFT JOIN LATERAL
(
    SELECT 
        customer_id,
        MAX(order_date) max_order_date
    FROM orders 
    WHERE customer_id = c.customer_id
    GROUP BY customer_id
) mcod ON 1 = 1 
LEFT JOIN orders o 
    ON  c.customer_id = o.customer_id
    AND mcod.max_order_date = o.order_date
ORDER BY 1;

CUSTOMER_NAME   CITY        ORDER_ID MAX_ORDER_DATE ORDER_TOTAL 
--------------- ----------- -------- -------------- ----------- 
Alice Johnson   Toronto     103      22-JUL-26      240         
Brian Smith     Winnipeg    105      18-AUG-26      310.2       
Carla Davis     Vancouver   106      12-APR-26      75          
Daniel Brown    Calgary                                         
Emma Wilson     Ottawa      108      30-MAY-26      95.25                                                                          

As seen above, the LATERAL keyword enables us to get the same results as we did earlier using the window functions whilst allowing us to work at the problem in perhaps a more intuitive way.

Finding employee bonuses

Let’s look at another example of using LATERAL.

The code below creates the tables and inserts the data needed for this example:

DROP TABLE IF EXISTS employees;
DROP TABLE IF EXISTS emp_bonus ;

CREATE TABLE employees
(
    employee_id NUMBER(6)    CONSTRAINT employees_pk PRIMARY KEY,
    first_name  VARCHAR2(20),
    last_name   VARCHAR2(25) NOT NULL
);


CREATE TABLE emp_bonus
(
    bonus_id      NUMBER(6)    CONSTRAINT emp_bonus_pk PRIMARY KEY,
    employee_id   NUMBER(6)    NOT NULL,
    amount        NUMBER(6,2)  NOT NULL,
    date_granted  DATE         NOT NULL,

    CONSTRAINT emp_bonus_employee_fk
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
);

INSERT INTO employees (employee_id, first_name, last_name)
VALUES
    (100, 'Steven',    'King'),
    (101, 'Neena',     'Kochhar'),
    (102, 'Lex',       'De Haan'),
    (103, 'Alexander', 'Hunold');

INSERT INTO emp_bonus (bonus_id, employee_id, amount, date_granted)
VALUES
    (1, 100,  3.25, DATE '2026-08-18'),
    (2, 101,  6.50, DATE '2026-08-17'),
    (3, 102,  9.75, DATE '2026-08-16'),
    (4, 103, 13.00, DATE '2026-08-15');

COMMIT;

Consider a requirement to find employee bonuses and the date those bonuses were granted. To satisfy this query, we need data from two tables: employee and emp_bonus. The query can be written using an INNER JOIN as follows:

SELECT
   e.first_name
  ,e.last_name 
  ,eb.amount
  ,to_char(eb.date_granted,'DD-MON-RR') DATE_GRANTED
FROM 
     employees e
JOIN emp_bonus eb
ON e.employee_id = eb.employee_id
order by 3,4 ;

FIRST_NAME           LAST_NAME                     AMOUNT DATE_GRAN
-------------------- ------------------------- ---------- ---------
Steven               King                            3.25 18-AUG-26
Neena                Kochhar                          6.5 17-AUG-26
Lex                  De Haan                         9.75 16-AUG-26
Alexander            Hunold                            13 15-AUG-26

However, some people like to think about a problem in steps i.e building from a smaller unit to a bigger one and so on and so forth.

If we break this problem down into steps, we can first retrieve the amounts of the bonuses and the dates they were granted:

SELECT amount, to_char(date_granted,'DD-MON-RR') DATE_GRANTED
FROM emp_bonus ;

AMOUNT DATE_GRANTED 
------ ------------ 
3.25   18-AUG-26    
6.5    17-AUG-26    
9.75   16-AUG-26    
13     15-AUG-26    

We can now pass this information to an outer query to get the results using the LATERAL keyword:

SELECT 
        e.first_name
       ,e.last_name
       ,eb.amount
       ,to_char(eb.date_granted,'DD-MON-RR') DATE_GRANTED
FROM employees e, LATERAL
(
SELECT 
       amount
      ,date_granted
FROM
       emp_bonus
WHERE  employee_id = e.employee_id
) eb
ORDER BY 3,4 ;

FIRST_NAME  LAST_NAME AMOUNT DATE_GRANTED 
----------- --------- ------ ------------ 
Steven      King      3.25   18-AUG-26    
Neena       Kochhar   6.5    17-AUG-26    
Lex         De Haan   9.75   16-AUG-26    
Alexander   Hunold    13     15-AUG-26    

With LATERAL, we are able to refer to the column e.employee_id that is external to the inline view. Without the LATERAL keyword, this is not possible:

SQL> select e.first_name ,e.last_name, eb.amount, eb.date_granted
  2  from employees e,
  3  (
  4  select amount, date_granted
  5  from emp_bonus
  6  where employee_id = e.employee_id
  7  ) eb
  8  order by 3,4
  9  /
where employee_id = e.employee_id
                    *
ERROR at line 6:
ORA-00904: "E"."EMPLOYEE_ID": invalid identifier

Conclusion

It took me a while to wrap my head around LATERAL. I have been using SQL for a while, not the longest time, but enough that certain challenges have forced me to go out and learn new keywords, syntax, etc. I have never quite had a scenario where I’ve felt I’ve “needed” LATERAL to bypass. However, knowing this syntax expands my toolbox and gives me more options and ideas and ways that I can solve SQL problems going forward. Hopefully you feel the same way and find this post helpful. I have included some links to learn more about lateral below.

Thanks for reading, Harris.

References and Further reading

• @databasedude on youtube
• LATERAL Joins in SQL | Write Smarter SQL Queries(youtube)
• SQL LATERAL JOIN – A Beginner’s Guide
• A Deep Dive into SQL LATERAL JOIN