MySQL中内连接和外连接实现不同在哪
导读:这篇文章给大家分享的是“MySQL中内连接和外连接实现不同在哪”,文中的讲解内容简单清晰,对大家学习和理解有一定的参考价值和帮助,有这方面学习需要的朋友,接下来就跟随小编一起学习一下“MySQL中内连接和外连接实现不同在哪”吧。...
这篇文章给大家分享的是“MySQL中内连接和外连接实现不同在哪”,文中的讲解内容简单清晰,对大家学习和理解有一定的参考价值和帮助,有这方面学习需要的朋友,接下来就跟随小编一起学习一下“MySQL中内连接和外连接实现不同在哪”吧。
mysql内连接和外连接的区别:内连接会取出连接表中匹配到的数据,匹配不到的不保留;而外连接会取出连接表中匹配到的数据,匹配不到的也会保留,其值为NULL。
本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。
区别
- 内连接(inner join):取出连接表中匹配到的数据,匹配不到的不保留
- 外连接(outer join):取出连接表中匹配到的数据,匹配不到的也会保留,其值为NULL
示例表
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)
- 左外连接(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中内连接和外连接实现不同在哪已经有了进一步的了解,如果想要了解更多相关的内容,欢迎关注网络,网络将为大家推送更多相关知识点的文章。
声明:本文内容由网友自发贡献,本站不承担相应法律责任。对本内容有异议或投诉,请联系2913721942#qq.com核实处理,我们将尽快回复您,谢谢合作!
若转载请注明出处: MySQL中内连接和外连接实现不同在哪
本文地址: https://pptw.com/jishu/650944.html