多表查询 join

inner join（内联）

其实就是联结查询，那和之前的where+=的方式有什么不同嘛？

我们先来看看它怎么写的：

select name,score from student inner join score on student.id=score.stuid

它的效果和之前的：

select name,score from student,score where student.id=score.stuid

其实是一样的，只不过前者是显性的，后者是隐性的。但是，隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。

注意点

一般要使得数据库查询语句性能好点遵循一下原则：

在做表与表的连接查询时，大表在前，小表在后不使用表别名，通过字段前缀区分不同表中的字段查询条件中的限制条件要写在表连接条件前尽量使用索引的字段做为查询条件

结论

尽量使用join而不是where来列出关联条件，特别是多个表联合的时候。

原因是：

在效率上，Where可能具有和Inner join一样的效率。但基本可以肯定的是Join的效率不比Where差。

使用join可以帮助检查语句中的无效或者误写的关联条件

查询多张表（>=2）

多几个就多写几行呗

select name,score from student inner join score on student.id=score.stuid inner join ... on ... inner join ... on ... ...

left join（左外联结）

以左表为主（基准）表，不满足连接条件的也不过滤，为null就好了

select name,score from student left join score on student.id=score.stuid

就拿上面的例子来说，student是左表，为学生表；score是右表，为成绩表

要是我不仅想查成绩，还想查谁没来考试呢？这时候就需要左外连接了（查询出来的结果null在最后）

right join（右外联结）

同上，相反即可

cross join（交叉连接）

结果是获取笛卡儿积

mysql> select * from canteen,score; +----+----------+------+------+---------+---------+------+ | id | money | tid | id | chinese | english | math | +----+----------+------+------+---------+---------+------+ | 1 | 100.0000 | 1 | 1 | 98 | 99 | 100 | | 1 | 100.0000 | 1 | 2 | 90 | 92 | 80 | +----+----------+------+------+---------+---------+------+ 2 rows in set (0.00 sec) mysql> select * from canteen cross join score; +----+----------+------+------+---------+---------+------+ | id | money | tid | id | chinese | english | math | +----+----------+------+------+---------+---------+------+ | 1 | 100.0000 | 1 | 1 | 98 | 99 | 100 | | 1 | 100.0000 | 1 | 2 | 90 | 92 | 80 | +----+----------+------+------+---------+---------+------+ 2 rows in set (0.00 sec)

这两种写法是一样的

natural join（自然连接）

也就是去重的等值连接，咱们类比一下：

mysql> select * from canteen natural join score; +----+----------+------+---------+---------+------+ | id | money | tid | chinese | english | math | +----+----------+------+---------+---------+------+ | 1 | 100.0000 | 1 | 98 | 99 | 100 | +----+----------+------+---------+---------+------+ 1 row in set (0.00 sec) mysql> select * from canteen inner join score on canteen.id = score.id; +----+----------+------+------+---------+---------+------+ | id | money | tid | id | chinese | english | math | +----+----------+------+------+---------+---------+------+ | 1 | 100.0000 | 1 | 1 | 98 | 99 | 100 | +----+----------+------+------+---------+---------+------+ 1 row in set (0.00 sec)

注意点

如果找不到两个相同的字段，那将会返回笛卡儿积如果有两个相同字段，将会返回空

怎么破除呢？（假设t1、t2字段相同）

select * from t1 inner join t2 on using(id);

但是不推荐，会出现这种情况，说明设计不合理了；尽量不用自然连接，用inner join写全来；因为你不知道表结构时，自然连接会出事

子查询

in

其实就是嵌套查询，毕竟数据库是面向集合的，可以拿着结果集继续查，举个例子：

select * from student where id in (select stuid from score where score>=85); 首先后半句查出来成绩不低于85的学生的学生id集合看student表里有没有满足条件（id在上面查出来的集合里，是or的关系）的实体

关键就在in，判断前面的字段是否在后面查出来的集合中（你不必知道后面查出来的集合有什么，这一过程被隐藏了）

相反的，还有not in

exists

这个和in容易搞混，要注意，exists表示的是子查询出来的集合里有没有东西，有东西就执行前面的语句，没有就不执行

select * from canteen where exists (select id from score where chinese>95);

只要存在语文成绩大于95的，就查整张餐厅表，这是个存在问题；而in是需要满足条件的

一样，也有相反的not exists