mysql中left join,right join,inner join的区别
来源:互联网 发布:mac电脑程序强制退出 编辑:程序博客网 时间:2024/05/22 11:39
sql查询中有一个非常重要的环节就是表的关联查询,一般使用left join,right join,inner join,他们之间的区别是什么呢?
下面我们通过具体的sql语句来演示,演示用的表名为test1/test2:
mysql> select * from test1;
+----+--------+------+------+
| id | name | age | sex |
+----+--------+------+------+
| 0 | 小董 | 20 | 男 |
| 1 | 小王 | 15 | 男 |
| 2 | 小李 | 18 | 男 |
| 3 | 小红 | 16 | 女 |
+----+--------+------+------+
4 rows in set (0.00 sec)
mysql> select * from test2;
+----+--------+-----------------------+-------+
| id | name | interest | score |
+----+--------+-----------------------+-------+
| 1 | 小王 | 打篮球 | 81 |
| 2 | 小李 | 踢足球 | 84 |
| 3 | 小红 | 看动漫 | 99 |
| 4 | 小军 | 打游戏,打篮球 | 100 |
+----+--------+-----------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from test1 t1 left join test2 t2 on t1.id=t2.id and t1.name=t2.name;
+----+--------+------+------+------+--------+-----------+-------+
| id | name | age | sex | id | name | interest | score |
+----+--------+------+------+------+--------+-----------+-------+
| 1 | 小王 | 15 | 男 | 1 | 小王 | 打篮球 | 81 |
| 2 | 小李 | 18 | 男 | 2 | 小李 | 踢足球 | 84 |
| 3 | 小红 | 16 | 女 | 3 | 小红 | 看动漫 | 99 |
| 0 | 小董 | 20 | 男 | NULL | NULL | NULL | NULL |
+----+--------+------+------+------+--------+-----------+-------+
4 rows in set (0.12 sec)
mysql> select * from test2 t1 left join test1 t2 on t1.id=t2.id and t1.name=t2.name;
+----+--------+-----------------------+-------+------+--------+------+------+
| id | name | interest | score | id | name | age | sex |
+----+--------+-----------------------+-------+------+--------+------+------+
| 1 | 小王 | 打篮球 | 81 | 1 | 小王 | 15 | 男 |
| 2 | 小李 | 踢足球 | 84 | 2 | 小李 | 18 | 男 |
| 3 | 小红 | 看动漫 | 99 | 3 | 小红 | 16 | 女 |
| 4 | 小军 | 打游戏,打篮球 | 100 | NULL | NULL | NULL | NULL |
+----+--------+-----------------------+-------+------+--------+------+------+
4 rows in set (0.00 sec)
mysql> select t1.id,t1.name,t1.interest,t1.score,t2.age,t2.sex from test2 t1 left join test1 t2 on t1.id=t2.id and t1.name=t2.name;
+----+--------+-----------------------+-------+------+------+
| id | name | interest | score | age | sex |
+----+--------+-----------------------+-------+------+------+
| 1 | 小王 | 打篮球 | 81 | 15 | 男 |
| 2 | 小李 | 踢足球 | 84 | 18 | 男 |
| 3 | 小红 | 看动漫 | 99 | 16 | 女 |
| 4 | 小军 | 打游戏,打篮球 | 100 | NULL | NULL |
+----+--------+-----------------------+-------+------+------+
4 rows in set (0.02 sec)
mysql> select t1.id,t1.name,t1.interest,t1.score,t2.age,t2.sex from test2 t1 inner join test1 t2 on t1.id=t2.id and t1.name=t2.name;
+----+--------+-----------+-------+------+------+
| id | name | interest | score | age | sex |
+----+--------+-----------+-------+------+------+
| 1 | 小王 | 打篮球 | 81 | 15 | 男 |
| 2 | 小李 | 踢足球 | 84 | 18 | 男 |
| 3 | 小红 | 看动漫 | 99 | 16 | 女 |
+----+--------+-----------+-------+------+------+
3 rows in set (0.00 sec)
mysql> select * from test1 t1 left join test2 t2 on t1.id=t2.id and t1.name=t2.name;
+----+--------+------+------+------+--------+-----------+-------+
| id | name | age | sex | id | name | interest | score |
+----+--------+------+------+------+--------+-----------+-------+
| 1 | 小王 | 15 | 男 | 1 | 小王 | 打篮球 | 81 |
| 2 | 小李 | 18 | 男 | 2 | 小李 | 踢足球 | 84 |
| 3 | 小红 | 16 | 女 | 3 | 小红 | 看动漫 | 99 |
| 0 | 小董 | 20 | 男 | NULL | NULL | NULL | NULL |
+----+--------+------+------+------+--------+-----------+-------+
4 rows in set (0.00 sec)
mysql> select * from test1 t1 right join test2 t2 on t1.id=t2.id and t1.name=t2.name;
+------+--------+------+------+----+--------+-----------------------+-------+
| id | name | age | sex | id | name | interest | score |
+------+--------+------+------+----+--------+-----------------------+-------+
| 1 | 小王 | 15 | 男 | 1 | 小王 | 打篮球 | 81 |
| 2 | 小李 | 18 | 男 | 2 | 小李 | 踢足球 | 84 |
| 3 | 小红 | 16 | 女 | 3 | 小红 | 看动漫 | 99 |
| NULL | NULL | NULL | NULL | 4 | 小军 | 打游戏,打篮球 | 100 |
+------+--------+------+------+----+--------+-----------------------+-------+
4 rows in set (0.00 sec)
mysql> select * from test1 t1 inner join test2 t2 on t1.id=t2.id and t1.name=t2.name;
+----+--------+------+------+----+--------+-----------+-------+
| id | name | age | sex | id | name | interest | score |
+----+--------+------+------+----+--------+-----------+-------+
| 1 | 小王 | 15 | 男 | 1 | 小王 | 打篮球 | 81 |
| 2 | 小李 | 18 | 男 | 2 | 小李 | 踢足球 | 84 |
| 3 | 小红 | 16 | 女 | 3 | 小红 | 看动漫 | 99 |
+----+--------+------+------+----+--------+-----------+-------+
3 rows in set (0.00 sec)
从上面的截图可以很容易看出来三者之间的区别,left join 查出来的数据条数是以查询的表为主,right join查出来的数据条数是以关联表为主,inner join查出来的数据是在查询条件在两者的交集
- mysql中left join、right join、inner join的区别
- MYSQL中 inner join left join right join的区别
- mysql中left join,right join,inner join的区别
- mysql left join,right join,inner join 的区别
- mysql之left join、right join、inner join的区别
- mysql之left join、right join、inner join的区别
- mysql之left join、right join、inner join的区别
- MySQL之left join、right join、inner join的区别
- mysql left join,right join,inner join的区别
- sql中left join、right join、inner join的区别
- SQL中left join,right join,inner join的区别
- SQL中inner join,left join,right join的区别
- mysql中left join,right join,inner join,outer join的区别
- MySQL中LEFT JOIN 、RIGHT JOIN、INNER JOIN、FULL JOIN 的区别
- inner join,outer join,left join,right join的区别
- inner join,outer join,left join,right join的区别
- inner join,outer join,left join,right join的区别
- inner join, left join, right join, full join 的区别
- 【动态更新】解决夫妻两地分居手续
- ThreadLocal源码分析
- 图片加载机制的比较
- opencv 图像直方图
- 利用SpringCloud搭建一个最简单的微服务框架
- mysql中left join,right join,inner join的区别
- OnlineObject Tracking:A Benchmark (转)
- Windows下当前权限上下文
- Ehcache缓存-简单入门
- react中间件的概念
- 激活函数
- ECMAScript 6 扫盲
- Hanoi递归
- CentOS6.5升级内核到3.10.28