SQL · Guides · October 30, 2026 · 10 min read

SQL self join explained, with examples: pairs, connections, history

A self join is an ordinary join in which both sides are the same table. You list the table twice under two aliases, for example flights f1 JOIN flights f2, and the join condition says how a row on the left relates to a row on the right: same city, next leg of a trip, the following version in a history. There is no SELF JOIN keyword; any join type works (INNER, LEFT, even CROSS). Self joins answer questions about relationships between rows of one table: pairs, sequences and connections.

All examples run on the airline database from the Nomto SQL course (104 airports, 33,121 flights, 366,733 tickets), in PostgreSQL syntax on DuckDB, the engine of the Nomto SQL room. Every result table is the real output.

Self join syntax

SELECT a.col, b.col
FROM some_table a
JOIN some_table b
  ON <how row a relates to row b>;

The aliases are required. Without them, airports.city = airports.city is ambiguous, and you could not say which copy a column comes from. Pick aliases that describe the role of each copy (origin and dest, prev and next, out and back), not just a and b, once the query grows beyond a few lines.

Unique pairs: a < b instead of a <> b

The simplest self join pairs rows that share something. The joins guide uses it to find cities with several airports. Here is a pair query without a shared key: airports in different cities that are closest to each other, by great-circle distance from their coordinates.

SELECT a1.airport_code, a1.city,
       a2.airport_code AS other, a2.city AS other_city,
       ROUND(2 * 6371 * ASIN(SQRT(
         POWER(SIN(RADIANS(a2.latitude - a1.latitude) / 2), 2) +
         COS(RADIANS(a1.latitude)) * COS(RADIANS(a2.latitude)) *
         POWER(SIN(RADIANS(a2.longitude - a1.longitude) / 2), 2)
       ))) AS km
FROM airports a1
JOIN airports a2 ON a1.airport_code < a2.airport_code
WHERE a1.city <> a2.city
ORDER BY km
LIMIT 6;

The condition in ON decides how many rows you get. With 104 airports:

The < trick works on any column with a fixed order, such as an ID, a code or a name. It removes self-matches and mirror duplicates in one go, and halves the work. Use <> only when direction matters, for example “A is closer to B than to anything else” where A and B play different roles. The query compares every airport with every other, which is fine for a hundred rows but grows with the square of the table size, so on large tables add a condition that narrows the candidates (same region, a bounding box on latitude).

Connecting flights: a row’s end meets another row’s start

The most useful self joins chain events: the arrival of one flight joined to a departure from the same airport soon after. Connections from Kazan via Moscow Domodedovo on 20 August, with a layover between one and four hours:

SELECT f1.flight_no AS leg1, f1.arrival_airport AS via,
       f1.scheduled_arrival::time AS lands,
       f2.flight_no AS leg2, f2.arrival_airport AS final_dest,
       f2.scheduled_departure::time AS departs,
       f2.scheduled_departure - f1.scheduled_arrival AS layover
FROM flights f1
JOIN flights f2
  ON  f2.departure_airport = f1.arrival_airport
  AND f2.scheduled_departure BETWEEN f1.scheduled_arrival + INTERVAL '1 hour'
                                 AND f1.scheduled_arrival + INTERVAL '4 hours'
  AND f2.arrival_airport <> f1.departure_airport
WHERE f1.departure_airport = 'KZN'
  AND f1.arrival_airport = 'DME'
  AND f1.scheduled_departure::date = '2026-08-20'
ORDER BY f2.scheduled_departure;

One evening flight from Kazan connects on to Belgorod, Yuzhno-Sakhalinsk and Khabarovsk. Three details make this query right:

• The join is not on a key. f2.departure_airport = f1.arrival_airport is an equality, but the time window is a range condition. Self joins often mix both.
• The time window is relative to the first leg. BETWEEN arrival + 1 hour AND arrival + 4 hours stops impossible connections (leaving before you land) and absurd ones (a next-day departure).
• f2.arrival_airport <> f1.departure_airport removes “connections” that fly you straight back home.

The WHERE filter on the date applies only to the first leg; the second leg can leave on the next day if the first one lands late in the evening. Drop the f1.arrival_airport = 'DME' filter and the same query lists all 17 connections from Kazan that day, via St Petersburg, Rostov, Kirov and Domodedovo. To chain three or more legs, repeat the join or switch to a recursive CTE.

Return trips: matching a row with its mirror

A ticket can contain several flight segments. A round trip is a ticket where one segment goes A to B and a later one goes B to A. That is a self join of the ticket’s segments, with the airports swapped in the condition. To keep it simple, look only at tickets with exactly two segments (183,338 of them):

WITH seg AS (
  SELECT tf.ticket_no, f.departure_airport, f.arrival_airport, f.scheduled_departure
  FROM ticket_flights tf
  JOIN flights f ON f.flight_id = tf.flight_id
  WHERE tf.ticket_no IN (
    SELECT ticket_no FROM ticket_flights GROUP BY ticket_no HAVING COUNT(*) = 2
  )
)
SELECT outbound.departure_airport AS home, outbound.arrival_airport AS destination,
       COUNT(*) AS round_trips,
       ROUND(AVG(back.scheduled_departure::date - outbound.scheduled_departure::date), 1) AS avg_days_away
FROM seg outbound
JOIN seg back
  ON  back.ticket_no = outbound.ticket_no
  AND back.departure_airport = outbound.arrival_airport
  AND back.arrival_airport = outbound.departure_airport
  AND back.scheduled_departure > outbound.scheduled_departure
GROUP BY home, destination
ORDER BY round_trips DESC
LIMIT 5;

Across all two-segment tickets, 177,847 are round trips, with travellers away 8.4 days on average. The condition back.scheduled_departure > outbound.scheduled_departure plays the same role as a < b above: without it every round trip would also match “backwards”, with the return flight as the outbound one, and the count would double.

Why only two-segment tickets? Tickets with connections, say Moscow to Belgorod to Anapa and back, contain four segments, and the mirror condition matches each leg with its own return: two pairs per ticket. Joining a table to itself multiplies rows whenever one row has several matches, the same fan-out as in any join. Before you aggregate the result of a self join, check what one row of the output stands for.

History rows: linking each version to the next

Tables that store versions with valid_from and valid_to can be self-joined to put each version next to the one that replaced it. Loyalty tier changes, where the old row’s end equals the new row’s start:

SELECT prev.tier AS from_tier, nxt.tier AS to_tier, COUNT(*) AS moves
FROM loyalty_tier_history prev
JOIN loyalty_tier_history nxt
  ON  nxt.member_id = prev.member_id
  AND nxt.valid_from = prev.valid_to
GROUP BY from_tier, to_tier
ORDER BY moves DESC;

This is the classic “employee and manager” pattern applied to time: a row points to another row of the same table, here through dates instead of a manager_id column. With an org chart you would write JOIN employees mgr ON mgr.employee_id = emp.manager_id, and a LEFT JOIN would keep the CEO, who has no manager.

Self join vs window function

The tier table has 58,194 rows for 53,000 members, so there should be 5,194 transitions. The self join found 4,989. Ask the same question with LAG, which simply takes the previous row by date:

SELECT prev_tier AS from_tier, tier AS to_tier, COUNT(*) AS moves
FROM (
  SELECT tier,
         LAG(tier) OVER (PARTITION BY member_id ORDER BY valid_from) AS prev_tier
  FROM loyalty_tier_history
) t
WHERE prev_tier IS NOT NULL
GROUP BY from_tier, to_tier
ORDER BY moves DESC;

5,194 transitions. The 205 that the self join missed are rows whose valid_to does not match the next valid_from: 110 old versions were never closed, and 95 were closed a few days late, overlapping the next one. An equality join on dates silently drops those links; LAG does not care about valid_to at all. The SCD type 2 guide shows how to find and repair such rows.

Neither answer is “the” right one. The self join trusts the stated links, the window function trusts the ordering. The difference between them is itself a data quality check. As a rule of thumb:

Window functions read the table once and sort it; a self join reads it twice and can produce many matches per row. When both can answer the question, the window function is usually faster and cannot fan out. SQL window functions covers LAG and LEAD in detail.

LEFT self join: keep rows with no partner

An INNER self join drops rows that have no match. To keep them, use LEFT JOIN and test for NULL on the right side. Members whose current tier came from nowhere, that is, the first row of each history, are the rows with no predecessor:

SELECT COUNT(*) AS rows_without_predecessor
FROM loyalty_tier_history cur
LEFT JOIN loyalty_tier_history prev
  ON  prev.member_id = cur.member_id
  AND prev.valid_to = cur.valid_from
WHERE prev.member_id IS NULL;

53,000 of them are the members’ first rows. The other 205 are the broken links from the previous section, found this time without a window function. This “anti self join” is how you find orphans: categories whose parent is missing, flights with no return, versions with no predecessor.

Common mistakes

• Forgetting the inequality. a.city = b.city alone matches every airport with itself; the result has more rows than the table and every airport looks like it shares a city.
• Using <> where < is meant. Every pair appears twice, so counts are doubled.
• Aggregating after a fan-out. If one row can match several, SUM and COUNT over the self join count it several times.
• Joining on columns that can be NULL. NULL = NULL is not true, so rows with a NULL key never find a partner. That is the reason the 110 open tier rows above did not join.
• No narrowing condition on a big table. A self join on a weak condition is close to a cross join: a million rows against a million rows.

FAQ

What is a self join in SQL?

A join of a table with itself. You reference the table twice under different aliases and join the copies on a condition, for example FROM employees e JOIN employees m ON m.employee_id = e.manager_id. It is used to compare rows of the same table with each other.

Is SELF JOIN a keyword?

No. A self join is written with the usual JOIN, LEFT JOIN or CROSS JOIN syntax; what makes it a self join is that both sides are the same table.

How do I avoid duplicate pairs in a self join?

Join on a.id < b.id instead of a.id <> b.id. That removes rows matched with themselves and keeps only one of each mirror pair (A–B but not B–A).

When should I use a self join instead of LAG or LEAD?

Use LAG/LEAD for the previous or next row in a fixed order. Use a self join when the relationship is defined by a condition rather than by position: pairs within a distance, connections within a time window, a row pointing to another by ID.

Can a self join be a LEFT JOIN?

Yes. A LEFT self join keeps rows that have no partner, with NULLs on the right side. Adding WHERE right_side.key IS NULL turns it into an anti join that finds rows with no match, such as records whose parent is missing.

Also in Español, Português (Brasil), Tiếng Việt, 简体中文