1. Use different places
Where can be used in select update delete insert... into statements
Having can only be used in select statements.
2. The execution sequence is different.
The where search condition is applied before the statement is executed for grouping.
Having search conditions are executed after grouping conditions.
If the where and having colleagues appear in the statement, the where statement is executed first and then the having statement is executed.
3. Clauses are different
The conditional expression having in the where clause can be followed, while some expressions in the having clause cannot be followed. The having clause can use a set function (sum, count, avg, max, and min ), the where clause is not allowed. Both of them can be used in some places, such
Having: select studentid, avg (score) from studentScore group by studentid having left (studentid, 1) = '0'
Where: select studentid, avg (score) from studentScore where left (studentid, 1) = '0' group by studentid
In this case, it will be faster to parse:
Select studentid, avg (score) from studentScore group by studentid having left (studentid, 1) = '0'
1. GROUP Summary 2. Filter out invalid items
Left (studentid, 1) = '0' is an inefficient filtering condition. Grouping can greatly reduce the amount of data (for example, each person has dozens of courses ), in addition, only a small number of students need to be filtered out. This method is highly efficient.
Wk_ad_begin ({pid: 21}); wk_ad_after (21, function () {$ ('. ad-Den '). hide () ;}, function () {$ ('. ad-Den '). show () ;}); if (navigator. userAgent. indexOf ("MSIE 6.0") <0) {BAIDU_CLB_fillSlot ('000000 ');}
Select studentid, avg (score) from studentScore where left (studentid, 1) = '0' group by studentid
1. Filter invalid items. 2. GROUP Summary
Although left (studentid, 1) = '0' is an inefficient filtering condition, if you want to find the average score of dozens of students with 3-5 course assignments for millions of students, it should be wise to choose it.
Differences between where and having