SQL · Guides · October 29, 2026 · 11 min read

SQL joins explained: INNER, LEFT, FULL, self and anti joins

A SQL join combines rows from two tables by matching a condition, usually a key in one table equal to a key in the other. The join type decides what happens to rows that find no match: an INNER JOIN drops them from both sides, a LEFT JOIN keeps every row of the left table and fills the right side with NULL, a FULL JOIN keeps unmatched rows from both sides. And the thing most explanations skip: when one row matches several rows, it is repeated once per match. That repetition, called fan-out, is behind most wrong totals in analytics.

Every example here runs on the airline database from the Nomto SQL course (104 airports, 9 aircraft models, 33,121 flights, 262,788 bookings, money in US dollars), and the result tables are the real output. Paste any query into the Nomto SQL room to check it.

The tables in the examples

• aircrafts: one row per aircraft model (aircraft_code, model, range)
• airports: one row per airport (airport_code, airport_name, city)
• flights: one row per departure; aircraft_code, departure_airport and arrival_airport point to the two tables above
• bookings: one row per booking, with total_amount
• tickets: one row per passenger ticket; several tickets can share one book_ref
• ticket_flights: one row per ticket per flight segment, with the segment’s amount
• boarding_passes: one row per issued boarding pass for a segment

Knowing the grain of each table (what one row means) is what lets you predict a join’s output before you run it.

INNER JOIN: only matching rows

Show which aircraft model operated each departure from Sochi on 1 August:

SELECT f.flight_no, f.scheduled_departure, f.arrival_airport, a.model
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
WHERE f.departure_airport = 'AER'
  AND f.scheduled_departure >= '2026-08-01'
  AND f.scheduled_departure <  '2026-08-02'
ORDER BY f.scheduled_departure
LIMIT 5;

JOIN on its own means INNER JOIN. Each flight has exactly one aircraft model, so the join adds a column without changing the number of rows. That is the safe direction: many-to-one.

Now count flights per model:

SELECT a.model, COUNT(*) AS flights
FROM flights f
JOIN aircrafts a ON a.aircraft_code = f.aircraft_code
GROUP BY a.model
ORDER BY flights DESC;

Eight rows, but the fleet has nine models. The inner join silently dropped the one model that has no flights. Sometimes that is what you want; when you are building a report “for every model”, it is a bug.

LEFT JOIN: keep every row on the left

SELECT a.aircraft_code, a.model, COUNT(f.flight_id) AS flights
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
GROUP BY a.aircraft_code, a.model
ORDER BY flights
LIMIT 3;

The Airbus A320-200 is back, with zero flights. Two details make this work:

• aircrafts is on the left, because that is the table whose rows must all survive.
• The query counts COUNT(f.flight_id), not COUNT(*). For the unmatched model the join produces one row with NULL in every flights column; COUNT(*) would count that row and report 1.

The WHERE trap: a LEFT JOIN that turns into an INNER JOIN

Task: for every model, how many departures from Kazan (KZN)?

SELECT a.aircraft_code, COUNT(f.flight_id) AS kzn_departures
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
WHERE f.departure_airport = 'KZN'
GROUP BY a.aircraft_code
ORDER BY a.aircraft_code;

Only five models. The WHERE runs after the join; for models with no Kazan flights, f.departure_airport is NULL, and NULL = 'KZN' is not true, so those rows are removed. The LEFT did nothing.

Move the condition on the right-hand table into ON:

SELECT a.aircraft_code, COUNT(f.flight_id) AS kzn_departures
FROM aircrafts a
LEFT JOIN flights f
  ON f.aircraft_code = a.aircraft_code
 AND f.departure_airport = 'KZN'
GROUP BY a.aircraft_code
ORDER BY a.aircraft_code;

Now all nine models come back: the same five counts, plus 320, 733, 763 and 773 with 0. Rule of thumb: in a LEFT JOIN, conditions on the right table go in ON; conditions on the left table go in WHERE.

Anti join: rows with no match

“Which models never fly?”, “which flights sold no tickets?” are anti-join questions. There are two standard ways to write them.

LEFT JOIN and keep the rows where the right side is missing:

SELECT a.aircraft_code, a.model
FROM aircrafts a
LEFT JOIN flights f ON f.aircraft_code = a.aircraft_code
WHERE f.flight_id IS NULL;

NOT EXISTS:

SELECT a.aircraft_code, a.model
FROM aircrafts a
WHERE NOT EXISTS (
  SELECT 1 FROM flights f WHERE f.aircraft_code = a.aircraft_code
);

Both return one row:

NOT EXISTS reads closer to the question and never multiplies rows, so many analysts prefer it. In the LEFT JOIN version, test a column that cannot be NULL in a real match, such as the right table’s key.

A bigger example: how many flights landed without a single ticket sold?

SELECT COUNT(*) AS arrived_empty
FROM flights f
WHERE f.status = 'Arrived'
  AND NOT EXISTS (
    SELECT 1 FROM ticket_flights tf WHERE tf.flight_id = f.flight_id
  );

Avoid NOT IN (SELECT ...) for anti joins: if the subquery returns even one NULL, NOT IN returns no rows at all.

FULL OUTER JOIN: reconcile two sets

A FULL JOIN keeps unmatched rows from both sides. It is the tool for comparing two lists: which destinations are served from Sheremetyevo (SVO), which from Pulkovo (LED), which from both?

WITH svo AS (
  SELECT DISTINCT arrival_airport AS dest FROM flights WHERE departure_airport = 'SVO'
), led AS (
  SELECT DISTINCT arrival_airport AS dest FROM flights WHERE departure_airport = 'LED'
)
SELECT CASE WHEN svo.dest IS NULL THEN 'only LED'
            WHEN led.dest IS NULL THEN 'only SVO'
            ELSE 'both' END AS served_from,
       COUNT(*) AS destinations
FROM svo
FULL JOIN led ON led.dest = svo.dest
GROUP BY 1
ORDER BY 1;

Neither a LEFT nor a RIGHT join could produce all three categories in one pass. When you list the rows, use COALESCE(svo.dest, led.dest) to get one non-empty key per row.

Note the DISTINCT inside each CTE: it reduces each side to one row per destination before the join. Without it, every flight would match every flight to the same airport on the other side, which is fan-out again.

Self join: a table joined to itself

Give the same table two aliases and you can compare its rows with each other. Which cities have more than one airport?

SELECT a1.city, a1.airport_code, a1.airport_name,
       a2.airport_code AS other_code, a2.airport_name AS other_name
FROM airports a1
JOIN airports a2
  ON a2.city = a1.city
 AND a1.airport_code < a2.airport_code
ORDER BY a1.city, a1.airport_code, a2.airport_code;

The condition a1.airport_code < a2.airport_code does two jobs: it stops an airport matching itself, and it keeps each pair once instead of twice (DME–SVO but not SVO–DME). Self joins are also how you find connections: an arrival of one flight joined to a departure from the same airport a few hours later.

Chains of joins

Joins chain naturally. Arriving passengers by city goes segment → flight → airport:

SELECT a.city, COUNT(*) AS passengers
FROM ticket_flights tf
JOIN flights f ON f.flight_id = tf.flight_id
JOIN airports a ON a.airport_code = f.arrival_airport
WHERE f.status = 'Arrived'
GROUP BY a.city
ORDER BY passengers DESC
LIMIT 5;

Every step here is many-to-one (many segments per flight, many flights per airport), so the count stays at the segment grain and is correct. Trouble starts when a join goes the other way.

Fan-out: why joins duplicate rows

When a row on the “one” side meets several rows on the “many” side, it is copied once per match. Booking 65A6EA has five tickets:

SELECT b.book_ref, b.total_amount, t.ticket_no, t.passenger_name
FROM bookings b
JOIN tickets t ON t.book_ref = b.book_ref
WHERE b.book_ref = '65A6EA'
ORDER BY t.ticket_no;

The booking’s $10,656 now appears five times. Nothing is wrong with these rows; the error happens when you sum them.

Double counting in practice

Revenue from bookings made on 2 August, straight from bookings:

SELECT COUNT(*) AS bookings, SUM(total_amount) AS revenue
FROM bookings
WHERE book_date >= '2026-08-02' AND book_date < '2026-08-03';

The same sum after joining tickets, for example because someone also wanted a ticket count:

SELECT COUNT(*) AS rows_after_join,
       COUNT(DISTINCT b.book_ref) AS bookings,
       SUM(b.total_amount) AS revenue
FROM bookings b
JOIN tickets t ON t.book_ref = b.book_ref
WHERE b.book_date >= '2026-08-02' AND b.book_date < '2026-08-03';

Revenue jumps by 66% with no error message. COUNT(DISTINCT book_ref) still says 5,761, which makes the inflated sum look trustworthy.

The tempting fix, SUM(DISTINCT b.total_amount), is worse: it returns 1,750,097.00, because different bookings that happen to have the same amount are collapsed into one.

Two fan-outs at once

Joining two independent “many” tables to the same parent multiplies them by each other. Flight 4729 has 161 sold segments and 161 boarding passes:

SELECT f.flight_id,
       COUNT(tf.ticket_no) AS segments,
       COUNT(bp.ticket_no) AS boarding_passes
FROM flights f
LEFT JOIN ticket_flights tf ON tf.flight_id = f.flight_id
LEFT JOIN boarding_passes bp ON bp.flight_id = f.flight_id
WHERE f.flight_id = 4729
GROUP BY f.flight_id;

161 × 161 = 25,921. Each segment row was paired with every boarding pass row of the flight.

How to avoid double counting

1. Aggregate before you join. Bring each “many” table down to the grain of the parent first, then join one-to-one:

WITH t AS (
  SELECT book_ref, COUNT(*) AS tickets
  FROM tickets
  GROUP BY book_ref
)
SELECT COUNT(*) AS bookings, SUM(t.tickets) AS tickets, SUM(b.total_amount) AS revenue
FROM bookings b
JOIN t ON t.book_ref = b.book_ref
WHERE b.book_date >= '2026-08-02' AND b.book_date < '2026-08-03';

Correct revenue and the ticket count in one query.

2. Sum the measure at its own grain. If the money lives on the “many” side, sum it there. The segment amounts for the same bookings add up to exactly the booking totals:

SELECT SUM(tf.amount) AS revenue
FROM bookings b
JOIN tickets t ON t.book_ref = b.book_ref
JOIN ticket_flights tf ON tf.ticket_no = t.ticket_no
WHERE b.book_date >= '2026-08-02' AND b.book_date < '2026-08-03';

This returns 4563873.00. Here the fan-out is harmless, because each amount belongs to exactly one segment row.

3. Use scalar subqueries for independent counts. For the two-fan-out case:

SELECT f.flight_id,
       (SELECT COUNT(*) FROM ticket_flights tf WHERE tf.flight_id = f.flight_id) AS segments,
       (SELECT COUNT(*) FROM boarding_passes bp WHERE bp.flight_id = f.flight_id) AS boarding_passes
FROM flights f
WHERE f.flight_id = 4729;

4. Check row counts. Before trusting a total, compare COUNT(*) before and after a join, or reconcile the total against the source table as above. If the number of rows grew and you did not expect it to, a key is not unique where you thought it was.

For more on filtering grouped results after a join, see GROUP BY vs HAVING; join questions also make up a good share of SQL interview questions.

FAQ

What is the difference between INNER JOIN and LEFT JOIN?

An INNER JOIN returns only rows that match on both sides. A LEFT JOIN returns every row of the left table; where there is no match, the right table’s columns are NULL. In the examples above, the inner join lost the A320 model with no flights and the left join kept it.

Is JOIN the same as INNER JOIN?

Yes. JOIN without a keyword is an inner join. Likewise LEFT JOIN and LEFT OUTER JOIN are the same thing.

Why does my join return more rows than the table has?

Because a key on the other side is not unique: each row is repeated once per matching row. That is expected for one-to-many joins. It only becomes a bug when you then sum a column that belongs to the “one” side.

What is an anti join?

A query that returns rows from one table that have no matching row in another. In SQL you write it with NOT EXISTS or with LEFT JOIN ... WHERE right_key IS NULL. There is no ANTI JOIN keyword in PostgreSQL.

When do I need a FULL OUTER JOIN?

When you compare two sets and want to see what is in the first only, the second only, and both, in one result: reconciling two systems, two periods or two airports’ route lists.

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