大橙子网站建设,新征程启航
为企业提供网站建设、域名注册、服务器等服务
这篇文章主要介绍“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)
示例
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)
左外连接(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内连接和外连接有哪些区别”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!