• 技术文章 >数据库 >mysql教程

    mysql内连接和外连接有什么区别

    青灯夜游青灯夜游2022-01-06 14:50:26原创149

    mysql内连接和外连接的区别:内连接会取出连接表中匹配到的数据,匹配不到的不保留;而外连接会取出连接表中匹配到的数据,匹配不到的也会保留,其值为NULL。

    本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。

    区别

    示例表

    users表

    mysql> select * from users;
    +----+-------+
    | id | name  |
    +----+-------+
    |  1 | john  |
    |  2 | May   |
    |  3 | Lucy  |
    |  4 | Jack  |
    |  5 | James |
    +----+-------+
    5 rows in set (0.00 sec)

    topics表

    mysql> select * from topics;
    +----+---------------------------------------+---------+
    | id | title                                 | user_id |
    +----+---------------------------------------+---------+
    |  1 |  Hello world                          |       1 |
    |  2 | PHP is the best language in the world |       2 |
    |  3 | Laravel artist                        |       6 |
    +----+---------------------------------------+---------+
    3 rows in set (0.00 sec)

    内连接(inner join)

    mysql> select * from users as u inner join topics as t on u.id=t.user_id;
    +----+------+----+---------------------------------------+---------+
    | id | name | id | title                                 | user_id |
    +----+------+----+---------------------------------------+---------+
    |  1 | john |  1 |  Hello world                          |       1 |
    |  2 | May  |  2 | PHP is the best language in the world |       2 |
    +----+------+----+---------------------------------------+---------+
    2 rows in set (0.00 sec)

    inner可以省略,as是给表起别名,也可以省略

    mysql> select * from users u join topics t on u.id=t.user_id;
    +----+------+----+---------------------------------------+---------+
    | id | name | id | title                                 | user_id |
    +----+------+----+---------------------------------------+---------+
    |  1 | john |  1 |  Hello world                          |       1 |
    |  2 | May  |  2 | PHP is the best language in the world |       2 |
    +----+------+----+---------------------------------------+---------+
    2 rows in set (0.00 sec)

    以上两句等价于

    mysql> select * from users,topics where users.id=topics.user_id;
    +----+------+----+---------------------------------------+---------+
    | id | name | id | title                                 | user_id |
    +----+------+----+---------------------------------------+---------+
    |  1 | john |  1 |  Hello world                          |       1 |
    |  2 | May  |  2 | PHP is the best language in the world |       2 |
    +----+------+----+---------------------------------------+---------+
    2 rows in set (0.00 sec)

    外连接(outer join)

    以某一个表为主表,进行关联查询,不管能不能关联的上,主表的数据都会保留,关联不上的以NULL显示

    通俗解释就是:先拿出主表的所有数据,然后到关联的那张表去找有没有符合关联条件的数据,如果有,正常显示,如果没有,显示为NULL

    示例

    mysql> select * from users as u left join topics as t on u.id=t.user_id;
    +----+-------+------+---------------------------------------+---------+
    | id | name  | id   | title                                 | user_id |
    +----+-------+------+---------------------------------------+---------+
    |  1 | john  |    1 |  Hello world                          |       1 |
    |  2 | May   |    2 | PHP is the best language in the world |       2 |
    |  3 | Lucy  | NULL | NULL                                  |    NULL |
    |  4 | Jack  | NULL | NULL                                  |    NULL |
    |  5 | James | NULL | NULL                                  |    NULL |
    +----+-------+------+---------------------------------------+---------+
    5 rows in set (0.00 sec)

    等价于以下,只是字段的位置不一样

    mysql> select * from topics as t right join users as u on u.id=t.user_id;
    +------+---------------------------------------+---------+----+-------+
    | id   | title                                 | user_id | id | name  |
    +------+---------------------------------------+---------+----+-------+
    |    1 |  Hello world                          |       1 |  1 | john  |
    |    2 | PHP is the best language in the world |       2 |  2 | May   |
    | NULL | NULL                                  |    NULL |  3 | Lucy  |
    | NULL | NULL                                  |    NULL |  4 | Jack  |
    | NULL | NULL                                  |    NULL |  5 | James |
    +------+---------------------------------------+---------+----+-------+
    5 rows in set (0.00 sec)

    左外连接和右外连接是相对的,主要就是以哪个表为主表去进行关联

    【相关推荐:mysql视频教程

    以上就是mysql内连接和外连接有什么区别的详细内容,更多请关注php中文网其它相关文章!

    声明:本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系admin@php.cn核实处理。
    专题推荐:mysql 内连接 外连接
    上一篇:mysql怎么实现除法保留两位小数 下一篇:mysql端口号是多少

    相关文章推荐

    • mysql怎么实现小写转大写• 让人心动的mysql体系结构和InnoDB存储引擎知识详解• php7连不上mysql8怎么解决• mysql怎么求交集• mysql需要配置环境变量吗

    全部评论我要评论

  • 取消发布评论发送
  • 1/1

    PHP中文网