MySQL之join/where的用法

join、where关键字都可用于表与表之间的连接，join又分为了内连接和外连接，where又有等值与非等值连接和自身连接。
一、where关键字
连接查询的where子句中用来连接两个表的条件称为连接条件或连接谓词

格式：where [表名.]<列名1><比较运算符>[表名.]<列名2>…
比较运算符主要有：= 、> 、< 、>= 、<= 、!= 、<> 还有 and 和between 等
当连接运算符为=时为等值连接，其它的是非等值连接
eg1： where table1.c=table2.c
eg2： where table2.c>100

我们来结合例子看一下使用where连接表，最终得到的表是怎么样的
创建的stu_infos表：

teachers表：

我们使用一下等值连接：

select *
from stu_infos s, teachers t
where s.id=t.t_id;

结果：

再使用非等值连接：

select * 
from stu_infos s,teachers t
where s.id < t.t_id;

可见，它们都是在满足where条件的情况下将表与表连接起来。
再看一下使用where的自身连接：

select * 
from stu_infos s1,stu_infos s2
where s1.id < s2.id;

可见，自身连接的时候，字段列表相当于再复制了一份连起来，每次从s1表中取出一个记录，将它与s2表中的每一个记录比较，符合where条件即留下，否则，将被筛掉。

二、join关键字
首先我们得先了解一下笛卡尔积：若一个n行的表1和一个m行的表2，进行了笛卡尔积后得到的是一个n*m行的表
如下：

使用格式：（inner可以省略，默认为inner join）

<表1> [inner] join <表2>
on 连接条件

①inner join 内连接（或等值连接）：将两个表笛卡尔积的结果记录中，符合on的连接条件的记录保留下来。

我们来看一个例子：

select * 
from stu_infos s1 inner join teachers t
on s1.id = t.t_id;

结果

②left join 左连接：将两个表笛卡尔积的结果记录中，先把符合on的连接条件的记录保留，再把左边的表剩余的记录全部保留下来。（读取左边数据时，即使对应的右边表中的那条记录没有数据，也要读出来，只是右表没有的数据用null代替）

我们来看一个例子：

select * 
from stu_infos s1 left join teachers t
on s1.id = t.t_id;

结果：

③right join 右连接：与左连接情况类似，只是右连接会保留下右边表的全部数据，左表对应的记录没有数据就用null代替，注意，若是，右边表的记录条数少于左边表，那么只保留共同部分，左边表多余的数据会被过滤掉。

又来看个例子：

select * 
from stu_infos s1 right join teachers t
on s1.id = t.t_id;

结果：

另外的，on和where是可以出现在同一条命令里的，只是on的条件是作为表间的连接条件，而之后的where的条件是用于对连接后得到的表数据的筛选，作为筛选条件。
如：

select * 
from stu_infos s1 left join teachers t
on s1.id = t.t_id
where s1.ranking<101;

结果：

小提示：当只有join时，不能有left 或right，可以用where代替on

附上一小题：
现在有两张表，并给出表的部分数据如下：
选课信息表 takes


ID为学生学号，year为开课年份，要求完成以下查询：

对于在2009年讲授的每个课程段，如果该课程段有至少2名学生选课，查询选修该课程段的所有学生的总分数（ tot_cred ）的平均值。

sql语句：

select t.course_id,t.semester,t.year,t.sec_id,avg(stu.tot_cred) as 'avg(tot_cred)'
from takes t join student stu
on t.year='2009'
group by t.course_id
having count(t.ID)>=2;

运行结果：