SQL 数据分析 · 指南 · 2026年10月29日 · 11 分钟阅读

SQL JOIN 用法详解：INNER、LEFT、FULL、自连接与反连接

SQL JOIN 按照某个条件把两张表的行组合起来，最常见的条件是一张表的键等于另一张表的键。JOIN 的类型决定了找不到匹配的行怎么处理：INNER JOIN 把两边没匹配上的行都丢掉；LEFT JOIN 保留左表的每一行，右边填 NULL；FULL JOIN 两边没匹配上的行都保留。还有一点大多数教程都略过了：当一行匹配到多行时，它会按匹配次数被重复。这种重复叫作「扇出」（fan-out），数据分析里大部分算错的总数都源于它。

本文所有例子都在 Nomto SQL 课程的航空公司数据库上运行（104 个机场、9 种机型、33,121 个航班、262,788 笔订单，金额单位为美元），结果表都是真实输出。把任何一个查询粘贴到 Nomto 的 SQL 练习场里就能核对。

例子中用到的表

• aircrafts：每种机型一行（aircraft_code、model、range）
• airports：每个机场一行（airport_code、airport_name、city）
• flights：每次起飞一行；aircraft_code、departure_airport 和 arrival_airport 分别指向上面两张表
• bookings：每笔订单一行，带 total_amount
• tickets：每位乘客的机票一行；多张机票可以共用一个 book_ref
• ticket_flights：每张机票的每个航段一行，带该航段的 amount
• boarding_passes：每个航段已发放的登机牌一行

弄清每张表的粒度（一行代表什么），你就能在运行之前预判 JOIN 的结果。

INNER JOIN：只保留匹配的行

显示 8 月 1 日从索契（AER）出发的每个航班用的是哪种机型：

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 就是 INNER JOIN。每个航班恰好对应一种机型，所以这个 JOIN 只是多了一列，行数不变。这是安全的方向：多对一。

接下来按机型统计航班数：

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;

只有八行，可机队里有九种机型。INNER JOIN 悄悄地丢掉了那个没有航班的机型。有时这正是你想要的；但如果你要做的是一份「每种机型都要列出」的报表，这就是个 bug。

LEFT JOIN：保留左表的每一行

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;

Airbus A320-200 回来了，航班数是 0。这里有两个细节：

• aircrafts 放在左边，因为它是所有行都必须保留的那张表。
• 计数用的是 COUNT(f.flight_id)，不是 COUNT(*)。对于没匹配上的机型，JOIN 会生成一行，flights 的所有列都是 NULL；COUNT(*) 会把这一行算进去，报出 1。

WHERE 陷阱：LEFT JOIN 变成了 INNER JOIN

任务：每种机型从喀山（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;

只有五种机型。WHERE 在 JOIN 之后执行；对于没有喀山航班的机型，f.departure_airport 是 NULL，而 NULL = 'KZN' 不为真，于是这些行被删掉了。LEFT 等于白写。

把针对右表的条件挪到 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;

现在九种机型全部回来了：同样的五个计数，再加上 320、733、763 和 773，计数为 0。经验法则：在 LEFT JOIN 里，右表的条件写在 ON 中，左表的条件写在 WHERE 中。

反连接：找出没有匹配的行

「哪些机型从来不飞？」「哪些航班一张票都没卖出去？」这类都是反连接（anti join）问题。标准写法有两种。

用 LEFT JOIN，保留右边缺失的行：

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
);

两者都返回一行：

NOT EXISTS 读起来更贴近问题本身，而且永远不会让行数翻倍，所以很多分析师更喜欢它。如果用 LEFT JOIN 写法，要检查的应该是一个在真实匹配中不可能为 NULL 的列，比如右表的键。

再看一个规模大一点的例子：有多少航班一张票都没卖就落地了？

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
  );

做反连接时避免用 NOT IN (SELECT ...)：只要子查询返回哪怕一个 NULL，NOT IN 就一行都不返回。

FULL OUTER JOIN：核对两个集合

FULL JOIN 保留两边没匹配上的行，是比较两份清单的工具：哪些目的地从谢列梅捷沃（SVO）有航班，哪些从普尔科沃（LED）有，哪些两边都有？

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;

无论 LEFT 还是 RIGHT JOIN，都无法一次得到全部三类。如果要逐行列出，用 COALESCE(svo.dest, led.dest) 为每一行取一个非空的键。

注意每个 CTE 里的 DISTINCT：它在 JOIN 之前把每一边压缩成每个目的地一行。没有它，每个航班都会和另一边飞往同一机场的每个航班匹配，又是扇出。

自连接：一张表和自己 JOIN

给同一张表起两个别名，就可以拿它的行互相比较。哪些城市有不止一个机场？

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;

条件 a1.airport_code < a2.airport_code 有两个作用：防止机场和自己匹配，并且让每一对只出现一次而不是两次（有 DME–SVO，没有 SVO–DME）。找中转也是用自连接：把一个航班的到达和几小时后从同一机场出发的航班连起来。

多表连续 JOIN

JOIN 可以自然地串起来。按城市统计到达旅客，路径是 航段 → 航班 → 机场：

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;

这里每一步都是多对一（一个航班有多个航段，一个机场有多个航班），所以计数始终停留在航段粒度上，结果正确。麻烦出现在 JOIN 反方向走的时候。

扇出：JOIN 为什么会让行数翻倍

当「一」那一边的一行遇到「多」那一边的多行时，它会按匹配次数被复制。订单 65A6EA 有五张机票：

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;

这笔订单的 10,656 美元现在出现了五次。这些行本身没有问题，错误发生在你对它们求和的时候。

重复计算的实际例子

8 月 2 日所下订单的收入，直接从 bookings 计算：

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

同样的求和，但先连接了机票表（比如有人还想顺便统计机票数）：

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';

收入凭空多了 66%，而且没有任何报错。COUNT(DISTINCT book_ref) 仍然显示 5,761，这让被放大的总和看起来很可信。

看似顺手的修正 SUM(DISTINCT b.total_amount) 反而更糟：它返回 1,750,097.00，因为金额恰好相同的不同订单被合并成了一个。

两个扇出同时发生

把两张互相独立的「多」表连接到同一个父表上，它们会彼此相乘。航班 4729 卖出了 161 个航段，发了 161 张登机牌：

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。每一行航段都和该航班的每一行登机牌配了对。

如何避免重复计算

1. 先聚合，再 JOIN。 先把每张「多」表压缩到父表的粒度，再做一对一 JOIN：

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';

一个查询里同时得到正确的收入和机票数。

2. 在度量值自己的粒度上求和。 如果金额本来就在「多」的那一边，就在那一边求和。同一批订单的航段金额加起来，恰好等于订单总额：

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';

结果是 4563873.00。这里的扇出是无害的，因为每个 amount 只属于一行航段。

3. 用标量子查询做彼此独立的计数。 针对两个扇出同时发生的情况：

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. 检查行数。 相信一个总数之前，先比较 JOIN 前后的 COUNT(*)，或者像上面那样拿源表的总数来核对。如果行数变多了，而你并没有预料到，说明某个你以为唯一的键其实并不唯一。

JOIN 之后怎样筛选分组结果，见 GROUP BY 和 HAVING 的区别；JOIN 也是 SQL 面试题 里的常客。想动手练习，可以做 SQL 练习题 里的第 12、13 题。

常见问题

INNER JOIN 和 LEFT JOIN 有什么区别？

INNER JOIN 只返回两边都匹配上的行。LEFT JOIN 返回左表的每一行；匹配不到时，右表的列为 NULL。在上面的例子里，INNER JOIN 丢掉了没有航班的 A320 机型，LEFT JOIN 则保留了它。

JOIN 和 INNER JOIN 是一样的吗？

是的。不加关键字的 JOIN 就是内连接。同样，LEFT JOIN 和 LEFT OUTER JOIN 也是同一个东西。

为什么 JOIN 之后的行数比原表还多？

因为另一边的键不唯一：每一行会按匹配行数被重复。对一对多的 JOIN 来说这是正常的；只有当你接着对属于「一」那一边的列求和时，它才会变成 bug。

什么是反连接？

反连接返回一张表中在另一张表里找不到匹配行的那些行。在 SQL 里用 NOT EXISTS 或 LEFT JOIN ... WHERE right_key IS NULL 来写。PostgreSQL 里没有 ANTI JOIN 这个关键字。

什么时候需要 FULL OUTER JOIN？

当你要比较两个集合，并想在一个结果里同时看到「只在第一个里」「只在第二个里」「两边都有」的时候：比如核对两个系统的数据、两个时间段，或者两个机场的航线清单。

其他语言： Español, Português (Brasil), Tiếng Việt, English