• 浅谈 MySQL 连表查询


    浅谈 MySQL 连表查询

    连表查询是一把双刃剑, 优点是适应范式, 减少数据冗余; 缺点是连表查询特别是多张表的连表会增加数据库的负担, 降低查询效率.

    简介

    连表查询就是 2 张表或者多张表的联合查询, 联合查询的结果称之为 "笛卡尔积", 假设 A 表中有 n 条记录, B 表中有 m 条记录, "笛卡尔积" 就是 n*m

    各种连表查询的本质就是对笛卡尔积的过滤

    • 全查询: 全量查询笛卡尔积, n*m 种结果, 不加关键字过滤
    • 内连接: 关键字是 INNER JOIN, JOIN, WHERE, 或者自然匹配(省略连表条件, 不建议使用!!!)
    • 外连接:
      • 左外连接: 关键字是 LEFT JOIN, LEFT OUTER JOIN
      • 右外连接: 关键字是 RIGHT JOIN, RIGHT OUTER JOIN

    这里有一张神图

    连表查询总结
    连表查询总结

    数据准备

    create table `user_a` (
      `aid` int(11NOT NULL AUTO_INCREMENT,
      `a_name` varchar(255NOT NULL,
      `age` smallint NOT NULL,
      PRIMARY KEY(`aid`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表A';

    create table `user_b` (
      `bid` int(11NOT NULL AUTO_INCREMENT,
      `b_name` varchar(255NOT NULL,
      `age` smallint NOT NULL,
      PRIMARY KEY(`bid`)
    ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT '测试表B';

    # 插入测试数据
    INSERT INTO `user_a`(aid, a_name, age) VALUES(1'test1'1),(2'test2'2),(3'test3'3);
    INSERT INTO `user_b`(bid, b_name, age) VALUES(1'test2'2),(2'test3'3),(4'test4'4);

    全查询

    全查询
    全查询
    # 全查询: 下面的结果都是等价的
    # [推荐] INNER JOIN
    SELECT * FROM user_a INNER JOIN user_b;
    SELECT * FROM user_a, user_b;
    SELECT * FROM user_a JOIN user_b;
    SELECT * FROM user_a CROSS JOIN user_b;

    结果: 全量查询 n*m 种可能性

    全量查询结果
    全量查询结果

    内连接

    内连接
    内连接

    对 n*m 的笛卡尔积进行过滤, 用 WHERE 或者 ON 关键字都是等价的(内连接是等价的, 外连接不是等价的)

    (建议使用 ON 关键字, 约定俗称)

    # 内连接: 下面的结果都是等价的
    # [推荐] INNER JOIN + ON
    SELECT * FROM user_a a INNER JOIN user_b b ON a.a_name=b.b_name;
    # INNER JOIN + WHERE
    SELECT * FROM user_a a JOIN user_b b ON a.a_name=b.b_name;
    # JOIN + ON
    SELECT * FROM user_a a JOIN user_b b ON a.a_name=b.b_name;
    # JOIN + WHERE
    SELECT * FROM user_a a JOIN user_b b WHERE a.a_name=b.b_name;
    # 多表 + WHERE
    SELECT * FROM user_a a, user_b b WHERE a.a_name=b.b_name;

    结果: 只有左边和右边同时存在才会返回再结果集里面

    内连接结果
    内连接结果

    外连接

    外连接包括左连接和右连接

    左连接

    左连接
    左连接
    # 左连接
    # [唯一写法] LEFT JOIN + ON
    SELECT * FROM user_a a LEFT JOIN user_b b ON a.a_name=b.b_name;

    结果: 保证左边的数据完整, 如果右边没有该数据, 则右边的数据为 NULL

    左连接结果
    左连接结果

    右连接

    右连接
    右连接
    # 右连接
    # [唯一写法] RIGHT JOIN + ON
    SELECT * FROM user_a a RIGHT JOIN user_b b ON a.a_name=b.b_name;

    结果: 保证右边的数据完整, 如果左边没有该数据, 则左边的数据为 NULL

    右连接结果
    右连接结果

    ON 和 WHERE 的区别

    ON 和 WHERE 在内连表的时候是没有区别的 (推荐使用 INNER JOIN + ON 的规范写法)

    外连接只能用 ON, 外连接用 WHERE 直接语法错误

    为什么要小表驱动大表

    因为连接查询的本质是遍历左边表的记录去匹配右边表的记录, 左边表的复杂度是 O(n), 右边表能走索引 O(log n)

    总结

    • 全量查询: 推荐使用 INNER JOIN 查询整个笛卡尔集, 不常用
    • 内连接: 推荐使用 INNER JOIN + ON
    • 左连接: 推荐使用 LEFT JOIN + ON
    • 右连接: 推荐使用 RIGHT JOIN + ON
    • 使用小表驱动大表, 因为驱动表的复杂度是 O(n), 被驱动表的复杂度是 O(log n)

    reference

    https://juejin.cn/post/7043811976270577672

    本文由 mdnice 多平台发布

  • 相关阅读:
    is not allowed to connect to this mysql server
    如何使用ASO优化来提高应用的安装率
    go中父协程与子协程的生命周期(子协程能否使用主协程变量)
    Java 通过页码范围提取新的文档(docx、pdf)
    0开篇-介绍
    vivado 时序约束
    振动监测:物联网预测性维护的“听诊器”
    圣诞老人遇见 GenAI:利用大语言模型、LangChain 和 Elasticsearch 破译手写的圣诞信件
    CSS餐厅练习链接及答案
    基于萤火虫算法优化的BP神经网络预测模型附Matlab代码
  • 原文地址:https://blog.csdn.net/jarvan5/article/details/127070481