Definition, Comparison and Difference between HAVING and WHERE Clause - Bug Reaper

                  Bug Reaper

Lean about Automation Testing,Selenium WebDriver,RestAssured,Appium,Jenkins,JAVA,API Automation,TestNG,Maven, Rest API, SOAP API,Linux,Maven,Security Testing,Interview Questions

Monday 5 May 2014

Definition, Comparison and Difference between HAVING and WHERE Clause

Definition, Comparison and Difference between HAVING and WHERE Clause

Where clause doesn’t work with aggregates – like sum, avg, max, etc.. Instead, what we will need to use is the having clause.


emp_bonus
EmployeeBonus
A1000
B2000
A500
C700
B1250
If we want to calculate the total bonus that each employee received, then we would write a SQL statement like this:
select employee, sum(bonus) from emp_bonus group by employee;

The Group By Clause

In the SQL statement above, you can see that we use the "group by" clause with the employee column. What the group by clause does is allow us to find the sum of the bonuses for each employee. Using the ‘group by’ in combination with the ‘sum(bonus)’ statement will give us the sum of all the bonuses for employees A, B, and C.
Running the SQL above would return this:
EmployeeSum(Bonus)
A1500
B3250
C700
Now, suppose we wanted to find the employees who received more than $1,000 in bonuses for the year of 2007. You might think that we could write a query like this:
BAD SQL:
select employee, sum(bonus) from emp_bonus 
group by employee where sum(bonus) > 1000;

The WHERE clause does not work with aggregates like SUM

The SQL above will not work, because the where clause doesn’t work with aggregates – like sum, avg, max, etc.. Instead, what we will need to use is the having clause. The having clause was added to sql just so we could compare aggregates to other values – just how the ‘where’ clause can be used with non-aggregates. Now, the correct sql will look like this:
GOOD SQL:
select employee, sum(bonus) from emp_bonus 
group by employee having sum(bonus) > 1000;

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.
A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output.

WHERE is first, then you GROUP the result of the query, and last but not least HAVING-clause is taken to filter the grouped result. This is the "logical" order,

No comments:

Post a Comment