转自：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查出来的数据是在查询条件在两者的交集
转载于:https://www.cnblogs.com/xiaouisme/p/9226068.html
