SQLite 3.27

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table CITIES(id int,  name varchar(50));

insert into CITIES values(1,   'New York');
insert into CITIES values(2,   'Paris');
insert into CITIES values(4,   'Amsterdam');   
insert into CITIES values(3,   'Tokyo');
create table FLIGHTS (id int, departure_id int, arrival_id int);
insert into FLIGHTS values(1,   1,               2);
insert into FLIGHTS values(2,   1,               3);
insert into FLIGHTS values(3,   2,               3);
insert into FLIGHTS values(4,   2,               4);
Select c.name as departure_city, ct.name as arrival_city from FLIGHTS f 
left join CITIES c on f.departure_id=c.id
left join CITIES ct on f.arrival_id = ct.id


departure_city
arrival_city


New York
Paris

New York
Tokyo

Paris
Tokyo

Paris
Amsterdam


Select c.name as departure_city, ct.name as arrival_city from FLIGHTS f 
inner join CITIES c on f.departure_id=c.id
inner join CITIES ct on f.arrival_id = ct.id


departure_city
arrival_city


New York
Paris

New York
Tokyo

Paris
Tokyo

Paris
Amsterdam