How to Join Inside Inline Views with LATERAL

View All Scripts Login to Run Script

Script Name How to Join Inside Inline Views with LATERAL
Description An overview of the LATERAL clause. This allows you to join tables to the left of an inline view inside the subquery.
Area SQL General / SQL Query
Contributor Chris Saxon (Oracle)
Created Tuesday October 01, 2019

Statement 1

In an inline view, by default you can't access columns from tables outside the subquery. This tries to do it, so raises an ORA-00904.

select *   
from   co.customers c, (  
         select * from co.orders o  
         where  c.customer_id = o.customer_id  
       )  
order  by order_datetime desc  
fetch  first 10 rows only

ORA-00904: "C"."CUSTOMER_ID": invalid identifier

More Details: https://docs.oracle.com/error-help/db/ora-00904

Statement 2

By defining the inline view as LATERAL, you can now access columns from table to the left of it in the FROM clause.

select full_name, order_id, order_datetime  
from   co.customers c, lateral (  
         select * from co.orders o  
         where  c.customer_id = o.customer_id  
       )  
order  by order_datetime desc  
fetch  first 10 rows only


FULL_NAME ORDER_ID ORDER_DATETIME
Connie Delagarza 1950 12-APR-19 09.41.06.642641 PM
Connie Delagarza 1949 11-APR-19 07.14.31.588169 PM
Rubi Carlisle 1948 11-APR-19 03.49.45.728115 AM
Adam Martinez 1947 11-APR-19 03.49.08.760887 AM
Connie Delagarza 1946 10-APR-19 06.22.55.181577 PM
Monte Calloway 1945 10-APR-19 03.49.31.059138 PM
Cheryl Phillips 1944 10-APR-19 10.14.27.406048 AM
Adam Martinez 1943 10-APR-19 08.14.01.961369 AM
Leon Schoepfer 1942 10-APR-19 03.36.17.661842 AM
Sheryl Banks 1941 08-APR-19 11.16.55.199255 PM

10 rows selected.

FULL_NAME	ORDER_ID	ORDER_DATETIME
Connie Delagarza	1950	12-APR-19 09.41.06.642641 PM
Connie Delagarza	1949	11-APR-19 07.14.31.588169 PM
Rubi Carlisle	1948	11-APR-19 03.49.45.728115 AM
Adam Martinez	1947	11-APR-19 03.49.08.760887 AM
Connie Delagarza	1946	10-APR-19 06.22.55.181577 PM
Monte Calloway	1945	10-APR-19 03.49.31.059138 PM
Cheryl Phillips	1944	10-APR-19 10.14.27.406048 AM
Adam Martinez	1943	10-APR-19 08.14.01.961369 AM
Leon Schoepfer	1942	10-APR-19 03.36.17.661842 AM
Sheryl Banks	1941	08-APR-19 11.16.55.199255 PM

Statement 3

This makes it possible to do "Top-N per group" queries inside the inline view. For example, this finds the two most recent orders for each customer. Then returns the first 10 rows, sorted by customer name then order dates descending.

select full_name, order_id, order_datetime  
from   co.customers c, lateral (  
         select o.order_id, o.order_datetime  
         from   co.orders o  
         where  o.customer_id = c.customer_id  
         order  by o.order_datetime desc  
         fetch  first 2 rows only  
       )  
order  by full_name, order_datetime desc  
fetch  first 10 rows only


FULL_NAME ORDER_ID ORDER_DATETIME
Adam Martinez 1947 11-APR-19 03.49.08.760887 AM
Adam Martinez 1943 10-APR-19 08.14.01.961369 AM
Adam Miller 1550 24-JAN-19 05.29.20.590604 AM
Adam Miller 1523 21-JAN-19 09.35.14.026494 AM
Adam Walker 1202 01-DEC-18 07.42.04.777939 PM
Adam Walker 722 02-SEP-18 08.29.24.738773 PM
Adan Lamica 1846 16-MAR-19 11.23.27.800966 PM
Adan Lamica 1389 31-DEC-18 02.13.56.291244 PM
Adeline Iannotti 1793 06-MAR-19 11.17.49.990617 PM
Adeline Iannotti 1475 13-JAN-19 06.23.33.261294 PM

10 rows selected.

FULL_NAME	ORDER_ID	ORDER_DATETIME
Adam Martinez	1947	11-APR-19 03.49.08.760887 AM
Adam Martinez	1943	10-APR-19 08.14.01.961369 AM
Adam Miller	1550	24-JAN-19 05.29.20.590604 AM
Adam Miller	1523	21-JAN-19 09.35.14.026494 AM
Adam Walker	1202	01-DEC-18 07.42.04.777939 PM
Adam Walker	722	02-SEP-18 08.29.24.738773 PM
Adan Lamica	1846	16-MAR-19 11.23.27.800966 PM
Adan Lamica	1389	31-DEC-18 02.13.56.291244 PM
Adeline Iannotti	1793	06-MAR-19 11.17.49.990617 PM
Adeline Iannotti	1475	13-JAN-19 06.23.33.261294 PM

Statement 4

Compare the previous query to this, which joins does the join outside the inline view. This finds the two most recent orders. Then joins to the customers that placed these. So this returns only two rows. Instead of two rows/customer.

select full_name, order_id, order_datetime 
from   co.customers c 
join   ( 
         select o.customer_id, o.order_id, o.order_datetime 
         from   co.orders o 
         order  by o.order_datetime desc 
         fetch  first 2 rows only 
       ) o 
on     o.customer_id = c.customer_id 
order  by full_name, order_datetime desc


FULL_NAME ORDER_ID ORDER_DATETIME
Connie Delagarza 1950 12-APR-19 09.41.06.642641 PM
Connie Delagarza 1949 11-APR-19 07.14.31.588169 PM

2 rows selected.

FULL_NAME	ORDER_ID	ORDER_DATETIME
Connie Delagarza	1950	12-APR-19 09.41.06.642641 PM
Connie Delagarza	1949	11-APR-19 07.14.31.588169 PM

Statement 5

You can also do grouping and aggregations inside a lateral inline view. This finds the two highest-valued orders for each store. And returns the first 10 rows.

select store_name, order_id, order_total  
from   co.stores s, lateral (  
         select o.order_id,   
                sum ( oi.quantity * oi.unit_price ) order_total  
         from   co.orders o  
         join   co.order_items oi  
         on     o.order_id = oi.order_id  
         where  o.store_id = s.store_id  
         group  by o.order_id  
         order  by sum ( oi.quantity * oi.unit_price ) desc  
         fetch  first 2 rows only  
       )  
order  by store_name, order_total desc 
fetch  first 10 rows only


STORE_NAME ORDER_ID ORDER_TOTAL
Bejing 1911 310.4
Bejing 1818 241.86
Bengaluru 1300 400.03
Bengaluru 1202 278.98
Berlin 847 470.73
Berlin 1795 331.8
Bucharest 1569 307.11
Bucharest 445 285.58
Buenos Aires 1874 291.47
Buenos Aires 1821 264.2

10 rows selected.

STORE_NAME	ORDER_ID	ORDER_TOTAL
Bejing	1911	310.4
Bejing	1818	241.86
Bengaluru	1300	400.03
Bengaluru	1202	278.98
Berlin	847	470.73
Berlin	1795	331.8
Bucharest	1569	307.11
Bucharest	445	285.58
Buenos Aires	1874	291.47
Buenos Aires	1821	264.2

Statement 6

Again, compare a LATERAL join to joining outside the inline view. This finds the two highest-valued orders across all stores. Then joins to the corresponding stores. So it returns at most two rows.

select store_name, order_id, order_total  
from   co.stores s  
join  (  
         select o.store_id, o.order_id,   
                sum ( oi.quantity * oi.unit_price ) order_total  
         from   co.orders o  
         join   co.order_items oi  
         on     o.order_id = oi.order_id  
         group  by o.store_id, o.order_id  
         order  by sum ( oi.quantity * oi.unit_price ) desc  
         fetch  first 2 rows only  
       ) o  
on     o.store_id = s.store_id


STORE_NAME ORDER_ID ORDER_TOTAL
Online 918 529.4
Online 1509 517.69

2 rows selected.

STORE_NAME	ORDER_ID	ORDER_TOTAL
Online	918	529.4
Online	1509	517.69

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database