大家好,又见面了,我是你们的朋友全栈君。如果您正在找激活码,请点击查看最新教程,关注关注公众号 “全栈程序员社区” 获取激活教程,可能之前旧版本教程已经失效.最新Idea2022.1教程亲测有效,一键激活。
Jetbrains全系列IDE使用 1年只要46元 售后保障 童叟无欺
区别
- 内连接(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)
左外连接和右外连接是相对的,主要就是以哪个表为主表去进行关联
发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/178383.html原文链接:https://javaforall.cn
【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛
【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...