SQL Clause Details

web front end seven thousand three hundred and eighty-nine 13 years ago (2011-04-06)

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:
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:
select s.*,sum(s.score) from student s where sex='f' group by s.name

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:
select s.*,sum(s.score) from student s where sex='f' group by s.name having sum(s.score)>230

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:
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.