Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table_a (
  name VARCHAR(255),
  date_1 DATE
);
INSERT INTO table_a (name, date_1) VALUES
('john', '2010-01-01'),
('john', '2012-02-01'),
('john', '2017-08-01'),
('sara', '2008-04-01'),
('sara', '2011-04-01'),
('tim', '2000-01-01'),
('tim', '2001-01-01'),
('alex', '2013-01-01');
CREATE TABLE table_b (
  name VARCHAR(255),
  date_2 DATE,
  date_3 DATE,
  var CHAR(1)
);
INSERT INTO table_b (name, date_2, date_3, var) VALUES
('john', '2001-01-01', '2015-01-01', 'b'),
('sara', '2000-01-01', '2015-01-01', 'c'),
('sara', '2015-01-02', '2022-01-01', 'a'),
('tim', '2020-01-01', '2021-01-01', 'a'),
('john', '1998-01-01', '1999-01-01', 'd');
select a.name, a.date_1, b.var
from table_a a
left join lateral (
  select * from table_b b where b.name = a.name and b.date_2 < a.date_1
  order by b.date_2 desc fetch next 1 rows only
) b on 1 = 1


NAME
DATE_1
VAR


john
2010-01-01
b

john
2012-02-01
b

john
2017-08-01
b

sara
2008-04-01
c

sara
2011-04-01
c

tim
2000-01-01
null

tim
2001-01-01
null

alex
2013-01-01
null

NAME	DATE_1	VAR
john	2010-01-01	b
john	2012-02-01	b
john	2017-08-01	b
sara	2008-04-01	c
sara	2011-04-01	c
tim	2000-01-01	null
tim	2001-01-01	null
alex	2013-01-01	null