MySQL的几种连接 join/inner join/cross join/逗号/left join/right join/natural join

时间:2022-02-09 22:28:17
转载请注明出处!!
之前数据表连接操作多使用逗号或者join,对几种连接的概念一直浑浑噩噩,最近研究了一波,把这些连接的区别搞明白了。
 
连接:A xjoin B(主表 操作 关联表)
 select过程:from->where->group by->having->order by->limit
在不使用on语法时,join、inner join、逗号、cross join结果相同,都是取2个表的笛卡尔积。逗号与其他操作符优先级不同,所以有可能产生语法错误,尽量减少用逗号
 
join、inner join、cross join支持on和using语法,逗号不支持on和using语法
on语法:筛选连接后的结果,两表的对应列值相同才在结果集中,可以通过and连接多个列值的匹配要求,列名可以不同
     select * from tb_test1 inner join tb_student on tb_test1.id=tb_student.id;
using语法:筛选连接后的结果,两表的对应列值相同才在结果集中,括号内用多个列名要求用逗号连接,列名必须相同
     select * from tb_test1 cross join tb_student using(id);
 
仅MySQL中!:(MySQL中没有全外连接,用UNION操作实现吧)
join=inner join=cross join(笛卡尔积)
left join=left outer join
right join=right outer join
 
sample数据:
 
 select * from tb_test1;
+----+-----------+--------+-------+
| id | name      | gender | score |
+----+-----------+--------+-------+
|  1 | 李毅      | 男     | 95.30 |
|  2 | 二丫      | 女     | 95.30 |
|  3 | 张三      | 女     | 95.30 |
|  4 | 李四      | 女     | 95.30 |
|  7 | 胡鲁瓦    | 男     | 95.30 |
|  9 | 后羿      | 男     | 95.30 |
+----+-----------+--------+-------+
 
 select * from tb_student;
+----+--------+--------+------------+
| id | name   | gender | birthday   |
+----+--------+--------+------------+
|  1 | 李毅   | 男     | 1988-03-20 |
|  2 | kevin  | 男     | 1987-08-23 |
|  3 | marry  | 女     | 1989-11-25 |
|  4 | lucy   | 女     | 1989-11-25 |
|  5 | lily   | 女     | 1992-01-25 |
+----+--------+--------+------------+
 

natural join:自然连接(不允许带on/using)
     natrual join:全自然连接,对左右2个表相同名字的列进行等值匹配,不可用on,using操作指定,自动删除多余重名列
     natrual left join:左自然连接,保留2个表的列(删除多余重名列),以左表为准,不存在匹配的右表列,值置为NULL
     natrual right join:和左自然连接相反
 
select * from tb_test1 natural join tb_student;
+----+--------+--------+-------+------------+
| id | name   | gender | score | birthday   |
+----+--------+--------+-------+------------+
|  1 | 李毅   | 男     | 95.30 | 1988-03-20 |
+----+--------+--------+-------+------------+
 
select * from tb_test1 natural left join tb_student;
+----+-----------+--------+-------+------------+
| id | name      | gender | score | birthday   |
+----+-----------+--------+-------+------------+
|  1 | 李毅      | 男     | 95.30 | 1988-03-20 |
|  2 | 二丫      | 女     | 95.30 | NULL       |
|  3 | 张三      | 女     | 95.30 | NULL       |
|  4 | 李四      | 女     | 95.30 | NULL       |
|  7 | 胡鲁瓦    | 男     | 95.30 | NULL       |
|  9 | 后羿      | 男     | 95.30 | NULL       |
+----+-----------+--------+-------+------------+
 
select * from tb_test1 natural right join tb_student;
+----+--------+--------+------------+-------+
| id | name   | gender | birthday   | score |
+----+--------+--------+------------+-------+
|  1 | 李毅   | 男     | 1988-03-20 | 95.30 |
|  2 | kevin  | 男     | 1987-08-23 |  NULL |
|  3 | marry  | 女     | 1989-11-25 |  NULL |
|  4 | lucy   | 女     | 1989-11-25 |  NULL |
|  5 | lily   | 女     | 1992-01-25 |  NULL |
+----+--------+--------+------------+-------+
 

inner join:内连接(不带on/using时做笛卡尔积)
     主表和关联表的笛卡尔积结果,通过on或者using操作筛选结果集(先求笛卡尔积,后筛选,所以性能上和where一样),2个表的所有列都被保存
 
 select * from tb_student a join tb_test1 b on a.id=b.id;
+----+--------+--------+------------+----+--------+--------+-------+
| id | name   | gender | birthday   | id | name   | gender | score |
+----+--------+--------+------------+----+--------+--------+-------+
|  1 | 李毅   | 男     | 1988-03-20 |  1 | 李毅   | 男     | 95.30 |
|  2 | kevin  | 男     | 1987-08-23 |  2 | 二丫   | 女     | 95.30 |
|  3 | marry  | 女     | 1989-11-25 |  3 | 张三   | 女     | 95.30 |
|  4 | lucy   | 女     | 1989-11-25 |  4 | 李四   | 女     | 95.30 |
+----+--------+--------+------------+----+--------+--------+-------+
 
select * from tb_student a join tb_test1 b on a.gender=b.gender and a.name=b.name;
+----+--------+--------+------------+----+--------+--------+-------+
| id | name   | gender | birthday   | id | name   | gender | score |
+----+--------+--------+------------+----+--------+--------+-------+
|  1 | 李毅   | 男     | 1988-03-20 |  1 | 李毅   | 男     | 95.30 |
|  6 | 李毅   | 男     | 1985-10-11 |  1 | 李毅   | 男     | 95.30 |
+----+--------+--------+------------+----+--------+--------+-------+
 
 

left join:左外连接(必须带on/using,等价与left outer join)
     左表为主表,保留全部记录全部列,右表为关联表,加上on操作之外的其他列,符合on条件的记录连接上
 
on和where的区别(在外连接时有差别):on和where在性能上不存在差异,都是在笛卡尔积后筛选,但on在from阶段,属于join操作,先于where。所以当left join连接时,虽然左表的列不符合on条件,也必须保留,只是该记录右表全是null。而where对连接后的结果做筛选,不符合条件的直接剔除
 
select * from tb_student a left join tb_test1 b on a.id=b.id;
+----+--------+--------+------------+------+--------+--------+-------+
| id | name   | gender | birthday   | id   | name   | gender | score |
+----+--------+--------+------------+------+--------+--------+-------+
|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 |
|  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 |
|  3 | marry  | 女     | 1989-11-25 |    3 | 张三   | 女     | 95.30 |
|  4 | lucy   | 女     | 1989-11-25 |    4 | 李四   | 女     | 95.30 |
|  5 | lily   | 女     | 1992-01-25 | NULL | NULL   | NULL   |  NULL |
|  6 | 李毅   | 男     | 1985-10-11 | NULL | NULL   | NULL   |  NULL |
+----+--------+--------+------------+------+--------+--------+-------+
 
select * from tb_student a left join tb_test1 b on a.id=b.id and a.id<4;
+----+--------+--------+------------+------+--------+--------+-------+
| id | name   | gender | birthday   | id   | name   | gender | score |
+----+--------+--------+------------+------+--------+--------+-------+
|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 |
|  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 |
|  3 | marry  | 女     | 1989-11-25 |    3 | 张三   | 女     | 95.30 |
|  4 | lucy   | 女     | 1989-11-25 | NULL | NULL   | NULL   |  NULL |
|  5 | lily   | 女     | 1992-01-25 | NULL | NULL   | NULL   |  NULL |
|  6 | 李毅   | 男     | 1985-10-11 | NULL | NULL   | NULL   |  NULL |
+----+--------+--------+------------+------+--------+--------+-------+
 
select * from tb_student a left join tb_test1 b on a.id=b.id where a.id<4;
+----+--------+--------+------------+------+--------+--------+-------+
| id | name   | gender | birthday   | id   | name   | gender | score |
+----+--------+--------+------------+------+--------+--------+-------+
|  1 | 李毅   | 男     | 1988-03-20 |    1 | 李毅   | 男     | 95.30 |
|  2 | kevin  | 男     | 1987-08-23 |    2 | 二丫   | 女     | 95.30 |
|  3 | marry  | 女     | 1989-11-25 |    3 | 张三   | 女     | 95.30 |
+----+--------+--------+------------+------+--------+--------+-------+
 

right join:右外连接,和左外连接相反
 
 
以后再使用MySQL的连接就清晰了。
如有不准确的地方请留言指教