SQL子句详解

where与having

1.作用的对象不同。WHERE 子句作用于表和视图，HAVING 子句作用于组(group)。

eg:SELECT city FROM weather WHERE temp_lo = (SELECT max(temp_lo) FROM weather);

2.WHERE 在分组和聚集计算之前选取输入行（因此，它控制哪些行进入聚集计算）， 而 HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数； 因为试图用聚集函数判断那些行输入给聚集运算是没有意义的。 相反，HAVING 子句总是包含聚集函数。（严格说来，你可以写不使用聚集的 HAVING 子句， 但这样做只是白费劲。同样的条件可以更有效地用于 WHERE 阶段。）

在前面的例子里，我们可以在 WHERE 里应用城市名称限制，因为它不需要聚集。 这样比在 HAVING 里增加限制更加高效，因为我们避免了为那些未通过 WHERE 检查的行进行分组和聚集计算。

以下示例使用的数据库是MySQL 5。
数据表：student
表结构：
Field Name DataType Len
id                int           20
name           varchar    25
major           varchar    25
score           int           20
sex              varchar    20

表数据：
编号/姓名/专业/学分/性别
id   name major     score sex
1    jak    Chinese    40    f
2    rain    Math        89    m
3    leo    Phy          78    f
4    jak    Math         76    f
5    rain    Chinese   56    m
6    leo    Math         97    f
7    jak    Phy          45    f
8    jak    Draw         87    f
9    leo    Chinese    45    f

现在我们要得到一个视图：
要求查询性别为男生，并且列出每个学生的总成绩：
SQL：
select s.*,sum(s.score) from student s where sex='f' group by s.name

Result:
id   name major     score sex sum(s.score)
1    jak    Chinese    40    f       248
3    leo    Phy         78     f       220

可以看到总共查到有两组，两组的学生分别是jak和leo，每一组都是同一个学生，这样我们就可以使用聚合函数了。
只有使用了group by语句，才能使用如：count()、sum()之类的聚合函数。

下面我们再对上面的结果做进一步的筛选，只显示总分数大于230的学生：
SQL：
select s.*,sum(s.score) from student s where sex='f' group by s.name having sum(s.score)>230

Result:
id   name major     score       sex   sum(s.score)
1    jak    Chinese    40          f       248

可见having于where的功能差不多。

on与where

数据库在通过连接两张或多张表来返回记录时，都会先生成一张中间的临时表(由from部分生成)，然后再将这张临时表返回继续处理。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2 id size
1 10
2 20
3 30
表2：tab2 size name
10 AAA
20 BBB
20 CCC

两条SQL:
1、select * form tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name='AAA'
2、select * form tab1 left join tab2 on (tab1.size = tab2.size and tab2.name='AAA')

第一条SQL的过程：

1、中间表
on条件:
tab1.size = tab2.size tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 (null) (null)

2、再对中间表过滤
where 条件：
tab2.name='AAA'
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

第二条SQL的过程：

1、中间表
on条件:
tab1.size = tab2.size and tab2.name='AAA'
(条件不为真也会返回左表中的记录) tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的关联条件(如:table1.a = table2.a) 是否为真,都会返回主表中的记录，full则具有left和right的特性的并集。 而inner jion没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。
on与where区别较大，当语句之中使用了on之后就应该考虑where以后的语句是否合理有效，应该在where中使用对主表的筛选语句而避免使用对副表的的筛选语句，对副表的筛选语句应该放置于on中进行实现。