I have two tables: Orders1 and Orders2, both with OrderDate column in them. I want to join the two tables, matching the closest date in Order2 to Order1 -- the one single order in Order2 whose date is closest (greater than or equal to) the Order1 date. I know I can do this with a correlated subquery. But wouldn't a direct join (using a non equi operator) perform better? I just can't figure out how to construct a direct join that returns only 1 row when using >=.

Minimal example:

 CREATE TABLE Order1(
  OrderDate DATE, 
  Info Varchar(256) );
CREATE TABLE Order2(
 OrderDate DATE,
 Info Varchar(256) );

Order1 Data:  
2022-09-01  'ABC'
2022-10-01  'XYZ'

Order2 Data:
2022-09-03  '123'
2022-09-04  '456'
2022-10-15  '000'
2022-10-17  '777'

Desired Output:
Order1.Orderdate, Order1.Info, Order2.Orderdate, Order2.Info
2022-09-01, 'ABC', 2022-09-03 '123'
2022-10-01, 'XYZ', 2022-10-15, '000'

CodePudding user response：

A lateral join will do. DB-fiddle

select o.OrderDate first_orderdate, o.Info first_info, 
       l.OrderDate second_orderdate, l.Info second_info
from Order1 as o left join lateral 
(
 select * 
 from Order2 
 where OrderDate >= o.OrderDate 
 order by OrderDate 
 limit 1
) as l on true;