数据库mysql自然连接_mysql 连接查询（俗称连表查询）内连接、外连接、自然连接...

mysql 连接查询(俗称连表查询)内连接、外连接、自然连接

发布时间：2020-08-03 02:29:09

来源：51CTO

阅读：2876

作者：梧桐深院

连接查询的分类

本文讨论中用到的测试数据

``create table student(

id int primary key auto_increment,

name varchar(10)

);

insert into student values

(null,'xiaohong'),

(null,'xiaoming'),

(null,'xiaogang'),

(null,'xiaoliang');

create table score(

id int primary key auto_increment,

stu_id int not null,

score decimal(5,2)

);

insert into score values

(null,1,300.45),

(null,2,400.35),

(null,3,500);``

内连接

inner join / join

由于mysql默认是内连接，所以，join 等同于 inner join

以两个表举例，内连接只返回在连接过程中有连接匹配的记录。也就是说，根据连接条件，两个表中都有对应的数据存在的记录，才会返回。

举例：

select stu.id,stu.name,s.score from student as stu inner join score as s on stu.id = s.stu_id;

如上图所示，由于小亮没有成绩，所以小刚没有出现在最终的结果中。

连接条件分析：

连接条件可以使用 on using where

区别：on 在连表查询中，任何情况下都可以使用on，而且建议使用 on。on 是在连表的过程中，根据on条件判断是否保留连表的结果。

using 是在连表查询的字段名一致时，可以使用。如 using(id)。using 条件中使用的字段，返回结果中只有一遍。

where 是在连表操作完成后，再根据where条件进行数据过滤。不建议使用，因为这样效率很低。

外连接

外连接查询时，允许另一方存在与之不匹配的数据。外连接的连接条件不可使用 where，须使用 on 或者 using其中的一种，其它都与内连接一致。

左外连接(left outer join / left join)：

左表为主表，即使右表没有与左表匹配的记录，也返回左表的记录。而右表与左表不匹配的记录将被忽略。

举例：

select * from student as stu left join score as s on stu.id = s.stu_id;

结果如下：

+----+-----------+------+--------+--------+

| id | name | id | stu_id | score |

+----+-----------+------+--------+--------+

| 1 | xiaohong | 1 | 1 | 300.45 |

| 2 | xiaoming | 2 | 2 | 400.35 |

| 3 | xiaogang | 3 | 3 | 500.00 |

| 4 | xiaoliang | NULL | NULL | NULL |

+----+-----------+------+--------+--------+

右外连接(right outer join / right join)：

右表为主表，即使左表没有与之匹配的记录，也返回右表的记录。

举例：

select * from score as s right join student as stu on s.stu_id=stu.id;

+------+--------+--------+----+-----------+

| id | stu_id | score | id | name |

+------+--------+--------+----+-----------+

| 1 | 1 | 300.45 | 1 | xiaohong |

| 2 | 2 | 400.35 | 2 | xiaoming |

| 3 | 3 | 500.00 | 3 | xiaogang |

| NULL | NULL | NULL | 4 | xiaoliang |

+------+--------+--------+----+-----------+

全外连接(full join)：mysql 暂不支持，可以用union模拟实现。

自然连接

natural join (同 join)

natural left join (同 left join)

natural right join (同 right join)

自然连接会自动判断，以两个表中相同的字段为连接条件，返回查询结果。

注意：内连接不写连接条件会出现笛卡尔积的结果，应该避免这种情况，而外连接不写连接条件会报错