Friday, 13 January 2012

WHERE vs HAVING

 

WHERE and HAVING is the two common clause always mistaken by junior people to make SQL statement. The clauses are quite easy and specified distinguishes between them.

Related to this I search several Article and they are all over written and complex to understand. I just provide the main differences between them.

The WHERE clause, provide the filtration of rows depending to the criteria provided. It simply filter the row nothing else.

The HAVING clause is used with GROUP BY clause. GROUP BY clause first groups the records and then HAVING clause is used to filter the grouped records. So HAVING clause is used with GROUP BY clause.

In performance factor in mind, the WHERE is most efficient then HAVING clause. The HAVING clause must be used if needed.

A SQL statement may contains WHERE and HAVING clause both together. Please look at the Example for better understanding.

 

SELECT  rollnumber,

        studentname,

        Class,

        SUM(Marks) Marks

FROM    student_Result

WHERE   Class=1       

GROUP BY rollnumber, Class, studentname

HAVING   UPPER(ISNULL(studentname,''))='RAJA RAM'

In this case the student result table is first filtered by WHERE clause and then use the GOUP BY Clause to group the records and finally use the HAVING clause to filter the student name.

I think this litter scenario is quite informative and thanking you to give time on it.

 

--Posted by: MR. JOYDEEP DAS

 

 

 

 

 

1 comment: