Where and having
1. The objects are different. The WHERE clause acts on tables and views, and the HAVING clause acts on groups.
eg:SELECT city FROM weather WHERE temp_ lo = (SELECT max(temp_lo) FROM weather);
2. WHERE selects input rows before grouping and aggregation calculation (therefore, it controls which rows enter aggregation calculation), while HAVING selects grouped rows after grouping and aggregation. Therefore, the WHERE clause cannot contain aggregate functions; Because it is meaningless to try to use the aggregate function to determine which rows are input to the aggregate operation. In contrast, the HAVING clause always contains aggregate functions. (Strictly speaking, you can write HAVING clauses without aggregation, but this is just a waste of effort. The same conditions can be used more effectively in the WHERE phase.)
In the previous example, we can apply city name restrictions in WHERE, because it does not require aggregation. This is more efficient than adding restrictions in HAVING, because we avoid grouping and aggregation calculation for rows that fail the WHERE check.
The following example uses data base It is MySQL 5.
Datasheet: student
Table structure:
Field Name DataType Len
id int 20
name varchar 25
major varchar 25
score int 20
sex varchar 20
Table data:
No./Name/Major/Credit/Gender
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
Now we want to get a view:
The query is required to be male, and the total score of each student is listed:
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
It can be seen that there are two groups of students, jak and leo. Each group is the same student, so we can use the aggregate function.
Only when the group by statement is used can aggregate functions such as count () and sum () be used.
Next, we will further filter the above results, and only the students with a total score of more than 230 will be shown:
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
It can be seen that having has the same function as where.
On and where
When the database returns records by connecting two or more tables, it will first form an intermediate temporary table (generated by the from part), and then return this temporary table to continue processing.
When using left jion, the difference between on and where conditions is as follows:
1. The on condition is the condition used when generating temporary tables. It returns the records in the left table regardless of whether the on condition is true.
2. The where condition is used to filter temporary tables after they are generated. At this time, there is no meaning of left join (the records in the left table must be returned). If the conditions are not true, they will be filtered out.
Suppose there are two tables:
Table 1: tab2 id size
1 10
2 20
3 30
Table 2: tab2 size name
10 AAA
20 BBB
20 CCC
Two SQLs:
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')
The first SQL procedure:
1. Intermediate table
On condition:
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. Then filter the intermediate table
Where condition:
tab2.name='AAA'
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
The second SQL process:
1. Intermediate table
On condition:
tab1.size = tab2.size and tab2.name='AAA'
(If the condition is not true, the record in the left table will also be returned) tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 (null) (null)
3 30 (null) (null)
In fact, the key reason for the above results is the particularity of left join, right join and full join, regardless of the Association condition (such as table1. a=table2. a) Whether it is true or not, the records in the main table will be returned. Full has the union of left and right characteristics. However, inner jion does not have this particularity. If the condition is placed in "on" and "where", the returned result set is the same.
There is a big difference between on and where. When you use on in a statement, you should consider whether the statement after where is reasonable and effective. You should use the filter statement for the main table in where instead of the filter statement for the secondary table. The filter statement for the secondary table should be placed in on for implementation.
