22.表的连接方式说明

表的连接方式说明

目录

• 表的连接方式说明
  • 1. 内连接（INNER JOIN / JOIN）
  • 2. 左外连接（LEFT OUTER JOIN / LEFT JOIN）
  • 3. 右外连接（RIGHT OUTER JOIN / RIGHT JOIN）
  • 4. 全外连接（FULL OUTER JOIN / FULL JOIN）
  • 5. 交叉连接（CROSS JOIN）
  • 6. 自然连接（NATURAL JOIN）
  • 7. 自连接（SELF JOIN）
  • 8. 连接方式总结对比表
    • 图形化示意（维恩图风格）
    • 选择建议

以下是 SQL 中常见表连接方式的详细介绍，包括每种连接的定义、语法、返回结果的特征，并辅以示例数据和图形示意（使用文本表格和维恩图风格），帮助理解。

1. 内连接（INNER JOIN / JOIN）

定义：返回两个表中满足连接条件的行。不保留任何不匹配的行。
语法：

SELECT * FROM table1 INNER JOIN table2 ON table1.key = table2.key;

结果特征：只包含匹配的行，结果集行数 = 匹配组合数。

示例数据：
customers 表：

cust_id	name	city
1	Alice	Beijing
2	Bob	Shanghai
3	Carol	Beijing

orders 表：

order_id	cust_id	amount
101	1	200
102	1	150
103	3	300

内连接查询：

SELECT c.name, o.order_id, o.amount
FROM customers c INNER JOIN orders o ON c.cust_id = o.cust_id;


结果：
name	order_id	amount
Alice	101	200
Alice	102	150
Carol	103	300
Bob 没有订单，因此不在结果中。

图形示意：

   customers          orders
  ┌─────────┐       ┌─────────┐
  │ 1 Alice │       │ 101  1  │
  │ 2 Bob   │       │ 102  1  │
  │ 3 Carol │       │ 103  3  │
  └─────────┘       └─────────┘
        │                │
        └────────────────┘
         内连接取交集
         结果： (1,Alice,101), (1,Alice,102), (3,Carol,103)

用维恩图表示：

    ┌───────────┐
    │ customers │
    │   ┌───┐   │
    │   │ ∩ │   │
    └───┼───┼───┘
        │   │ orders
        └───┘
   交集 = 内连接结果

2. 左外连接（LEFT OUTER JOIN / LEFT JOIN）

定义：返回左表（LEFT JOIN 关键字左边的表）的全部行，以及右表中满足连接条件的匹配行；如果右表无匹配，则右表列填充 NULL。
语法：

SELECT * FROM table1 LEFT JOIN table2 ON table1.key = table2.key;

结果特征：左表所有行都保留，右表可能为 NULL。

查询：

SELECT c.name, o.order_id, o.amount
FROM customers c LEFT JOIN orders o ON c.cust_id = o.cust_id;

结果：
name	order_id	amount
Alice	101	200
Alice	102	150
Bob	NULL	NULL
Carol	103	300
Bob 保留了，但订单信息为 NULL。

图形示意：

   customers          orders
  ┌─────────┐       ┌─────────┐
  │ 1 Alice │       │ 101  1  │
  │ 2 Bob   │       │ 102  1  │
  │ 3 Carol │       │ 103  3  │
  └─────────┘       └─────────┘
左表全部保留，右表只匹配部分。
结果包含 Bob 但订单为空。

维恩图：

    ┌───────────┐
    │ customers │
    │   ┌───┐   │
    │   │ ∩ │   │
    └───┼───┼───┐
        │   │ orders │
        └───┘       │
    左圆全部保留，右圆只取交集部分。

3. 右外连接（RIGHT OUTER JOIN / RIGHT JOIN）

定义：返回右表的全部行，以及左表中满足连接条件的匹配行；如果左表无匹配，则左表列填充 NULL。
语法：

SELECT * FROM table1 RIGHT JOIN table2 ON table1.key = table2.key;

结果特征：右表所有行都保留，左表可能为 NULL。
查询：

SELECT c.name, o.order_id, o.amount
FROM customers c RIGHT JOIN orders o ON c.cust_id = o.cust_id;

结果：

name	order_id	amount
Alice	101	200
Alice	102	150
Carol	103	300

注意：没有订单的客户（Bob）未出现，但右表所有订单都在结果中（这里所有订单都有匹配客户，所以看起来和内连接一样；若右表有订单对应不存在的客户，则会保留订单且左表列为 NULL）。

4. 全外连接（FULL OUTER JOIN / FULL JOIN）

定义：返回左右两表的所有行。当某行在另一表中无匹配时，另一表列填充 NULL。相当于 LEFT JOIN + RIGHT JOIN 的并集。
语法：

SELECT * FROM table1 FULL JOIN table2 ON table1.key = table2.key;

结果特征：两个表的全部行都保留，不匹配的部分补 NULL。
查询：

SELECT c.name, o.order_id, o.amount
FROM customers c FULL JOIN orders o ON c.cust_id = o.cust_id;

结果：

name	order_id	amount
Alice	101	200
Alice	102	150
Bob	NULL	NULL
Carol	103	300
（假设 orders 中没有额外订单，所以与左连接结果相同；若 orders 有对应不存在客户的订单，则会多出 NULL 客户行）		

5. 交叉连接（CROSS JOIN）

定义：返回两个表的笛卡尔积，即每一行与另一表的每一行组合。没有连接条件（或者 ON 1=1）。
语法：

SELECT * FROM table1 CROSS JOIN table2;

结果特征：结果行数 = 表1行数 × 表2行数。通常用于生成组合数据，慎用（可能产生巨大结果集）。

示例：
customers（3行） × orders（3行）= 9 行结果。

6. 自然连接（NATURAL JOIN）

定义：一种特殊的内连接，它基于两个表中所有同名列的等值条件自动连接，并自动去除重复的同名列（只保留一列）。不允许使用 ON 子句。
语法：

SELECT * FROM table1 NATURAL JOIN table2;

注意：

• 隐式连接条件，不易控制，可能因无意中增加同名列而导致非预期结果。
• 可读性差，不推荐在生产环境中使用（尤其在复杂表结构中）。
  示例：若 customers 和 orders 都有 cust_id 列，则自然连接等价于 ON customers.cust_id = orders.cust_id。结果中 cust_id 只出现一次。

7. 自连接（SELF JOIN）

定义：将同一个表与自身连接，通常需要为表起不同的别名。可用于比较同一表中不同行之间的关系（如员工与经理）。
语法：

SELECT a.*, b.* FROM table a JOIN table b ON a.id = b.parent_id;

图形：实质上是内连接或外连接的特殊形式，只是表是同一个。

8. 连接方式总结对比表

连接类型	保留左表不匹配行	保留右表不匹配行	典型使用场景
INNER JOIN	否	否	仅取匹配数据
LEFT JOIN	是	否	主表数据全要，附加关联信息
RIGHT JOIN	否	是	从表数据全要，反查主表
FULL JOIN	是	是	合并两表所有数据，标记缺失
CROSS JOIN	所有组合	所有组合	生成测试数据或排列组合
NATURAL JOIN	自动匹配同名列	自动匹配同名列	简化书写，但易出错，慎用

图形化示意（维恩图风格）

1. INNER JOIN         2. LEFT JOIN          3. RIGHT JOIN         4. FULL JOIN
    ┌───┐                ┌───┐                 ┌───┐                 ┌───┐
    │ A │                │ A │                 │ A │                 │ A │
    │ ┌─┼─┐              │ ┌─┼─┐               │ ┌─┼─┐               │ ┌─┼─┐
    │ │∩│ │              │ │∩│ │               │ │∩│ │               │ │∩│ │
    └─┼─┘ │              └─┼─┘ │               └─┼─┘ │               └─┼─┘ │
      │ B │                │ B │                 │ B │                 │ B │
      └───┘                └───┘                 └───┘                 └───┘
    只取∩               A全部 + ∩             B全部 + ∩            A+B全部

注意：维恩图是集合的交并补概念，但实际 SQL 连接是基于行匹配的，可能产生多行，而集合论中元素唯一。这里仅用于直观理解行是否被包含。

选择建议

• 需要精确匹配数据 → INNER JOIN
• 需要主表全部数据（即使无关联） → LEFT JOIN
• 需要从表全部数据 → RIGHT JOIN（通常可以用 LEFT JOIN 调换表顺序替代）
• 需要两表全部数据并标缺失 → FULL JOIN
• 生成所有组合 → CROSS JOIN（注意性能）
• NATURAL JOIN 应尽量避免，使用显式 ON 条件更清晰可控。
• 掌握这些连接方式，可以应对绝大部分多表查询需求。