SQL Self-Join Example: The Fastest Connecting Flight

Q: How do I find connecting flights in SQL?

Join the flights table to itself: the first copy is the first leg, the second copy is the second leg. Join them on the arrival airport of the first leg being the departure airport of the second, and require the second departure to be later than the first arrival.

Q: How do I calculate the time between two timestamps in PostgreSQL?

Subtract them: scheduled_arrival - scheduled_departure returns an interval. In MySQL, use TIMESTAMPDIFF(MINUTE, start_time, end_time).

Q: How do I find routes with two or more connections?

Add another self-join for each extra leg, or use a recursive CTE that extends a route one flight at a time (Lesson 6.6).

Lesson 12.1 · Reading time: ~6 min · Practice tasks ↓

This lesson walks through the solution of the SQL task Find the fastest flight on the Bookings database (PostgreSQL). You'll see how a self-join turns one flights table into the two legs of a route with a connection, how to make sure the connection is possible, and how to choose the fastest option.

The task: find the fastest way to fly from Pulkovo Airport in St. Petersburg (LED) to Bryansk (BZK) on August 16, 2017, with exactly one connection. For each option you need the flight numbers, the connection airport, the duration of each flight, the waiting time, and the total trip time. Times are calculated from scheduled_departure and scheduled_arrival.

Try to solve the task yourself first; the full query is hidden at the end of the lesson.

The Key Idea: a Route Is Two Rows

A route with one connection is not one flight but two:

Leg 1: from LED to some airport X.
Leg 2: from the same airport X to BZK.

Both legs are rows of the same flights table. To put them side by side in one result row, join the table to itself and give each copy its own alias, first_leg and second_leg. This is a self-join (see Lesson 5.7).

Step-by-Step Solution

1. Choose the Candidate Flights

The first leg departs from LED, the second leg arrives at BZK, and both depart on August 16:

SELECT COUNT(*) AS flights_from_led
FROM flights
WHERE departure_airport = 'LED'
  AND scheduled_departure BETWEEN '2017-08-16' AND '2017-08-17';

Result: 33 flights depart from LED that day; each of them can be the first leg.

2. Connect the Legs

The legs must meet at the same airport, and the second flight must depart after the first one lands:

SELECT
    first_leg.flight_no,
    first_leg.arrival_airport AS connection_airport,
    second_leg.flight_no,
    second_leg.scheduled_departure - first_leg.scheduled_arrival AS connection_time,
    second_leg.scheduled_arrival - first_leg.scheduled_departure AS total_trip_time
FROM flights AS first_leg
JOIN flights AS second_leg
    ON first_leg.arrival_airport = second_leg.departure_airport
   AND second_leg.arrival_airport = 'BZK'
   AND second_leg.scheduled_departure > first_leg.scheduled_arrival
WHERE first_leg.departure_airport = 'LED'
  AND first_leg.scheduled_departure BETWEEN '2017-08-16' AND '2017-08-17'
  AND second_leg.scheduled_departure BETWEEN '2017-08-16' AND '2017-08-17'
ORDER BY total_trip_time;

Result: 18 options through 3 connection airports; the fastest one goes through Moscow Sheremetyevo (SVO) and takes 1 hour 25 minutes.

first_leg.arrival_airport = second_leg.departure_airport is the connection point.
second_leg.scheduled_departure > first_leg.scheduled_arrival drops options where the second plane leaves before the first one lands. Without it, the query would return impossible routes.

3. Calculate the Durations

In PostgreSQL, subtracting one timestamp from another gives an interval, such as 01:25:00:

first flight: first_leg.scheduled_arrival - first_leg.scheduled_departure;
waiting time: second_leg.scheduled_departure - first_leg.scheduled_arrival;
total trip: second_leg.scheduled_arrival - first_leg.scheduled_departure, from the first departure to the final arrival.

4. Keep the Fastest Option

Sort by the total trip time and keep the first row with LIMIT 1. If several options could have the same time, LIMIT 1 returns one of them; to show all of them, compare with the minimum instead, for example with RANK() (Lesson 7.2).

Things to Notice

The connection is only 5 minutes. In the fastest option, the second flight leaves SVO five minutes after the first one lands. The task only requires the second flight to depart later; a real booking system would require a minimum connection time, for example second_leg.scheduled_departure >= first_leg.scheduled_arrival + INTERVAL '1 hour'.
Filter both legs by date. Without the date condition on second_leg, the query would also connect flights on later days.
BETWEEN includes both ends. BETWEEN '2017-08-16' AND '2017-08-17' also includes a flight at exactly midnight on August 17. For whole days, >= '2017-08-16' AND < '2017-08-17' is more precise.

Spoiler: the SQL query with the solution is hidden below. Click to reveal.


select
    first_leg.flight_no flight1_no,
    first_leg.departure_airport,
    first_leg.arrival_airport connection_airport,
    first_leg.scheduled_arrival - first_leg.scheduled_departure flight1_time,
    second_leg.scheduled_departure - first_leg.scheduled_arrival connection_time,
    second_leg.flight_no flight2_no,
    second_leg.arrival_airport,
    second_leg.scheduled_arrival - second_leg.scheduled_departure flight2_time,
    second_leg.scheduled_arrival - first_leg.scheduled_departure total_trip_time
from flights first_leg
join flights second_leg on first_leg.arrival_airport = second_leg.departure_airport
    and second_leg.arrival_airport = 'BZK'
    and second_leg.scheduled_departure > first_leg.scheduled_arrival
where first_leg.departure_airport = 'LED'
    and first_leg.scheduled_departure between '2017-08-16' and '2017-08-17'
    and second_leg.scheduled_departure between '2017-08-16' and '2017-08-17'
order by total_trip_time
limit 1;

Key Takeaways:

A route with one connection is two rows of the same table, joined with a self-join and two aliases.
The connection condition is first_leg.arrival_airport = second_leg.departure_airport.
The second flight must depart after the first one arrives; otherwise the route is impossible.
Subtracting timestamps in PostgreSQL gives an interval, which can be compared and sorted.
ORDER BY ... LIMIT 1 returns the best option; use RANK() to keep ties.

Frequently Asked Questions

How do I find connecting flights in SQL?