[mysql]inner join/left join/right join/full outer join

import_random
2,860 阅读3分钟

图片.png

在 MySQL 中，JOIN 操作用于将多个表中的数据关联查询。

以下是 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL OUTER JOIN 的详细用法、区别及示例：

1. INNER JOIN（内连接）

  • 作用：返回两个表中满足连接条件的记录。

  • 特点：仅保留双方匹配的行，不匹配的行被丢弃。

  • 语法：

    SELECT columns
FROM table1
INNER JOIN table2 ON table1.column = table2.column;

  • 示例：

    • 表A（用户）和表B（订单）通过 user_id 关联：

      SELECT *
FROM users A
INNER JOIN orders B ON A.user_id = B.user_id;

    • 结果：仅包含有订单的用户及其订单信息。

2. LEFT JOIN（左连接）

  • 作用：返回左表（table1）的所有记录，以及右表（table2）中匹配的记录。若右表无匹配，右侧字段为 NULL。

  • 特点：保留左表全部数据，右表无匹配则填充 NULL。

  • 语法：

    SELECT columns
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

  • 示例：

    SELECT A.name, B.order_id
FROM users A
LEFT JOIN orders B ON A.user_id = B.user_id;
    • 结果：所有用户（包括无订单的用户），无订单的用户的 order_id 为 NULL。

3. RIGHT JOIN（右连接）

  • 作用：返回右表（table2）的所有记录，以及左表（table1）中匹配的记录。若左表无匹配，左侧字段为 NULL。

  • 特点：保留右表全部数据，左表无匹配则填充 NULL。

  • 语法：

    SELECT columns
FROM table1
RIGHT JOIN table2 ON table1.column = table2.column;

  • 示例：

    SELECT A.name, B.order_id
FROM users A
RIGHT JOIN orders B ON A.user_id = B.user_id;
    • 结果：所有订单（包括无用户的订单），无用户的订单的 name 为 NULL。

4. FULL OUTER JOIN（全外连接）

  • 作用：返回左右两表的全部记录。若某侧无匹配，对应字段为 NULL。

  • 特点：MySQL 不直接支持 FULL OUTER JOIN，需通过 LEFT JOIN 和 RIGHT JOIN 的 UNION 实现。

  • 语法（模拟）：

    SELECT columns FROM table1 LEFT JOIN table2 ON ...
UNION
SELECT columns FROM table1 RIGHT JOIN table2 ON ... WHERE table1.column IS NULL;

  • 示例：

    SELECT A.name, B.order_id
FROM users A LEFT JOIN orders B ON A.user_id = B.user_id
UNION
SELECT A.name, B.order_id
FROM users A RIGHT JOIN orders B ON A.user_id = B.user_id
WHERE A.user_id IS NULL;
    • 结果：所有用户和所有订单的组合，无匹配的部分为 NULL。

    UNION 的核心作用

    • 上下合并结果集：将多个 SELECT 语句的结果合并为一个结果集。
    • 去重：默认情况下，UNION 会去除重复的行（保留唯一行）。
    • 保持列结构一致：要求所有 SELECT 语句的列数相同，且对应列的数据类型兼容。

关键区别总结

JOIN 类型保留左表数据保留右表数据匹配条件MySQL 直接支持
INNER JOIN仅匹配的行仅匹配的行双方匹配✔️
LEFT JOIN所有行仅匹配的行左表为主✔️
RIGHT JOIN仅匹配的行所有行右表为主✔️
FULL OUTER JOIN所有行所有行无要求（全保留）❌（需模拟）

示例数据与结果

假设：

  • 表A（users） ：

    user_idname
    1Alice
    2Bob
    3Charlie

  • 表B（orders） ：

    order_iduser_idamount
    1011100
    1022200
    1034300

  • INNER JOIN 结果：

    nameorder_idamount
    Alice101100
    Bob102200

  • LEFT JOIN 结果：

    nameorder_idamount
    Alice101100
    Bob102200
    CharlieNULLNULL

  • RIGHT JOIN 结果：

    nameorder_idamount
    Alice101100
    Bob102200
    NULL103300

  • FULL OUTER JOIN（模拟）结果：

    nameorder_idamount
    Alice101100
    Bob102200
    CharlieNULLNULL
    NULL103300

使用建议

  • 优先使用 INNER JOIN：当只需双方匹配的数据时。
  • 使用 LEFT JOIN：需保留左表全部记录（如统计用户订单，包括无订单用户）。
  • 慎用 RIGHT JOIN：通常可调换表顺序改用 LEFT JOIN，代码更易读。
  • 需要全外连接时：通过 UNION 模拟，注意去重。

通过理解这些 JOIN 的区别，可以更灵活地根据业务需求选择合适的数据关联方式。

avatar
文章
阅读
粉丝