转自：https://blog.csdn.net/qq_35975416/article/details/78842958
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查出来的数据是在查询条件在两者的交集