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

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

MysqL内连接和外连接的区别：内连接会取出连接表中匹配到的数据，匹配不到的不保留；而外连接会取出连接表中匹配到的数据，匹配不到的也会保留，其值为NulL。

本教程 *** 作环境：windows7系统、MysqL8版本、Dell G3电脑。

区别内连接（inner join）：取出连接表中匹配到的数据，匹配不到的不保留外连接（outer join）：取出连接表中匹配到的数据，匹配不到的也会保留，其值为NulL

示例表

users表

@H_403_25@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)左外连接（left outer join）：以左边的表为主表右外连接（right 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内连接和外连接有什么区别全部内容，希望文章能够帮你解决mysql内连接和外连接有什么区别所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。