深入解析：SQL连接类型解析：从交集到并集

核心概念总结

  • INNER JOIN（内连接）：只返回两个表中连接条件匹配 的行。取的是两个表的“交集”。

  • LEFT JOIN（左连接）：返回左表的所有行，以及右表中连接条件匹配的行。如果右表没有匹配的行，则结果集中右表的列返回 NULL。它关注的是“左表的全部”。

  • RIGHT JOIN（右连接）：与左连接相反。返回右表的所有行，以及左表中连接条件匹配的行。如果左表没有匹配的行，则结果集中左表的列返回 NULL。它关注的是“右表的全部”。

  • FULL OUTER JOIN（全外连接）：返回左表和右表中的所有行。当某一行在另一个表中没有匹配时，另一个表的列将包含 NULL。它取的是两个表的“并集”。

举例说明

假设我们有两个简单的表：

Customers 表（左表）

customer_idname
1张三
2李四
3王五

Orders 表（右表）

order_idamountcustomer_id
1012001
1025002
1033001
1048004(注意：客户4在Customers表中不存在)

1. INNER JOIN（内连接）

查询： 找出所有客户及其对应的订单（只找有订单的客户和有客户的订单）。

SELECT
    Customers.name,
    Orders.order_id,
    Orders.amount
FROM Customers
INNER JOIN Orders ON Customers.customer_id = Orders.customer_id;

结果：

nameorder_idamount
张三101200
张三103300
李四102500

解释：

  • 只返回了 Customers 表和 Orders 表中 customer_id 能匹配上的行。

  • 王五 没有订单，所以没有出现。

  • 订单 104 的客户 4 在客户表中不存在，所以也没有出现。

2. LEFT JOIN（左连接）

查询： 列出所有客户，以及他们下的订单（即使客户一个订单也没有）。

SELECT
    Customers.name,
    Orders.order_id,
    Orders.amount
FROM Customers
LEFT JOIN Orders ON Customers.customer_id = Orders.customer_id;

结果：

nameorder_idamount
张三101200
张三103300
李四102500
王五NULLNULL

解释：

  • 左表 Customers 的所有记录都被返回。

  • 张三 和 李四 有匹配的订单，所以订单信息正常显示。

  • 王五 没有订单，所以在 Orders 表的列（order_id, amount）上用 NULL 填充。

  • 订单 104 依然没有出现，因为它不属于任何左表中的客户。

3. RIGHT JOIN（右连接）

查询： 列出所有订单，以及对应的客户信息（即使订单找不到对应的客户）。

SELECT
    Customers.name,
    Orders.order_id,
    Orders.amount
FROM Customers
RIGHT JOIN Orders ON Customers.customer_id = Orders.customer_id;

结果：

nameorder_idamount
张三101200
李四102500
张三103300
NULL104800

解释：

  • 右表 Orders 的所有记录都被返回。

  • 订单 101, 102, 103 都找到了对应的客户。

  • 订单 104 的客户 4 在 Customers 表中不存在，所以在 Customers 表的列（name）上用 NULL 填充。

  • 王五 没有出现在结果中，因为右表 Orders 里没有他的订单。

4. FULL OUTER JOIN（全外连接）

查询： 列出所有客户和所有订单，不管它们之间是否有关系。

-- 注意：MySQL不支持FULL OUTER JOIN，但其他数据库如PostgreSQL, SQL Server支持。
SELECT
    Customers.name,
    Orders.order_id,
    Orders.amount
FROM Customers
FULL OUTER JOIN Orders ON Customers.customer_id = Orders.customer_id;

结果：

nameorder_idamount
张三101200
李四102500
张三103300
王五NULLNULL
NULL104800

解释：

  • 它结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

  • 既包含了所有客户（即使没订单），也包含了所有订单（即使没客户）。

总结与类比（使用集合韦恩图）

可以想象两个圆圈：

  • 左圈：Customers 表

  • 右圈：Orders 表

  • 重叠部分：customer_id 匹配的行

连接类型相当于取...描述
INNER JOIN重叠部分只关心有关系的记录
LEFT JOIN左圈全部关心左表全部，右表只是补充信息
RIGHT JOIN右圈全部关心右表全部，左表只是补充信息
FULL OUTER JOIN两个圈所有部分所有数据我都要，不管有没有关系

其他连接

  • CROSS JOIN（交叉连接）：返回两个表的笛卡尔积，即左表的每一行与右表的每一行进行组合。结果集的行数 = 左表行数 × 右表行数。通常需要避免无意中使用。

  • SELF JOIN（自连接）：本质上是将一个表与它自己进行连接（通常是内连接或左连接），常用于查询有层级关系的数据，比如员工和经理都在同一个 Employees 表中。

SELF JOIN（自连接）

核心概念

SELF JOIN 是指一个表与自身进行连接。它不是一种特殊的 JOIN 语法，而是一种技术。你可以使用 INNER JOIN、LEFT JOIN 等任何连接类型来实现自连接。

为什么要自连接？

当表中的记录之间存在内部关系时，就需要自连接。

总结对比

特性CROSS JOINSELF JOIN
本质两个不同表的笛卡尔积同一个表与自身连接
连接条件不需要 ON 子句需要 ON 子句来定义表内关系
结果集大小行数 = 表A行数 × 表B行数取决于连接条件和连接类型
主要用途生成所有组合、测试数据查询层级关系、内部关联
语法特点可省略 ON 子句必须使用表别名

语法

由于是同一个表，必须使用表别名来区分两个"实例"。

SELECT
    A.column1,
    B.column2
FROM table_name A
JOIN table_name B ON A.common_column = B.common_column;

示例：员工层级关系

Employees 表

emp_idnamemanager_id
1AliceNULL
2Bob1
3Carol1
4David2

查询： 找出每个员工及其经理的名字

SELECT
    E.name AS employee_name,
    M.name AS manager_name
FROM Employees E
LEFT JOIN Employees M ON E.manager_id = M.emp_id;

结果：

employee_namemanager_name
AliceNULL
BobAlice
CarolAlice
DavidBob

更多自连接示例

查询有相同经理的员工对：

SELECT
    A.name AS employee1,
    B.name AS employee2,
    M.name AS manager_name
FROM Employees A
JOIN Employees B ON A.manager_id = B.manager_id AND A.emp_id < B.emp_id
JOIN Employees M ON A.manager_id = M.emp_id;

结果：

employee1employee2manager_name
BobCarolAlice

  • 使用场景

  • 层级关系查询（组织架构、分类树）

  • 查找相似或相关的记录

  • 分析序列或时间线数据

  • 查找重复或模式匹配

  • CROSS JOIN（交叉连接）

    核心概念

    CROSS JOIN 返回两个表的笛卡尔积。这意味着左表中的每一行都会与右表中的每一行进行组合。它不需要任何连接条件（ON子句）。

    语法

    SELECT *
FROM table1
CROSS JOIN table2;
-- 或者使用隐式语法（不推荐，容易混淆）
SELECT *
FROM table1, table2;

    示例

    假设我们有两个小表：

    Colors 表

    color_idcolor_name
    1Red
    2Blue

    Sizes 表

    size_idsize_name
    SSmall
    MMedium
    LLarge

    CROSS JOIN 查询：

    SELECT
    Colors.color_name,
    Sizes.size_name
FROM Colors
CROSS JOIN Sizes;

    结果：

    color_namesize_name
    RedSmall
    RedMedium
    RedLarge
    BlueSmall
    BlueMedium
    BlueLarge

    结果集行数 = 2 colors × 3 sizes = 6行

    使用场景

  • 生成所有可能的组合（如上面的颜色和尺寸例子）

  • 创建测试数据

  • 生成序列或矩阵

  • 进行系统性的计算（如每个产品在所有地区的销售目标）

  • 如果表很大，CROSS JOIN 会产生极其庞大的结果集

  • 要谨慎使用，确保这是你真正需要的

posted @ 2025-12-14 17:24  yangykaifa  阅读(22)  评论(0)    收藏  举报