Differences between where and having

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.